Using SQL Server 2005: How can I read a file into a SPROC using T-SQL? So, imagine I have a CSV file like so: ID,OtherUselessData<br/> 1,asdf<br/> 2,asdf<br/> 3,asdf<br/> etc... I basically want to do this: Select * from mytable where id in (select id from txtFile) ...
I have the following T-SQL code (in SQL 2000) which functions correctly: INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey, fcpoptext, fcpopval, fnorder, fcpopacces) SELECT CSP69.fcpopkey, C...
I have some C# code that dynamically generates an SQL query and executes it via IDbCommand.ExecuteScalar(). This works fine; there's exactly one result that matches my query in the DB, and that result is always returned. But just recently, as the first step in a refactoring to support multiple matches in the DB, I replaced the call to ...
I've got a really long running SQL query (data import, etc). It's crap - it uses cursors and it running slowly. It's doing it, so I'm not too worried about performance. Anyways, can I pause it for a while (instead of canceling the query)? It chews up a a bit of CPU so i was hoping to pause it, do some other stuff ... then resume it. I...
I'm running a T-SQL script that drops a database and then restores it. The script runs against a SQL Server 2008 database. Sometimes there is a problem with the backup file and the database gets stuck in the restoring state. IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = 'dbname') BEGIN ALTER DATABASE [dbname] ...
UPDATE Finally managed to work it out! Thanks for all the help from everyone. If you spot any potential errors or scope for improvement in my query please let me know. SELECT * FROM TBL_CAMPAIGNS C INNER JOIN TBL_MEMBERS M ON C.campaign_MemberId = M.members_Id INNER JOIN TBL_CAMPAIGNS_CHARITIES CC ON C.campaign_Key = CC.camcha...
Hi all... we have a view in our database which has an ORDER BY in it. Now, I realize views generally don't order, because different people may use it for different things, and want it differently ordered. This view however is used for a VERY SPECIFIC use-case which demands a certain order. (It is team standings for a soccer league.) Th...
I have to round down all incoming data with type DECIMAL(14,3) which has 3 decimal digit to the last one. I.e.: 100; 100.0; 100.00; 100.000 -> 100 100.2; 100.02; 100.20; 100.22 -> 100.xx but 100.221 -> 100.22 100.229 -> 100.22 Using which SQL operator can I check that residue of division in decimal digit is greater then zero? ...
I have a sproc that was taking far more time than I expected. I pulled out the SQL and ran it with just DECLARED variables for the parameters. It ran nearly instantaneously (versus a reliable 8 seconds with the sproc). This is the same SQL on same machine, returning the same data. How can I figure out and fix what is causing the spro...
Hi folks, I have three variables :- @ScoreA DECIMAL(10,7) @ScoreB DECIMAL(10,7) @ScoreC DECIMAL(10,7) @FinalScore DECIMAL(10, 7) I wish to get the average of the three scores. BUT 1, 2 or all 3 values might be zero. Eg. scenarios: A = 1.4, B=3.5, C=5.0; FinalScore = 3.3 A = 0.0, B=0.0, C=0.0; FinalScore = 0.0 A = 1.1, B=0.0, C=0.0...
Given a table, Table, with columns N1, N2, N3, how can I get all combinations satisfying the condition N1 + N2 + N3 > 10? For example, querying the table: N1 N2 N3 Row1 1 5 4 Row2 4 4 3 Should give the result: N1 N2 N3 Row1 4 5 4 Row2 4 4 4 Row...
Pulling my hair out with this query. Maybe some of the experts here can see what I'm doing wrong? I have a TimeSheetTime Table as follows: CREATE TABLE TimeSheetTime( TimeSheetTimeID int IDENTITY(1,1) NOT NULL, TimeSheetItemID int NOT NULL, OffsetToEntryDate tinyint NOT NULL, Hours float NOT NULL ) This is populated w...
I know this isn't recommended but my development server is 2008 while my production server is 2000. What is the easiest way to copy databases back and forth? For example, I just created a database with a robust date table which I intend to use in my queries. I figured the easiest way would be to back up the database and restore it t...
I have a StackOverflow-like tagging system for a database I'm working on. And I'm writing a stored procedure that looks for results based on an undetermined number of tags in a WHERE clause. There could be anywhere between 0 and 10 tags to filter results. So for example the user could be searching for items tagged with 'apple', 'orang...
How to retrieve the names of Non-system -tables in a database from SQL Server 2000 by a T-SQL query? ...
How to avoid the time portion of the datetime in sql server. I wants only the date portion to insert my database. Thanks in Advance ...
I have 2 strings in SQL and the REPLACE function only works on 1 of them, why is that? Example 1: SELECT REPLACE('18 286.74', ' ', '') Example 2: SELECT REPLACE('z z', ' ', '') Example 1's output is still "18 286.74" whereas Example 2's output is "zz". Why does SQL not react the same way to both strings? UPDATE: When running se...
I am using sp_MSforeachtable to get a rowcount of specific tables in my database. I want these ordered by name. How do I add an ORDER BY clause to sp_MSforeachtable? ...
If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it? ...
This should not be this hard. I simply need the following: SET @DueDate = CONVERT (DATETIME, '01/01/2010') However, I need it programatically so that if it were March of 2010, the date given would be '01/01/2011'. I know it's simple, but my brain isn't coming up with it. I'm sure it's with a DateAdd and getdate(). ...