I know I can update a single record like this - but then how to I get access to the id of the record that was updated? (I'm using MSSQL so I can't use Oracles RowId) update myTable set myCol = 'foo' where itemId in (select top 1 itemId from myTable ) If I was peforming an Insert I could use getGeneratedKeys to get the id field value,...
Hi ! I've got in an ASP.NET application this process : Start a connection Start a transaction Insert into a table "LoadData" a lot of values with the SqlBulkCopy class with a column that contains a specific LoadId. Call a stored procedure that : read the table "LoadData" for the specific LoadId. For each line does a lot of calculatio...
We use SQL Server 2000/2005 and Vault or SVN on most of our projects. I haven't found a decent solution for capturing database schema/proc changes in either source control system. Our current solution is quite cumbersome and difficult to enforce (script out the object you change and commit it to the database). We have a lot of ideas of...
May be my title is not clear. I am looking for some kind of version control on database tables, like subversion does on files, like wiki does. I want to trace the changes log. I want to extract and run the diff in reverse. (undo like a "svn merge -r 101:100"). I may need a indexed search on the history. I've read the "Design Pattern fo...
I would need to create a temp table for paging purposes. I would be selecting all records into a temp table and then do further processing with it. I am wondering which of the following is a better approach: 1) Select all the columns of my Primary Table into the Temp Table and then being able to select the rows I would need OR 2) Sel...
One of our programs is erroring out because a stored procedure was "created with QUOTED_IDENTIFIER option ON". This is a Microsoft SQL Server (8). All that happened to it yesterday was: Stored procedures were editted. A table was created. I have removed the edits. I have deleted the table. The error is still coming up. Is there a que...
I am modifying a SQL table through C# code and I need to drop a NOT NULL constraint if it exists. How do I check to see if it exists first? ...
DBCC SHRINKFILE always works when I run it manually on a log file, even when I get the following message: 'Cannot shrink log file 2 (Claim_Log) because all logical log files are in use.' When I run it from a job, however, it only shrinks the log about one third of the time. The other times, it just remains large (about 150Gb). There i...
Hi, when calling a SSIS package (C# app) using LoadFromSqlServer, does the user account have to have any special permissions on the database or server? I can't seem to view the packages in EM at the moment ... ...
On a SQL Server 2005 database, one of our remote developers just checked in a change to a stored procedure that changed a "select scope_identity" to "select @@identity". Do you know of any reasons why you'd use @@identity over scope_identity? ...
I am new to SQL Server, and I have been tasked with setting permissions on a remote server. What I need to do is limit the domain admin from having access to any of the DB's on a particular server. How can I accomplish this? ...
I'm trying to update something in AS400 from sql server 2000 through openquery like following. UPDATE OPENQUERY(odbcname, 'SELECT * FROM "libname"."filename" WITH NC') SET NBFLAG01=1 WHERE NBFLAG01 <> 1 Here's the error I'm getting. "OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: Insufficient base ...
Can I use a query in MSSQL to get the .mdf and .ldf filename/location for a specific database? ...
SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value FROM prodtree_element pe LEFT JOIN prodtree_link pl ON pe.prodtree_element_id = pl.to_prodtree_node_id LEFT JOIN line li ON pe.line_code = li.line_code INNER JOIN attribute_values av ON av.attribute_definition_id = #st...
I'm implementing some rudimentary SQL Server monitoring to watch for excessive blocking. I have a stored procedure that looks like this: CREATE PROCEDURE [dbo].[bsp_GetBlockedProcessCount] AS BEGIN SET NOCOUNT ON; DECLARE @count INT SELECT @count = COUNT(*) FROM master.dbo.sysprocesses WITH (NOLOCK) WHERE blocked ...
Hi, I am trying to trace a deadlock that is occurring in our SQL 2005 database (64-bit). We do not currently have snapshot isolation enabled. I turned on tf-1204 and received the output below. From this output I am able to determine that Node 1 is a stored procedure that selects data and only modifies values in #temp tables. Node 2...
Two installations of SSRS 2005 on two separate machines in my shop seem to have lost their encryption keys at exactly the same time. These are developer machines with the development edition of SQL Server 2005 running, so I'm hoping that perhaps someone here has an explanation for this. Yesterday, both machines began reporting the erro...
I would like to replicate a MSSQL 2005 db (hosting through a shared hosting provider) directly on an integration server for development purposes. MSSQL server provides quite a few ways of performing data replication, but I am not sure that any of them is compatible with a typical shared hosting environment. The idea would be to update t...
Problem: a table of coordinate lat/lngs. Two rows can potentially have the same coordinate. We want a query that returns a set of rows with unique coordinates (within the returned set). Note that distinct is not usable because I need to return the id column which is, by definition, distinct. This sort of works (@maxcount is the number of...
Can anyone provide (or point me to) example sql code for logging exceptions using Enterprise Library Exception Handling Application Block (EHAB)? ie I'd like standard CREATE TABLE and CREATE PROCEDURE statements for the structures and procs to store logged exceptions in a SQL Server database. Also, can anyone describe how the app block ...