Hi, I have a stored procedure in T-Sql with this code at the end of it: UPDATE @Results SET Percentage = CASE B.Total WHEN 0 THEN 0 ELSE (CAST(Number AS FLOAT)/B.Total) END FROM @TotalAnswersPerQuestion AS B WHERE @Results.QuestionNumber=B.QuestionNumber The temp table @Results is defined and correctly used in this store procedure jus...
int i=99; string s=i.ToString("D4"); //s=="0099" Please advice on efficient implementation of preceding zeroes of numbers in textual format. ...
Hello all, I have tried to make use of linked servers in SQL Server 2008 by doing the following to access a Microsoft Access 2003 Table. EXEC sp_addlinkedserver access1t, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb' EXEC sp_addlinkedsrvlogin access1t, FALSE, Null, Admin, Null GO CREATE VIEW TI001APCE1265 AS SEL...
Is there a equivalent clause to CONNECT BY of Oracle in SQL Server. The requirement to build a category tree using a parentId field. ...
Hello all, I have executed two identical bcp commands on two different setups with the same data. Machine A = Windows Vista machine which is running SQL Server 2008 Machine B = Windows Server 2003 machine running SQL Server 2005 The output text file of the bcp command is different! For a start, Machine B does not add column names to ...
I'm working on a SQL script where I need to refer to the database name in multiple stops. This script will be used to run against different databases so I wanted to store the database name in a variable so you only need to change the name in one location in the script. The main problem I have with this script is with the USE command. S...
Hello all, I have just noticed that one of the views I create from Microsoft Access in SQL Server via a linked server is interpreted differently in different machines/setups etc. Example: EXEC sp_addlinkedserver acc465tghv, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb' EXEC sp_addlinkedsrvlogin acc465tghv, TRUE...
In Microsoft SQL Server 2005 or above, I would like to get the first row, and if there is no matching row, then return a row with default values. SELECT TOP 1 ID,Name FROM TableName UNION ALL SELECT 0,'' ORDER BY ID DESC This works, except that it returns two rows if there is data in the table, and 1 row if not. I'd like it to always...
I am storing price data events for financial instruments in a table. Since there can be more than one event for the same timestamp, the primary key to my table consists of the symbol, the timestamp, and an "order" field. When inserting a row, the order field should be zero if there are no other rows with the same timestamp and symbol. ...
Hi I have a sql job step like this Declare @Result varchar(255) exec myprocedure @Result = @Result output What I want to do: if @Result = 'Error' then mark the job as failed, how can I achieve that? ...
Scenario I've got an application written in C# that needs to dump some data every minute to a database. Because its not me that has written the spec, I have been told to store the data as XML in the SQL Server database and NOT TO USE the "bulk upload" feature. Essentially I just wanted to have a single stored procedure that would take X...
Hi, I created a simple stored proc which returns a string create proc im.mmtest as select 'testvalue' go then I call the SP and set its return value to a variable. This block is executed by selecting all three lines and pressing F5. I do see the value when the exec statement is executed, but I do not get the string value back in sel...
I have a situation where I have an incoming data value that may or may not have leading zeroes. I need to match that to a field/row in a SQL Server table. The field value in the SQL Server database may or may not have leading zeroes as well. So, I might have: incoming = 5042800138 and the value in db can be any of 5042800138, 05042800...
I have an xml document of the bible as <bookcoll> <book> <bktshort>Matthew</bktshort> <chapter><chtitle>Chapter 1</chtitle> <v>The book of the generation of Jesus Christ, the son of David, the son of Abraham. </v> <v>Abraham begat Isaac; and Isaac begat Jacob; and Jacob begat Judas and his brethren; </v> .. </chapter> <chapter><chtitl...
I am looking for a way to write the below procedure without using a CURSOR or just a better performing query. CREATE TABLE #OrderTransaction (OrderTransactionId int, ProductId int, Quantity int); CREATE TABLE #Product (ProductId int, MediaTypeId int); CREATE TABLE #OrderDelivery (OrderTransactionId int, MediaTypeId int); INSERT INTO #P...
I have a SqlServer db that I would like to port to MySQL. What's the best way to to this. Things that need to be ported are: - Tables (and data) - FileStream --> MySQL equivalent? - Stored Procedures - Functions ...
Hi I wrote the following code, it works fine, but it takes like 3 sec to complete if the table is containing a million record. Is there a way to optimize the following code. DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; DECLARE @Page_Size int; DECLARE @Page_Number int; DECLARE @Lower_Bound int; DECLARE @Upper_Bound int; SET @Page_Size...
Hi folks, I have the following sql statement, which works perfectly fine. I was hoping to see how this could be refactored so it doesn't require the use of RANK/PARTITION ... if possible. SELECT LogEntryId, FileId, CreatedOn, EventTypeId FROM (SELECT a.LogEntryId, a.FileId, a.CreatedOn, a.EventTypeId, RANK() OVER (PARTITION B...
suppose i have two tables. articles and comments. when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid) how do I do that? I can get it done, but I do not know if my way would be effici...
Please help me undestand how order by influences to over clause. I have read msdn and one book and still misunderstood. Let's say we have such query: SELECT Count(OrderID) over(Partition By Year(OrderDate)),* FROM [Northwind].[dbo].[Orders] order by OrderDate The result is that each raw has the column with the value how many entr...