Here’s my script to fix this error. This occurs when I restore a database to another server and want to remove the source server users.
DECLARE @vcSchemaName VARCHAR(128); SET @vcSchemaName = 'schema name goes here' SELECT s.name, 'ALTER AUTHORIZATION ON SCHEMA::[' + @vcSchemaName + '] TO dbo;' FROM sys.schemas s WHERE s.principal_id = USER_ID(@vcSchemaName
Here’s a link to remember for
This error occurs for me when I loop through a list of $CurrentServers. The solution was to add Out-Host to the end of the output line.
Operation is not valid due to the current state of the object
At line: 0 char: 0
$dsOut = Invoke-sqlcmd2 -ConnectionTimeout 10 -ServerInstance $CurrentServer -Query $SQL -Verbose -QueryTimeout 10 -Database master_admin -As DataTable # Use Out-Host to prevent format-table errors $dsOut |Select-Object * |ft -AutoSize |Out-Host
This code shows the Symbol and HEX as well as DEC. Still to go is OCT.
WITH Symbols
AS (
SELECT -1 code,
CHAR(ASCII(' ')) Symbol
UNION ALL
SELECT code + 1,
CHAR(code + 1)
FROM Symbols
WHERE code + 1 <= 255
)
SELECT Symbol,
ASCII(Symbol) AS [DEC],
master.dbo.fn_varbintohexstr(CAST(Symbol AS VARBINARY)) AS HEX
--,(ASCII(Symbol) % 8) AS [OCT]
FROM Symbols
ORDER BY [DEC]
OPTION (MAXRECURSION 256)
Char Dec Oct Hex
(sub) 26 0032 0x1a
For some reason COPY, yes the DOS COPY command, adds 0x1a to the end of a multiple file COPY command. You can see the SUB at the end of a file in NOTEPAD 2.
One way to get a “header row” or a list of column names from a table for a bcp out is to append a header file and the data file.
The only way I’ve found is to use COPY. If we simply tell COPY the files are all BINARY, it won’t add the 0x1a to the end.
COPY /B File1+File2 File2