My view is, first see if you really need a temp table – or – can you make do with a Common Table Expression (CTE). Second, I would always drop my temp tables. Sometimes you need to have a temp table scoped to the connection (e.g. ##temp), so if you run the query a second … Read more
I’m not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the … Read more
A unique constraint is implemented behind the scenes as a unique index, so it doesn’t really matter how you specify it. I tend to implement it simply as: ALTER TABLE dbo.foo ADD CONSTRAINT UQ_bar UNIQUE(bar); Some people create a unique index instead, e.g. CREATE UNIQUE INDEX IX_UQ_Bar ON dbo.foo(bar); The difference is in the intent … Read more
Open Control Panel > Administrative Tools > Services > Select Standard services tab (under the bottom) > Find start SQL Server Agent Right Click and select properties, Startup Type : Automatic, Apply, Ok. Done.
It is the severity level of the error. The levels are from 11 – 20 which throw an error in SQL. The higher the level, the more severe the level and the transaction should be aborted. You will get the syntax error when you do: RAISERROR(‘Cannot Insert where salary > 1000’). Because you have not … Read more
use the command-line tool SQLCMD which is much leaner on memory. It is as simple as: SQLCMD -d <database-name> -i filename.sql You need valid credentials to access your SQL Server instance or even to access a database
I believe this error message is due to a “zombie transaction”. Look for possible areas where the transacton is being committed twice (or rolled back twice, or rolled back and committed, etc.). Does the .Net code commit the transaction after the SP has already committed it? Does the .Net code roll it back on encountering … Read more
The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown. These predicates work with any comparable … Read more
Depending on your needs, here are a couple of ways: IF EXISTS (SELECT * FROM TABLE WHERE COLUMN = ‘SOME VALUE’) –INSERT SOMETHING ELSE –INSERT SOMETHING ELSE Or a bit longer DECLARE @retVal int SELECT @retVal = COUNT(*) FROM TABLE WHERE COLUMN = ‘Some Value’ IF (@retVal > 0) BEGIN –INSERT SOMETHING END ELSE BEGIN … Read more
You can specify the connection string as one long string that uses semi-colons (;) as the argument separator. Working example: import pyodbc cnxn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;’) cursor = cnxn.cursor() cursor.execute(“SELECT LastName FROM myContacts”) while 1: row = cursor.fetchone() if not row: break print(row.LastName) cnxn.close() For connection strings with lots of parameters, the following will accomplish … Read more