I have a TSQL Stored Procedure tsql__sp__A which does two things: (a) Creates a temp table #tempTable that has SELECT data from a complex SELECT query. (b) Calls a CLR managed Stored Procedure clr__sp__B for each row that does computation on row parameters. Question: Is it possible to access #tempTable from CLR procedure clr__sp_...
I need to write an Insert, Update Trigger on table A which will delete all rows from table B whose one column (say Desc) has values like the value inserted/updated in the table A's column (say Col1). How would I go around writing it so that I can handle both Update and Insert cases. How would I determine if the trigger is executed for an...
I have two TSQL EXEC statements EXECUTE (N'MyDynamicallyGeneratedStoredProcedure') -- return 0 on success SELECT @errCode = @@ERROR ; IF (@errCode = 0) BEGIN EXEC 'A Sql Statement using ##temptable created from first', @returnValue END How do I make the two EXEC's synchronous? ; Right now the second EXEC does not wait for the first...
so I have an old database that I'm migrating to a new one. The new one has a slightly different but mostly-compatible schema. Additionally, I want to renumber all tables from zero. Currently I have been using a tool I wrote that manually retrieves the old record, inserts it into the new database, and updates a v2 ID field in the old ...
In MS SQL Server 2005, when encountering an "Arithmetic overflow error converting numeric to data type numeric" during an INSERT, is it possible to discover which column's input value caused the error? There seems to be a number of similar conversion errors that don't report which column provoked the trouble. This makes fixing mistakes ...
I have the following table with data: tag dt value 1 2009/03/01 10.2 2 2009/03/01 12 3 2009/03/01 120 .... 1 2009/03/02 13.2 2 2009/03/02 9 3 2009/03/02 23 .... Basically, for a group of tags, there are values along date (March 1, 2, ...). I would like to have data in a different view li...
I have a case where I wanna choose any database entry that have an invalid Country, Region, or Area ID, by invalid, I mean an ID for a country or region or area that no longer exists in my tables, I have four tables: Properties, Countries, Regions, Areas. I was thinking to do it like this: SELECT * FROM Properties WHERE Country_ID NOT ...
I am presenting to a final authority evaluation scores for employees. Each row is an employee’s data and since the categories to be evaluated can change from period to period the column names cannot be hardcoded in the Stored Procedures. I have already devised the following solution. 1 Create a temp table 2 Dynamically use the Alter...
I've got a requirement to pass parameters as Xml to my stored procedures. I have a WCF service in the middle tier that makes calls to my data layer which in turn forwards the request to the appropriate stored procedure. The design is that the WCF service is responsible for building the Xml to pass to the Repository. I'm just wondering...
I have a table with columns id, a, and b. a+b should be unique, but this is a legacy database that is not constrained correctly. How can I get a set of ids for records in which a+b is not unique? If I have ID A B 1 2 3 2 2 3 3 1 3 4 1 4 Then I want to get records 1 and 2 back from the query. ...
We have several SQL Server 2000 databases (I know, we need to upgrade) that have the same structure and have them set up to replicate to another server. The problem is that whenever I have to change the structure (which is usually pretty easy to do on all databases, especially with tools from Red Gate) I have to stop the replication, ma...
Probably an easy-to-answer question. I have this procedure: CREATE PROCEDURE [dbo].[AccountExists] @UserName nvarchar(16) AS IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName) SELECT 1 ELSE SELECT 0 When I have ADO.NET code that calls this procedure and does this: return Convert.ToBoolean(sproc.ExecuteScalar()); Either...
I'm trying to write a query to see if an engineer visited his job in a agreed time slot. This is my query so far: SELECT v.[VISITDATE], CONVERT(VARCHAR, v.[STARTTIME], 105) AS 'Startdate', CONVERT(VARCHAR, v.[STARTTIME], 108) AS 'StartTime', CONVERT(VARCHAR, v.[bookeddate], 105) AS 'BookedDate', CONVERT(VARCHAR, t.[star...
I have a MS SQL 2005 database with a table Test with column ID. ID is a identity column. I have rows in this table and all of them have their corresponding ID autoincremented value. Now I would like to change every ID in this table like this: ID = ID + 1 But when I do this I get error: Cannot update identity column 'ID'. I've trie...
Hello I am coming from Bosnia and Herzegovina and in our county the smallest note bill is 0.05, Now the government pushing us to our retrial prices rounding on 0.05 or at 0.00. Therefor I want to create SQL Scalar Valued Function for rounding the prices on given value. Is there some build in solution so I can save resource of my querie...
I have a table with varchar(128) column with data like: word1, word2, , word3, word4 word1, word2, word3, , word1,,,, ; word2 ;word1 word2, word3 I have to update this table, making words reverse order: word4, ,word3,,word2 ,word1 ,,word3, ,word2, word1 Could you help me to do it using only single sql query?? ...
I am fairly new to Linq To SQL but trying to run what should be a fairly simple SQL query and can't figure out how to make it play nice in LINQ. SELECT Users.Id, Users.Id AS Expr1, Users.FirstName, Users.LastName, User_x_Territory.UserID FROM Users LEFT OUTER JOIN User_x_Territory ON User_x_Territory.UserID = U...
I have this statement in T-SQL. SELECT Bay From TABLE where uid in ( select B_Numbers from Info_Step WHERE uid = 'number' ) I am selecting "multiple" BAYs from TABLE where their uid is equal to a string of numbers like this: B_Numbers = 1:45:34:98 Therefore, I should be selecting 4 different BAYs from TABLE. I basically need t...
There is a field in a 3rd party database that I need to group on for a report I'm writing. The field can contain a few different types of data. First it could contain a 3 digit number. I need to break these out into groups such as 101 to 200 and 201 to 300. In addition to this the field could also be prefaced with a particular letter...
hi guys, I have 2 dates. I want to get number of days between 2 dates in storedprocedure. ...