I have a scheduled AM process on SQL Server 2008 that imports data from Oracle using a linked server. I am overwriting the imported data from Oracle using drop table, then select into pattern Apparently, the presence of the "order by" affects my end result! Take a look. --This works fine to give me the one row I'm expecting from the ...
When 2 decimal(30,10) numbers are divided in Sql Server 05, 2 last decimals seem to be getting lost (not even rounded off, simply truncated). For example: Declare @x decimal(30,10) Declare @y decimal(30,10) Declare @z decimal(30,10) select @x = 2.1277164747 select @y = 4.8553794574 Select @z = @y/@x select @z Result: 2.28196731...
I have a situation where I am displaying records on a page, and I need a way for the user to select a subset of those records to be displayed on another page. These records aren't stored anywhere then, it is a dynamically generated thing. I know I could use jquery to pass in a comma-separated value to my other web page, but I'm not sur...
So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order: ORDER BY date_sent NULLS FIRST What is comparable in SQL Server? There are these alternatives, assuming the date values are NULL or in the past: ORDER BY ISNULL(date_sent, GETDATE()) DESC ORDER BY (CASE W...
Im going through a project I have taken over, and on the database side I have noticed that the previous programmers have written a bunch of triggers to delete child records. The thing is, these records already have a a foreign key relationship with the parent record I am deleting. The delete triggers are nothing but simple delete state...
Hi, I have a table in which there are records about works accesing entrance doors. DECLARE @doorStatistics TABLE ( id INT IDENTITY, [user] VARCHAR(250), accessDate DATETIME, accessType VARCHAR(5) ) Sample records: INSERT INTO @doorStatistics([user],accessDate,accessType) VALUES ('John Wayne','2009-09-01 07:02:43.000','IN') INSERT IN...
I need to split an existing table in to two or more tables on Sql Server 2005. The table already has more than a thousand of rows. For eg current table has cols A, B, C, D, E plus an id column. An I need to add A, B, C rows to another table in another database and add D, E to another table in another database. I know that it is weird. ...
I'm using the general construct shown below for my TSQL stored procedures. This works fine for returning the error information to the calling application code. However, I would also like to log the error in the database itself. How can I accomplish this in SQL Server 2005? BEGIN TRY TSQL... END TRY BEGIN CATCH DECLARE @ErrorMessage N...
Can anyone tell me how to insert a record in a table in the following case: I have 2 tables: create table #temp1(c4 int, c5 int,c3 int) ...and: create table #temp2(c1 int, c2 int) create procedure sptemp as begin select c1,c2 from #temp2 end Now I want to insert records into #temp1 table using the procedure as: insert in...
We are trying to create a stored procedure, however we run into the following error message: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. This error occurs at line 33, which reads as follows: SET @MINTIME = (SELECT CONVERT(varchar,DATEADD(MONTH,-1,G...
Given this data set: SummaryID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 25 ...
I'm using a version of SQL Server 2005 that does not support the profiler, trying to figure out how best to compare the performance of two stored procedures. I've run the execution plan for each, but it's not clear to me which of the provided metrics I should be focusing on. Do I go through and add up the various costs? What's the bes...
Hi, I want to find fiscal month and year based on a date. How Can I do it using TSQL? I have SQl server 2008. Any help? Regards Manjot ...
Hello everyone, I am using SQL Server 2008 Enterprise for development. I find from SQL Server logs, there are items like, 2009-09-20 19:54:33.55 spid53 Starting up database 'DummyOrderDB'. My confusion is, I think we could only start/stop database server instance (the contained database will be started/stopped when the containin...
am writing stored procedure in mysql 5.1 and call from c#.net ..and i want use that procedure in sql server..if its possible..please explain.. ...
Hi, I have 2 tables one with a lot of records(table 1), and a second(table 2) with similar data but with far fewer records. On a regular basis i need to add a marker to records in the larger table where there is a corresponding record in the smaller table. For example this could be an email address. So if email address exists in the s...
Hi All, I found one question answered with Row_Number() function in where clause. When I tried one query, I was getting the following error. "Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses." Here is the query I tried. If somebody knows how to solve this, please let me know....
I should probably be able to find an answer to this but my Google-fu is weak today. When I have the same stored proc called multiple times by a web app, do the calls queue up or do they run independently? ...
My employer has developed a utility that will run a stored procedure line by line against a DataTable, passing the fields of each row as parameters into the Stored Procedure. This is particularly useful for automated imports. However, I now need to extend this to provide a transactional-ized version so that we can see the potential resu...
I'm trying to randomly insert values from a list of pre-defined values into a table for testing. I tried using the solution found on this StackOverflow question: stackoverflow.com/.../update-sql-table-with-random-value-from-other-table When I I tried this, all of my "random" values that are inserted are exactly the same for all 3000 re...