I have a table with 6 million + records, but the first field has a " at beginning and the last field has " at the end i guess when doing a bulk insert they forgot to remove it. I want to run an update query which will remove the " from those 2 fields Sample data - Field 1 "18157142 "18157152 "18157159 "18157177 "18157189 "181571...
I have a trigger that monitors changes to database objects. Below is the code when I created the trigger: CREATE TRIGGER db_trg_ObjectChanges ON DATABASE FOR ALTER_PROCEDURE, DROP_PROCEDURE, ALTER_INDEX, DROP_INDEX, ALTER_TABLE, DROP_TABLE, ALTER_TRIGGER, DROP_TRIGGER, ALTER_VIEW, DROP_VIEW, ALTER_SCHEMA, DROP_SCHEMA, ALTER_ROLE, DR...
I have a database table: Col1 | Col2 | Col3 ------------------ 1 | 2 | - 2 | 3 | - 3 | 4 | - 4 | 5 | - Columns 1 and 2 have data but 3 is null. What I want to achieve is to set Col3 to the Col1 value of the previous row (technically the previous row in which the Col1 value equals the Col2 value), to get th...
I would like to know what special characters are allowed to be used in column name in T-SQL, MSSQL. So I know I can use letters and numbers, but are the other characters that are available without using brackets [] to refer to this column? ...
In some SQL dialects, you can state (something as): SELECT * FROM SomeTable WHERE (val1,val2) IN (SELECT val1,val2 FROM SomeOtherTable) But I don't know how to do that in the TSQL (sql server 2k) I am using. I am aware of (and using for now) workarounds like using joins or concatenated values, but is there some syntax in TSQL I...
Hi I am writing a large stored procedure, which creates a dynamic report table, of n columns in size, the first 6 are constant the remainder depend on a few arguments passed to the procedure to create the table with the required columns. The problem that I am having is with the following TSQL DECLARE @columnname VARCHAR(50) SET @column...
I can write something like this with LINQ: var selection = from person in personList let initials = person.FirstName[0] + person.LastName[0] select initials; Can I do something similar with SQL, like maybe: SELECT @Initials FROM [Person] SET @Initials = SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (P...
Example: SELECT * FROM [User] WHERE [Value1] > 0 AND [Value2] / [Value1] > 3 I would want to avoid a division-by-zero situation. Can I be sure that the first condition be checked first and the second one will never get hit if the first one is false? I suppose the query planner is allowed to rearrange the sequence if it will yield more...
Is there away to cause a script to prevent running if an if statement is true even with "GO"'s? For example I want to do something similar to the following: insert into table1 (col1, col2) value ('1', '2') GO if exists(select * from table1 where col1 = '1') BEGIN --Cause Script to fail END GO insert into table1 (col1, col2) val...
Been working with SQL Server since it was Sybase (early 90s for the greenies) and I'm a bit stumped on this one. In Oracle and DB2, you can pass a SQL batch or script to a stored procedure to test if it can be parsed, then execute conditional logic based on the result, like this pseudocode example: if (TrySQLParse(LoadSQLFile(filename...
Hey folks, Simple question here, and I know there are lots of kludgy ways to do it :) But I was hoping a SQL server expert had a script to easily script all the data out of a table? Or possibly all the tables in a DB? I'm getting tired of copying and pasting data over RDC! :) ...
Can someone please help me out with a query? In Microsoft SQL Server, I have the following query, which executes fine: SELECT * FROM ControlPoint INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID INNER JOIN Site ON Site.SiteID = Project.SiteID WHERE Project.ProjectName LIKE '%Flood%' My problem is, when I try to execute...
Scenario: I am using a T-SQL stored proc (Sql Server Management Studio) to return search matches for text documents using the MS Indexing Service and this (simplified) query: SELECT * FROM openquery( filesystem2, 'SELECT Path, Rank, Filename FROM SCOPE('' "e:\test\documents" '') WHERE CONTAINS('' FORMSOF ...
There's a login/user in SQL Server who is having a problem importing accounts in production server. I don't have an idea what method he is doing this. According to the one importing, this import is working fine in development server. But when he did the same import in production it is giving him errors. Below are the errors he is gettin...
I have a query using a FREETEXTTABLE full text search which works perfectly for every column included in the index except for the primary key. The primary keys are in a format like abcdef123456 and when you search for abcdef123456 you get that one record returned. However, if you search for abcd or 12345 you get no results (unless that...
If i run a TSQL Statement in management studio and run the same the query through SqlDataReader, the latter gives the result faster than the former... Any reason?? ...
I am making a split function in SQL Server 2005. I have already done it by using a while loop . But I am not happy with that. I want to do it using recursive function. I have already done it in C#. Now I am plotting the same in SQL SERVER 2005. But I am getting a compilation error. Here is my code ALTER FUNCTION [dbo].[fnSplit2] ( ...
When we copy a database down from production, we make a backup of the database, zip it up and copy the backup down. Then we have to restore using the SQL Server GUI, which involves navigating through several menus and windows. As far as I know, you can not do this with SQL Server's built in stored procedures because you may not know the ...
How can I store SQLSERVER's raiserror message in C# ? Thanks in advance ...
I have a T-SQL view with integer fields. I need a report on a monthly basis regarding the difference from one month to the next, i.e. so many people were engaged in a particular activity on 8am of the 1st of this month, so many the previous month, here is the difference. The numbers fluctuate all the time. I need a variance between 2 sna...