How to Select data from Table from a DSN in T-SQL?
How can I get data from a database that I can only access through an ODBC Data Source (DSN) in T-SQL MS SQL Server, any version? ...
tsql
T-Sql cursor not proceeding on fetch
Hi,I know that cursors are frowned upon and I try to avoid their use as much as possible, but there may be some legitimate reasons to use them. I have one and I am trying to use a pair of cursors: one for the primary table and one for the secondary table. The primary table cursor iterates through the primary table in an outer loop. the s...
Building a temp table /map
I'm working on a stored procedure in SQL Server 2000 with a temp table defined like this: CREATE TABLE #MapTable (Category varchar(40), Code char(5)) After creating the table I want to insert some standard records (which will then be supplemented dynamically in the procedure). Each category (about 10) will have several codes (typicall...
How do you remove two digits in smallmoney with tsql?
Sign. My tsql kungfu sucks. I have a fee that is of type small money. When I exported as SQL from MS Access the column that represents the fee was stored as text, for example $3.28 was stored as "00000328". When I imported this into MS SQLServer I changed the data type to smallmoney, but it was stored as 328. How do I make all the values...
How do I cancel a Delete in SQL
I want to create a trigger to check what is being deleted against business rules and then cancel the deletion if needed. Any ideas? Update The solution used the Instead of Delete trigger. The Rollback tran stopped the delete. I was afraid that I would have a cascade issue when I did the delete but that did'nt seem to happen. Maybe a tri...
T-SQL fuzzy lookup without SSIS?
SSIS 2005/2008 does fuzzy lookups and groupings. Is there a feature that does the same in T-SQL? ...
How do i update a table column based on a condition?
Two table: StoreInfo: UserId uniqueidentifier StoreNo nvarchar UserName nvarchar Password nvarchar UserInfo: UserId uniqueidentifier UserName nvarchar Password nvarchar the UserId on StoreInfo is currently null. How do i update StoreInfo's UserId with UserInfo's UserId based on StoreInfo's UserName and Password is match to the UserNa...
Summarize aggregated data
I have a table like as follows: SoftwareName Count Country Project 15 Canada Visio 12 Canada Project 10 USA Visio 5 USA How do I query it to give me a summary like... SoftwareName Canada USA Total Project 15 10 25 Visio 12 ...
Select from different tables via SQL depending on flag
I have a script to extract certain data from a much bigger table, with one field in particular changing regularly, e.g. SELECT CASE @Flag WHEN 1 THEN t.field1 WHEN 2 THEN t.field2 WHEN 3 THEN t.field3 END as field, ...[A bunch of other fields] FROM table t However, the issue is now I want to do other processing on the d...
How to Round a Time in T-SQL
I'me looking for a function that would receive a time and would round it to the next/previous hour / half-hour / quarter / minute. ...
How do I avoid multiple CASTs in sql query?
Hi, I have the following sql query for transforming data but is it possible to save the value of the int in some variable to avoid casting multiple times? update prospekts set sni_kod = case when cast(sni_kod as int) >= 1000 and cast(sni_kod as int) <= 1499 or cast(sni_kod as int) >= 1600 and cast(sni_kod as int) <= 2439 then...
When to create a new SQL Server Index?
Obviously (methinks), creating an index on a BIT column is unnecessary. However, if you had a column that you need to search in which every value is likely unique, like BlogPost or StreetAddress or something, then an index seems appropriate (again, methinks). But what's the cutoff? What if you expect 10,000 rows and you'll have about 20...
syntax to remove permissions previously granted via GRANT EXECUTE?
If I grant execute permissions to a role via GRANT EXECUTE ON [DBO].[MYPROC] TO MY_ROLE what's the equivalent syntax to remove them? ...
How can I convert a varchar field of the form: YYYYMMDD to a datetime in T-SQL?
How can I convert a varchar field of the form YYYYMMDD to a datetime in T-SQL? Thank you. ...
MS SQL 2005 Table to XML
I have a simple table in SQL Server 2005, I wish to convert this to XML (using the "FOR XML" clause). I'm having trouble getting my XML to look like the required output. I've tried looking through various tutorials on the web, but I am struggling. Can someone help? The table I have looks like this TYPE,GROUP,VALUE Books,Hardback,56 Bo...
Count work days between two dates in T-SQL
How can I calculate the number of work days between two dates in SQL Server? Monday to Friday and it must be T-SQL. ...
Splitting time + duration into intervals in t-sql
Hi, Does anyone know of a simple method for solving this? I have a table which consists of start times for events and the associated durations. I need to be able to split the event durations into thirty minute intervals. So for example if an event starts at 10:45:00 and the duration is 00:17:00 then the returned set should allocate 15 ...
Access Web Service from a SQL Server 2005 Stored Procedure
Hi everyone, Currently I have a stored procedure that makes some updates to a table, and after it makes them, I need to call a web service. The procedure uses a transaction, and I need to call the web service at the end of the transaction. If the web service call should fail, the transaction would get rolled back. I need to know how d...
How to split date column & sum it up
I have a query where i have a date column (time) which tells about "IN" & "OUT" timing of the people attendance by this single column My queries are :- 1) How to get the daily attendance of each employee 2) How to come to know if the employee is present less than 5 hours Please let me know the queries in SQL server. ...
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
Using MSSQL2005, Can I truncate a table with a foreign key constraint if I first truncate the child table(the table with the primary key of the FK relationship)? I know I can use a DELETE without a where clause and then RESEED the identity OR Remove the FK, truncate and recreate but I thought as long as you truncate the child table you...