How do I pass timeout parameter to SQL Database Engine
I'm running a query that is timing out for first two times and returning results on the third time. How do I tell SQL Server to wait until the query is completed instead of timing out? ...
tsql
Is it possible to add a logic Constraint to a Foreign Key?
Hi folks, I've got two tables and I've added a foreign key constraint. Kewl - works great. Now, is it possible to further constrain that relationship against some data in the parent table? Basically, I have animals in the parent table, and for the child table wishto only contain data where the parent data are .... um .. mammals. eg. ...
T-SQL: How to use parameters in dynamic SQL?
I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER. When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody help? CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */ ( @p_Creat...
Can I change foreign key IDs when deleting the FK row?
I need to make a column unique in one of our database tables, and we want to completely remove any duplicates from the table. There is however a snag, in that there are a bunch of dependencies to other tables that will be affected. For example, let’s say we’ve got the following relationship: ------------------- ------------------- * ...
How to programatically save a query results to a text file?
SQL Server 2005 - How can I save the results of a query to a text file programatically? (i.e without choosing the Results to file option in Analyzer and not through sql command)? For example select * from northwind.dbo.suppliers And I save my results to CSV file? ...
Beginner: Checking availability on a booking table in SQL
I'm writing a job vacancy database for a bit of fun (and to try and learn T-SQL/SQL Server and this is what I have in my applications table so far. application_id name interviewer location_id from to ------------------------------------------------------------------------------------------------------...
Can I use SELECT statement inside INSERT values?
I tried this: INSERT INTO tbl_vaucher ( vaucher_name, created_date ) VALUES ( ( SELECT TOP 1 con_full_name FROM tbl_contact ), GETDATE() ) , getting: Subqueries are not allowed in this context. Only scalar expressions are all...
SQL Pivot based on value between two columns
I have a table in which I have among other things, two columns, one for start date and another for end date. I need to write a query which will return a column for each month of the year and the value of that column is 1 if the month is between 0 otherwise. The PIVOT statement seems to be what I am looking for here, but from the best I...
Best way to determine Server Product version and execute SQL accordingly and one more question
Guys, This is a two-pronged question: Scenario: I have a script to query MSDB and get me details of job schedules. Obviously, the tables differ from SQL 2000 to SQL 2005. Hence, I want to check the version running on the box and query accordingly. Now the questions: Question 1: This is what I am doing. IF LEFT(CAST(SERVERPROPERTY('P...
Inserting Values into Dynamic Sql within a stored procedure - Where do the ticks go?
I'm trying to create sql statement in TSQL that looks like this: INSERT INTO UsersTable (UserName) VALUES (@UserName) This is easy until you're trying to do it dynamically in T-Sql and @UserName is a varchar The it looks like this: SELECT @SQLInsert = 'INSERT INTO UsersTable (UserName) ' + 'VALUES (' + @UserName ...
How to convert datetime to date only (with time set to 00:00:00.000)
I have a string '2009-06-24 09:52:43.000', which I need to insert to a DateTime column of a table. But I don't care about the time, just want to insert it as 2009-06-24 00:00:00.000 How can I do that in T-SQL? ...
Inserting n number of records with T-SQL
I want to add a variable number of records in a table (days) And I've seen a neat solution for this: SET @nRecords=DATEDIFF(d,'2009-01-01',getdate()) SET ROWCOUNT @nRecords INSERT int(identity,0,1) INTO #temp FROM sysobjects a,sysobjects b SET ROWCOUNT 0 But sadly that doesn't work in a UDF (because the #temp and the SET ROWCOUNT). A...
T-SQL looping to create a recordset
I have some stored procedures that I need to write against a nasty beast of an database. I need to loop through a table (application) and pull values out of other tables (some are aggerate / averages /etc values) using the application_id from the application table. So far I have: declare @id INT declare app cursor for SELECT applicat...
How can I SELECT where there is a specific link in a linking table or there is no link?
I have two tables: Videos -------------- VideoID VideoGroupID CreatorUserID and VideoTags -------------- VideoID TagID I want to select all of the VideoGroupIDs where the CreatorID is 1, 2, or 3 and there is either tag 10, 11, or 12 associated with the video OR there are no tags associated with the video. Can I do this in a single...
T-SQL Unicode "word" definition
I'm new to Unicode in Microsoft SQL Server 2005 / 2008. I converted my DB to use NVarChar() instead of VarChar(). I found to my surprise that the sorting is different than with VarChar(). I found this other reference here on StackOverflow, for SQL Sorting and hyphens that explained Unicode sorting is done on a "word" basis. After mor...
How do I define my own hardcoded Ceilings and Floors in Sql Server?
Hi folks, I have some sql statements that calculates some numbers. It's possible (with bonus points/penalty points) that a person could get a final score OVER 100 (with bonus points) or UNDER 0 (with penalties). How can i make sure that the value calculated, if it exceeds 100 it get's maxed at 100. Alternatively, if the score ends up ...
SQL counts performance (sql 2000)
I've seen in the post called something like "a small change you've done that has increased the performance of your application" a comment about changing from: SELECT U.userid,groups_in=( SELECT COUNT(*) FROM usersgroup WHERE userid=U.userid) FROM tbl_users U to: SELECT U.userid, groups_in FROM users U LEFT JOIN ( ...
MS SQL server casting without exception.
Hello world! Is there any "convert" function in MS SQL server that allows to cast types safely(without throwing exception). I need something like "tryParse" in C# lang but as SQL statement. More detailed, I need the following statement returns zero or any else but throwing exception. select convert(float, 'fjsdhf') thanks in advance...
SQL Server function to pass type as param
Is possible to pass type as param in SQL function? thank in advance. ...
What is the difference between SQL, PL-SQL and T-SQL?
What is the difference between SQL, PL-SQL and T-SQL? Can anyone explain what the difference between these three are and a scenario where each would be relevantly used? ...