I have a table that is used to store an incrementing numeric ID (of type INT). It contains a single row. The ID is incremented using a query: UPDATE TOP(1) MyTable WITH(TABLOCKX) SET NextID = NextID + 1 I would like to move this into a stored procedure that returns the value that was in the NextID column before it was incremented, but...
I have an SQL query I get from a configuration file, this query usually contains 3-6 joins. I need to find at run time, based on the result set represented by SqlDataReader, to find the name of the table for each column. Here are some thing that don't work: SqlDataReader.GetName returns the column name but not the table name. SqlData...
Is it possible to call a function from and dotnet asseble from a MS SQL query? ...
I have in production SQL Server 2005 and in Development SQL Server 2008 Database. I would like to detach database from production create tables, insert data etc. and then attach it back. How make it compatible after I make some changes in SQL server 2008? What is the right way to do it? ...
I have the following trigger: CREATE TRIGGER Users_Delete ON Users AFTER DELETE AS BEGIN SET NOCOUNT ON; -- Patients UPDATE Patients SET ModifiedByID=NULL WHERE ModifiedByID=ID; UPDATE Patients SET CreatedByID=NULL WHERE CreatedByID=ID; UPDATE Patients SET DeletedByID=NULL WHERE DeletedByID=ID; END I wa...
I'm trying to find a phpmyadmin-like db administration tool for SQL Server for a LAMP environment. I've only been able to find similar tools that exist for a IIS/ASP environment. ...
Using the following two tables on SQL Server 2005, how would I write a query to return the First_Name, Last_Name, Order_Date and total amount of an order where any order line item amounts for any order (OD_Amount) are greater than 100. Orders Order_ID First_Name Last_Name Order_Date Order_Details Order_Detail_ID Order_ID OD_Item_N...
let's say I've got two queries: select top 20 idField, field1, field2 from table1 where idField in ( select idField from table1 where field3 like '...' union select idField from table2 where field4 like '...' ) order by sortfield1 select top 20 idField, field1, field2 from table1 where field3 like '...' or idfield ...
How can I select the hour from a datetime? I'm looking for the equivalent of Oracle's TO_CHAR( d, 'HH24' ), which would return 17 for 2010-06-23 17:22:31. I tried to find out about the best time (best answer/questin-ration) to ask an SQL-related question on StackOverflow, using Data Explorer. I got it to work, but I'm sure there is a...
I'm trying, unsuccessfully, to remotely execute an SSIS package. The package resides on a SQL Server 2008 instance and I'd like to call it from a Job on a 2005 server. The error I'm getting is: The package could not be loaded. The step failed. If I go from 2008 to 2008, there is no error. Any ideas? ...
I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn't exist yet, I can create it). I tried stuff like this: IF OBJECT_ID('myserver\devdb_1') IS NULL BEGIN PRINT 'Does not exist, need to create link' EXEC master.dbo.sp_addlinke...
We are upgrading from an environment where the development web server, the SQL Server 2005, and SQL Server data are all on the same machine, a Windows XP Machine. We are upgrading to having the web server on one Windows 2008 server, the SQL Server on a 2nd Windows 2008 Server, and to Server to a Windows 2008 server, nd the data on a SAN...
I've been struggling with this for a while now and I dont know what I'm missing. The error is "call to undefined function mssql_connect()" Having looked into it already I've done about everything I can. I moved the php.ini file to c:windows. In the ini I changed the extension_dir to "c:PHP\ext" and uncommented extension = php_mssql.dll. ...
The modulus function in Microsoft SQL Server only works on certain data types. According to the MSDN Article [1] on the modulus operator, you normally would use modulus like this... dividend % divisor dividend Is the numeric expression to divide. dividend must be a valid expression of any one of the data types in the integer and mon...
How do I pass and use the column name to retrieve a bigint variable in the actual column? DECLARE @personID BIGINT, DECLARE @queryString varchar(500) Set @queryString = 'Select @personID = ' + @PersonColumnID + ' from dbo.Loss_Witness where WitnessID = @witnessID' exec(@queryString) Error message states "Must declare variable '@perso...
So I have a column in my DB on SQL Server that is a datetime. I want to do this simple select statement: SELECT * FROM Res Where dateSubmit='6/17/2010 5:01:26 PM' dateSubmit is a datetime data type and my database has a row where dateSubmit is 6/17/2010 5:01:26 PM However, when I execute this statement it returns null. ...
I want to write a query in a stored proc with many filters but I want to avoid dynamic SQL. Say my parameters are nullable (@filter1, @filter2, @filter3...). One way I might solve this is: SELECT col1, col2, col3 FROM table WHERE col1 = ISNULL(@filter1, col1) AND col2 = ISNULL(@filter2, col2) AND col3 = ISNULL(@filter3, col3) The re...
I want to delete from a table the entries where it has multiple values for Date field. So say I have Employee table - Id,Name,Date,Points i want to delete the entries with same Date field which should be unique...just to cleanup i need to just keep a single entry for date and delete the rest...maybe keep the recent one if possible....c...
The following stored procedure works correctly execpt when I pass in the @NameSubstring parameter. I know I am not dynamically building the like clause properly. How can I build the like clause when this parameter also needs to be passed as a parameter in the EXEC sp_executesql call near the bottom of the procedure? ALTER PROCEDURE [dbo...
In Microsoft SQL Server : I've added an insert trigger my table ACCOUNTS that does an insert into table BLAH based upon the inserted values. The inserts come from only one place, and those happen one at a time. (By that, I mean, that there's never two inserts in a transaction - two web users could, theoretically click submit and have th...