Recent content by psprague

A couple of thoughts: The first is to isolate the calls to the servers into different stored procs so the indiviaual names could be hardcoded: If @Department = 'P' exec do_stuff_with_server_a else exec do_stuff_with_server_b the other thought is have a single linked server alias and switch...

If the column TheID is of type int then the extra quotes are not required. Try: set @sql = 'select top 100 * from ' + @fulldbname + ' where TheID = ' + @intTheID Instead of: set @sql = 'select top 100 * from ' + @fulldbname + ' where TheID = ''' + @intTheID + ''''

Assign it to a variable first. Any operation sets the @@error intrinsic variable with a new value. SELECT @error = @@error IF @error <> 0 --you can return the error as a result set SELECT @error --or if you want to raise error RAISERROR(...) --in a sp you might want to just return the error...

Generate GUIDs with NewID(). Supposedly they are random too.

That is indeed redundant and not needed. The IF EXISTS statement would always return a false (if there are no concurency issues i.e. other processes inserting into the table) I'd remove the first one.

If there is no fk restraints on the table then simply deleting blindly from the table might not a bad option (as written in your example) butI have not ever done any performance testing on a 'do nothing' delete statement. If you wanted to 'test' the table prior to the delete then: IF...

I think this optimizes a bit better than count(*). IF EXISTS(SELECT * FROM mytable) or the negative: IF NOT EXISTS(SELECT * FROM mytable)

Did you try a blank password? Thats the default.

a cleaner solution is: SELECT c.name AS pkColumnName FROM syscolumns c INNER JOIN sysindexkeys sik ON sik.id = c.id AND sik.colid = c.colid INNER JOIN sysindexes si ON si.indid = sik.indid and si.id = c.id WHERE (si.indid BETWEEN 1 And 254) AND (si.status & 2048) =...

Binary types such as text, ntext and images are not allowed in an 'after' trigger but they are acceptable in an 'instead of' trigger.

INSERTED is just an instrinsic temp table and will go out of scope when the trigger has finished. If you are trying to do something with the table the trigger is working on, then link the INSERTED table on the PK and update the column in the base table. If you want to intercept the value...

Using '*' is not a particularly good programming practice for several reasons. SQL Server has to do the look up of columns itself, which consumes time and if, somewhere along the line, the table gets a column added, the code breaks.

You can just turn it off, if that helps. SET IDENTITY_INSERT mytable OFF

You can do a several different things. The first would be to switch within the proc firing different queries based on which param is null. Using if statements and static sql: if pool is not null and investor is not null begin --select using both as selection criterion end if pool is null begin...

Generally, doing something like this is considered a bad idea. (It would be nice if sql server exposed events better but alas, wait for the next version....) As you have discovered, launching a process (an .exe) from with in sql server is a syncronous operation, meaning the calling thread is...