Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts

Thursday, March 29, 2012

Cursor versus While Loop

I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.

Thanks!!The short answer boils down to both options are bad, and you are correct that the WHILE/SELECT/DELETE choice is even worse than the cursor.

More than 99 percent of the time there is a set-based solution that is hundreds or thousands of times faster/better than any loop can be, so the set based solution is what should be your "holy grail". Fix the problem instead of trying to find ways to make the loop less of an annoyance!

-PatP|||Why don't you post what it is you're trying to dosql

Cursor Update problem

I am using a cursor to take information from a temp table and either insert
or update another table. I am using a while loop to fetch all the vaules
from the cursor and then I close and deallocate the cursor. Everything runs
fine the first time but when I run the procedure again it updates the first
record with the information from the last record process from the time
before. If it helps I am running it as a job just like it will run when
completed. Ideas on why I am getting data from the previous run?Can you poste the code instead just a brief description of the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Shannon Thompson" wrote:

> I am using a cursor to take information from a temp table and either inser
t
> or update another table. I am using a while loop to fetch all the vaules
> from the cursor and then I close and deallocate the cursor. Everything ru
ns
> fine the first time but when I run the procedure again it updates the firs
t
> record with the information from the last record process from the time
> before. If it helps I am running it as a job just like it will run when
> completed. Ideas on why I am getting data from the previous run?|||Here is my code but I cannot give you the code for stored procedures call
from this code here because they are Encrypted and part of the software
program I am integrating with.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xxx]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE procedure xxxx
AS
/*
** Declare & initialize Local Variables
***
*/
DECLARE @.iReturnCode int,
@.iRet int,
@.vchFirstName nvarchar(255) ,
@.vchLastName nvarchar(255) ,
@.vchAdSource nvarchar(255) ,
@.vchOnyxCode nvarchar(255) ,
@.vchAddress1 nvarchar(255) ,
@.vchCity nvarchar(255) ,
@.chStateCode nvarchar(50) ,
@.vchPostCode nvarchar(40) ,
@.chCountryCode nvarchar(50) ,
@.vchPhoneType nvarchar(50) ,
@.vchPhoneNumber nvarchar(40) ,
@.vchBestTime nvarchar(255),
@.vchEmail nvarchar(255),
@.iHt_Feet nvarchar(50),
@.iHt_Inches nvarchar(50),
@.iWeight nvarchar(50),
@.dtDOB nvarchar(50),
@.vchInsurance nvarchar(255),
@.vchOtherIns nvarchar(255),
@.vchInsuranceType nvarchar(255),
@.vchSem nvarchar(255),
@.vchSemSrc nvarchar(255),
@.dtTimeStamp nvarchar(255),
@.iIndividualId int ,
@.iIncidentId int,
@.onyx_cursor cursor,
@.chInsUpd nchar(1),
@.iPhoneTypeId int,
@.getDate datetime,
@.dtPreviousUpdateDate datetime,
@.iHeight int,
@.dtUpdate datetime,
@.bmi float
set @.iReturnCode = 0
set @.iRet = 0
set @.getDate = getDate()
set @.bmi = 0
set @.onyx_cursor = cursor
--local Scroll Keyset Optimistic
FOR Select
vchFirstName,
vchLastName,
vchAdSource,
vchOnyxCode,
vchAddress1,
vchCity,
chStateCode,
vchPostCode,
chCountryCode,
vchPhoneType,
vchPhoneNumber,
vchBestTime,
vchEmail,
iHt_Feet,
iHt_Inches,
iWeight,
dtDOB,
vchInsurance,
vchOtherIns,
vchInsuranceType,
vchSem,
vchSemSrc,
dtTimeStamp
from CallCenter_Temp
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
-- loop while there are still records in table
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
(convert(float,ROUND(@.iHt_Feet,0))*12))
IF @.iHeight <> 0 AND @.iWeight <> 0
BEGIN
declare @.meters float,
@.totalinches float,
@.kilos float,
@.metersq float
set @.totalinches = convert(float,@.iHeight)
set @.meters = @.totalinches/39.36
set @.kilos = convert(float,@.iWeight)/2.2
set @.metersq = @.meters * @.meters
set @.bmi = Round(@.kilos/@.metersq,0)
END
set @.vchFirstName = UPPER(@.vchFirstName)
set @.vchLastName = UPPER(@.vchLastName)
set @.vchAddress1 = UPPER(@.vchAddress1)
set @.vchCity = UPPER(@.vchCity)
set @.chStateCode = UPPER(@.chStateCode)
set @.chCountryCode = UPPER(@.chCountryCode)
-- determine phone type
SELECT @.iPhoneTypeId =
CASE LOWER(RTRIM(@.vchPhoneType))
WHEN 'home' THEN 119
WHEN 'cell' THEN 103
WHEN 'work' THEN 102
ELSE 119
END
-- if no Onyx Id is listed then search for the individual
IF @.iIndividualId is null
BEGIN
-- check to see if Person is in Onyx
exec @.iRet = wbocpscOnyxTalley
@.vchFirstName,
@.vchLastName,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
NULL
if (@.iRet <> 0)
begin
--update
set @.chInsUpd = 'U'
set @.iIndividualId = @.iRet
end
else
begin
--insert
set @.chInsUpd = 'I'
end
end
-- iIndividualId is given so this is an update
ELSE
BEGIN
set @.chInsUpd = 'U'
END
print @.chInsUpd + ' ' + @.vchLastName
if @.chInsUpd = 'I'
begin
exec @.iReturnCode = wbospsiIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
119,
null,
null,
1,
1,
0,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1,
1
if @.iReturnCode <> 0
begin
print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
ELSE
begin
select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = ospsgCheckRecordLock
@.dtUpdate OUTPUT,
@.dtPreviousUpdateDate,
1
-- if error returned then must change dtUpdateDate to current
if (@.iReturnCode <> 0)
begin
UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = wbospsuIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
null,
null,
null,
1,
1,
0,
null,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1
if @.iReturnCode <> 0
begin
print 'update failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'updated ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
end
--fetch next record
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
END
CLOSE @.onyx_cursor
DEALLOCATE @.onyx_cursor
return @.iReturnCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Can you poste the code instead just a brief description of the problem?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Shannon Thompson" wrote:
>|||Shannon,
The cursor is based on a permanent table. How are you feeding this table
before calling the sp?
AMB
"Shannon Thompson" wrote:

> Here is my code but I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[xxx]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE procedure xxxx
> AS
> /*
> ** Declare & initialize Local Variables
> ***
> */
>
> DECLARE @.iReturnCode int,
> @.iRet int,
> @.vchFirstName nvarchar(255) ,
> @.vchLastName nvarchar(255) ,
> @.vchAdSource nvarchar(255) ,
> @.vchOnyxCode nvarchar(255) ,
> @.vchAddress1 nvarchar(255) ,
> @.vchCity nvarchar(255) ,
> @.chStateCode nvarchar(50) ,
> @.vchPostCode nvarchar(40) ,
> @.chCountryCode nvarchar(50) ,
> @.vchPhoneType nvarchar(50) ,
> @.vchPhoneNumber nvarchar(40) ,
> @.vchBestTime nvarchar(255),
> @.vchEmail nvarchar(255),
> @.iHt_Feet nvarchar(50),
> @.iHt_Inches nvarchar(50),
> @.iWeight nvarchar(50),
> @.dtDOB nvarchar(50),
> @.vchInsurance nvarchar(255),
> @.vchOtherIns nvarchar(255),
> @.vchInsuranceType nvarchar(255),
> @.vchSem nvarchar(255),
> @.vchSemSrc nvarchar(255),
> @.dtTimeStamp nvarchar(255),
> @.iIndividualId int ,
> @.iIncidentId int,
> @.onyx_cursor cursor,
> @.chInsUpd nchar(1),
> @.iPhoneTypeId int,
> @.getDate datetime,
> @.dtPreviousUpdateDate datetime,
> @.iHeight int,
> @.dtUpdate datetime,
> @.bmi float
> set @.iReturnCode = 0
> set @.iRet = 0
> set @.getDate = getDate()
> set @.bmi = 0
> set @.onyx_cursor = cursor
> --local Scroll Keyset Optimistic
> FOR Select
> vchFirstName,
> vchLastName,
> vchAdSource,
> vchOnyxCode,
> vchAddress1,
> vchCity,
> chStateCode,
> vchPostCode,
> chCountryCode,
> vchPhoneType,
> vchPhoneNumber,
> vchBestTime,
> vchEmail,
> iHt_Feet,
> iHt_Inches,
> iWeight,
> dtDOB,
> vchInsurance,
> vchOtherIns,
> vchInsuranceType,
> vchSem,
> vchSemSrc,
> dtTimeStamp
> from CallCenter_Temp
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into
> @.vchFirstName,
> @.vchLastName,
> @.vchAdSource,
> @.vchOnyxCode,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> @.chCountryCode,
> @.vchPhoneType,
> @.vchPhoneNumber,
> @.vchBestTime,
> @.vchEmail,
> @.iHt_Feet,
> @.iHt_Inches,
> @.iWeight,
> @.dtDOB,
> @.vchInsurance,
> @.vchOtherIns,
> @.vchInsuranceType,
> @.vchSem,
> @.vchSemSrc,
> @.dtTimeStamp
> -- loop while there are still records in table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
> (convert(float,ROUND(@.iHt_Feet,0))*12))
> IF @.iHeight <> 0 AND @.iWeight <> 0
> BEGIN
> declare @.meters float,
> @.totalinches float,
> @.kilos float,
> @.metersq float
> set @.totalinches = convert(float,@.iHeight)
> set @.meters = @.totalinches/39.36
> set @.kilos = convert(float,@.iWeight)/2.2
> set @.metersq = @.meters * @.meters
> set @.bmi = Round(@.kilos/@.metersq,0)
> END
> set @.vchFirstName = UPPER(@.vchFirstName)
> set @.vchLastName = UPPER(@.vchLastName)
> set @.vchAddress1 = UPPER(@.vchAddress1)
> set @.vchCity = UPPER(@.vchCity)
> set @.chStateCode = UPPER(@.chStateCode)
> set @.chCountryCode = UPPER(@.chCountryCode)
> -- determine phone type
> SELECT @.iPhoneTypeId =
> CASE LOWER(RTRIM(@.vchPhoneType))
> WHEN 'home' THEN 119
> WHEN 'cell' THEN 103
> WHEN 'work' THEN 102
> ELSE 119
> END
> -- if no Onyx Id is listed then search for the individual
> IF @.iIndividualId is null
> BEGIN
> -- check to see if Person is in Onyx
> exec @.iRet = wbocpscOnyxTalley
> @.vchFirstName,
> @.vchLastName,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> NULL
> if (@.iRet <> 0)
> begin
> --update
> set @.chInsUpd = 'U'
> set @.iIndividualId = @.iRet
> end
> else
> begin
> --insert
> set @.chInsUpd = 'I'
> end
> end
> -- iIndividualId is given so this is an update
> ELSE
> BEGIN
> set @.chInsUpd = 'U'
> END
> print @.chInsUpd + ' ' + @.vchLastName
> if @.chInsUpd = 'I'
> begin
> exec @.iReturnCode = wbospsiIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> 119,
> null,
> null,
> 1,
> 1,
> 0,
> null,
> null,
> @.iHeight,
> @.iWeight,
> @.bmi,
> null,
> null,
> null,
> @.dtDOB,
> null,
> 'CCLeads',
> @.getDate,
> 0,
> 1,
> 1
> if @.iReturnCode <> 0
> begin
> print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> else
> begin
> print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> end
> ELSE
> begin
> select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = ospsgCheckRecordLock
> @.dtUpdate OUTPUT,
> @.dtPreviousUpdateDate,
> 1
> -- if error returned then must change dtUpdateDate to current
> if (@.iReturnCode <> 0)
> begin
> UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = wbospsuIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> null,
> null,
> null,|||This table (CallCenter_temp) is populated by another stored procedure that
gets a file list from a directory, puts that into a true temp table (gets
created and deleted within procedure) and batch inserts each text file:
BEGIN
/*
** Declare & initialize Local Variables
*/
DECLARE
@.iReturnCode int,
@.MyFile varchar(200),
@.SQL varchar(2000),
@.Path varchar(400),
@.onyx_cursor cursor,
@.vchFileName varchar(255),
@.vchXPCMD nvarchar(255)
select
@.iReturnCode = 0
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
'CCFiles')
BEGIN
DROP TABLE CCFiles
END
CREATE TABLE [dbo].[CCFiles] (
[vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
IF @.iReturnCode = 0
BEGIN
SET @.onyx_cursor = cursor
FOR SELECT
vchFileName
FROM CCFiles
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into @.vchFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
@.vchFileName + '"' +
' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
--SELECT @.SQL
EXECUTE (@.SQL)
IF @.iReturnCode = 0
BEGIN
set @.vchFileName = @.Path + @.vchFileName
set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
--execute master..xp_cmdshell @.vchXPCMD
END
FETCH NEXT from @.onyx_cursor into @.vchFileName
END
END
DROP TABLE CCFiles
return @.iReturnCode
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Shannon,
> The cursor is based on a permanent table. How are you feeding this table
> before calling the sp?
>
> AMB
> "Shannon Thompson" wrote:
>|||> I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
Personally I'd want to decrypt those procs to see if it's feasible to
rewrite your code without a cursor.
http://www.planetsourcecode.com/vb/...6J00S003GU.html
David Portas
SQL Server MVP
--|||Shannon,
In the previous post you are not closing and deallocating the cursor and
this cursor.
AMB
"Shannon Thompson" wrote:
> This table (CallCenter_temp) is populated by another stored procedure that
> gets a file list from a directory, puts that into a true temp table (gets
> created and deleted within procedure) and batch inserts each text file:
> BEGIN
> /*
> ** Declare & initialize Local Variables
> */
> DECLARE
> @.iReturnCode int,
> @.MyFile varchar(200),
> @.SQL varchar(2000),
> @.Path varchar(400),
> @.onyx_cursor cursor,
> @.vchFileName varchar(255),
> @.vchXPCMD nvarchar(255)
> select
> @.iReturnCode = 0
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
> 'CCFiles')
> BEGIN
> DROP TABLE CCFiles
> END
> CREATE TABLE [dbo].[CCFiles] (
> [vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
> EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
> IF @.iReturnCode = 0
> BEGIN
> SET @.onyx_cursor = cursor
> FOR SELECT
> vchFileName
> FROM CCFiles
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
> @.vchFileName + '"' +
> ' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
> ,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
> --SELECT @.SQL
> EXECUTE (@.SQL)
> IF @.iReturnCode = 0
> BEGIN
> set @.vchFileName = @.Path + @.vchFileName
> set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
> --execute master..xp_cmdshell @.vchXPCMD
> END
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> END
> END
> DROP TABLE CCFiles
> return @.iReturnCode
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> "Alejandro Mesa" wrote:
>|||The reason I use a cursor is that having limited SQL Stored Procedure
experience (mainly VBScript SQL experience) I know of no other way to go
record by record to call these stored procedures. I have de-crypted these
procedures but by using these stored procedures instead of recreating them I
can safety insert data into the database without breaking any middle tier
rules of the software and corrupt any of the data.
The store procedures are made to put one person at a time into the database
(when a users clicks save on the web page) not bulk so this is why I am usin
g
the cursor.
"David Portas" wrote:

> Personally I'd want to decrypt those procs to see if it's feasible to
> rewrite your code without a cursor.
> http://www.planetsourcecode.com/vb/...6J00S003GU.html
> --
> David Portas
> SQL Server MVP
> --
>|||Why did you destroy Standard SQL behavior? Why are there more NULLs in
one table than should be in an entire Fortune 500 accounting package?
Do you really have a lot of data elements that are in Chinese and 255
characters long? Why do you have data type prefixes on variable names,
which is a violation of both good programming and ISO-11179? Why do you
have numeric data elements in strings? What kind of total garbage are
trying to get with things like "weight VARCHAR(50)", "@.phonetype
VARCHAR(50)", etc. And you don't seem to be aware of floating
point rounding errors (does your machine have a floating point
processor, or do you want to slow things down with a software floating
point package?)
You keep height in inches or cm then convert it for display. You do
not do this in the database. The syntax for CAST is CAST (<exp> AS
<datatype> ) -- do not use the proprietary CONVERT().
You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
But even worse, you did absolutely no design or research on the data.
Other products have a MERGE or UPSERT statement to do this. The usual
pattern in older products is:
BEGIN
-- insert the new rows
INSERT INTO Foobar
SELECT *
FROM WorkingData AS W
WHERE W.keycol
NOT IN (SELECT keycol FROM Foobar);
-- update the rows are already there
UPDATE Foobar
SET <column>
= (SELECT col FROM WorkingData AS W
WHERE W.keycol = Foobar.keycol)
WHERE keycol IN (SELECT keycol FROM WorkingData);
END;|||Since you have no idea about the situation or the database schema your
posting is just wasted useless space. Please do not bother my thread again
unless you want to know more about the database and can actually just give m
e
a reason to my error. I did not ask for a comments on the coding though
constructive criticism, not code bashing, is appreciated - again I stated I
am a Computer Programmer using SQL code in ASP pages mostly not much done
with SQL Server, I would have done this in VBScript (which I have already
done before) but store procedures are more efficient and reliable.
Oh and the comment about doing design and research on the data, you have no
idea how much design and research in this database I have done. With a
database that all the stored procedures are encrypted (which you cannot
decrypt unless you want to break the software agreement which I play by the
rules maybe you do not), no manuals or references on the database (because i
t
is part of a software program and while they want you to add functionalilty
to the product for your own uses they are not forth coming with how to do
things) and myself being no where near a DBA I think I have a pretty good
understanding of this database which you clearly do not because you did not
ask!
Thanks but no Thanks for your post...this is why I originally did not post
the code because people like you want to just code bash instead of helping!
"--CELKO--" wrote:

> Why did you destroy Standard SQL behavior? Why are there more NULLs in
> one table than should be in an entire Fortune 500 accounting package?
> Do you really have a lot of data elements that are in Chinese and 255
> characters long? Why do you have data type prefixes on variable names,
> which is a violation of both good programming and ISO-11179? Why do you
> have numeric data elements in strings? What kind of total garbage are
> trying to get with things like "weight VARCHAR(50)", "@.phonetype
> VARCHAR(50)", etc. And you don't seem to be aware of floating
> point rounding errors (does your machine have a floating point
> processor, or do you want to slow things down with a software floating
> point package?)
> You keep height in inches or cm then convert it for display. You do
> not do this in the database. The syntax for CAST is CAST (<exp> AS
> <datatype> ) -- do not use the proprietary CONVERT().
> You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
> But even worse, you did absolutely no design or research on the data.
> Other products have a MERGE or UPSERT statement to do this. The usual
> pattern in older products is:
> BEGIN
> -- insert the new rows
> INSERT INTO Foobar
> SELECT *
> FROM WorkingData AS W
> WHERE W.keycol
> NOT IN (SELECT keycol FROM Foobar);
> -- update the rows are already there
> UPDATE Foobar
> SET <column>
> = (SELECT col FROM WorkingData AS W
> WHERE W.keycol = Foobar.keycol)
> WHERE keycol IN (SELECT keycol FROM WorkingData);
> END;
>

Tuesday, March 27, 2012

Cursor or Temp table

I just want to know which one is more efficient cursor or temp table for looping through a record set?
I am basically looking for better performance and server utilization.I generally prefer set based solutions over cursors, so I'm more than 95% sure that I'd recommend the temp table solution, but I can't be sure about that without knowing more about the problem.

-PatP|||95%?

That low?|||For looping through a recordset i.e., record by record, you will need a cursor. Ofcourse, you can bring the data from many tables into a #temp table, and use a cursor on it, that is to say, the SELECT statement of the cursor could be on the #temp table.

It may affect the performance of the stored procedure, but it is negligible. It depends on what you want.

Roshmi Choudhury|||It partly depends upon what you plan on doing with the records. If you do a simple SELECT, then most-likely the DBMS is already creating a temporary-table to store the results. If you are requesting a live query-view for updating purposes, the SELECT is effectively a filter on the main table.

Your choice probably won't make any difference to the DBMS server; the impact, if any, will be upon the simultaneous users of the table. ("Large things" and "long-time things" are generally "bad things.")

My rule-of-thumb recommend is K.I.S.S. ... keep it simple. Choose the approach that makes the most sense to your application and let the DBMS take care of its own business. If in the future you can prove that it is a problem, then you can change it.|||I disagree, for looping thru a record set you most definitely do not need a cursor, using an IF WHILE loop is just as effective, and carries none of the problems associated with cursors.. Many shops - mine included - do not allow cursors on their servers...Seems like the only people who want to use a cursor are those people who can't write one that won't get hung and take a server down with it... Just my .02

Nick|||You don't ALLOW cursors? That's (unnecessarily) extreme.

If you truly need to "loop" through your dataset, then a cursor is an appropriate solution. The larger issue is WHETHER you need to loop through the dataset. I'm sure Pat would agree that "95%" of the time when somebody uses a cursor it's because they are not familiar enough with set-based solutions, and in these cases cursors are virtually always less efficient.

Post the problem you are trying to solve, and I'd bet that somebody here can show you how to solve it using set-base operations.

Thursday, March 22, 2012

Cursor and order by

I noticed that I cannot use ORDER BY in cursor, but in my case I need to order data in cursor.
I'm thinking about selecting data for cursor from temp table where data is ordered but may be there are other decisions to get data in cursor ordered?

declare CR cursor ...

for

select ..

from ...

order by ...

|||This query works:
select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date]

Works

Now I try

declare @.MembersValues cursor
set @.MembersValues = cursor for (select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date])

And I get message "Incorrect syntax near the keyword 'order'."
I use SQL server 2005 Express

|||

It doesn't like the parens around the select. It gives the error message in the wrong spot. It is not obvious what the parser is thinking.

By the way, Cursors are rarely required. Have you considered using a set query instead?

|||I didn't get an error when running without parents. But it's still paradox why it's working properly while running without ORDER BY with parents.

Thanks for the help|||

In the second case, you need to quote the cursor declare statement as a string.

declare @.MembersValues cursor
set @.MembersValues = N'cursor for (select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date])'

|||

No. This is not valid syntax. See the SET statement topic in BOL for more details.

http://msdn2.microsoft.com/en-us/library/ms189484.aspx

Sunday, February 19, 2012

CTE Vs Temp Table in Yukon

Hi All,

I would like to know which gives better performance: CTE or Temporary Table?

Thanks,

Suresh

I wanted to add another information here.

When I replaced Temporary Tables with CTE in my query, it took more execution time. For ex., the query using Temp Table took 1 min 5 secs. The query using CTE took 4 mins. The no. of records hold by temp table in my query is apprx. 44000.

Why CTE is slower?

Suresh

|||

Check the execution plan, that is the only way you will find out. Clearly the CTE is using a different plan. You can force the execution plan to use a certain order using the FORCE command or option force_order at the end of the query.

Clarity Consulting (www.claritycon.com)

|||You cannot compare CTE and temporary table. They are different beasts. There are cases where you can break a complex query into simpler parts using temporary tables and get better performance. I am not sure how you used CTE in your query so it is hard to say. Note that CTEs also provide the capability to perform recursive queries in a declarative manner. And what are you measuring regarding the performance? Is it the temporary table creation vs query using CTE returning rows? If you are just measuring creation part then it doesn't include the time taken to send results to whatever client you are using. You need to elaborate on the actual problem. Best is to post a sample script that reproes the performance problem.

Friday, February 17, 2012

CSV output sql server 2000

Hi,
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
Thanks for your time and help
:)You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
Katie wrote:
> Hi,
> I have a bunch of stored procedures (some using temp tables) which give
> out tables which are then used in crystal to give out reports. These
> stored procs are run dynamically depending on values users select on
> forms
> Some people instead of reports want Comma Seperated Files (CSVs). I am
> trying to find a good way of outputting to csv with using either sql
> server or ASP. I tried two ways but none of them were ideal
> 1st method
> first way i tried was creating a record set in asp and then using the
> following to output the data in a comma delimited row and writing to
> file.
> Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> The problem with the above was with data sets greater than 10,000
> records the processing time increases exponentially because of memory
> usage ( i found other people with the same problem)
> So i tried the following
> 2nd method
> I used bcp using the master.dbo.xp_cmdshell command to output the
> csv. The problem with that is that the bcp executes the stored proc
> three times and also doesnot give out column headers. So i had to run
> the stored proc once in ASP and get the headers from the record set and
> store it to a text file and then run the stored proc in bcp and send
> the output to a csv file. Then merge the two files into a third file.
> The problem with this method is that it takes around 4 times as long to
> run as just the stored proceedure and also i have to create 3 files
> instead of one.
> I would appreciate any suggestions you have to output the csv from a
> stored proc in sql server. Note users have to do this dynamically when
> they submit a form so cant use the query analyzer or enterprise manager
> tools.
> Thanks for your time and help
> :)|||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?
:)
Adi wrote:
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > I have a bunch of stored procedures (some using temp tables) which give
> > out tables which are then used in crystal to give out reports. These
> > stored procs are run dynamically depending on values users select on
> > forms
> >
> > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > trying to find a good way of outputting to csv with using either sql
> > server or ASP. I tried two ways but none of them were ideal
> >
> > 1st method
> > first way i tried was creating a record set in asp and then using the
> > following to output the data in a comma delimited row and writing to
> > file.
> > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > The problem with the above was with data sets greater than 10,000
> > records the processing time increases exponentially because of memory
> > usage ( i found other people with the same problem)
> >
> > So i tried the following
> > 2nd method
> > I used bcp using the master.dbo.xp_cmdshell command to output the
> > csv. The problem with that is that the bcp executes the stored proc
> > three times and also doesnot give out column headers. So i had to run
> > the stored proc once in ASP and get the headers from the record set and
> > store it to a text file and then run the stored proc in bcp and send
> > the output to a csv file. Then merge the two files into a third file.
> > The problem with this method is that it takes around 4 times as long to
> > run as just the stored proceedure and also i have to create 3 files
> > instead of one.
> >
> > I would appreciate any suggestions you have to output the csv from a
> > stored proc in sql server. Note users have to do this dynamically when
> > they submit a form so cant use the query analyzer or enterprise manager
> > tools.
> >
> > Thanks for your time and help
> > :)|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
> :)
> Adi wrote:
> > You can use the OSQL utility in order to create a file with or without
> > headers (you can control the behavior of the headers with the -h
> > switch). Another tool that you can use is DTS package and run it with
> > DTSRun utility. Also I would not run the tools in a stored procedure
> > with xp_cmdshell, because this is a huge security problem. By working
> > this way you give the user a way to run there own scripts and command
> > on the operating system level with the privileges of the account that
> > runs SQL Server's service. Since you are working with ASP anyway, it
> > is better to run the OSQL (or if you choose a different utility, then
> > the other utility) from the ASP code and not from a stored procedure.
> >
> > Adi
> >
> >
> > Katie wrote:
> > > Hi,
> > >
> > > I have a bunch of stored procedures (some using temp tables) which give
> > > out tables which are then used in crystal to give out reports. These
> > > stored procs are run dynamically depending on values users select on
> > > forms
> > >
> > > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > > trying to find a good way of outputting to csv with using either sql
> > > server or ASP. I tried two ways but none of them were ideal
> > >
> > > 1st method
> > > first way i tried was creating a record set in asp and then using the
> > > following to output the data in a comma delimited row and writing to
> > > file.
> > > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > > The problem with the above was with data sets greater than 10,000
> > > records the processing time increases exponentially because of memory
> > > usage ( i found other people with the same problem)
> > >
> > > So i tried the following
> > > 2nd method
> > > I used bcp using the master.dbo.xp_cmdshell command to output the
> > > csv. The problem with that is that the bcp executes the stored proc
> > > three times and also doesnot give out column headers. So i had to run
> > > the stored proc once in ASP and get the headers from the record set and
> > > store it to a text file and then run the stored proc in bcp and send
> > > the output to a csv file. Then merge the two files into a third file.
> > > The problem with this method is that it takes around 4 times as long to
> > > run as just the stored proceedure and also i have to create 3 files
> > > instead of one.
> > >
> > > I would appreciate any suggestions you have to output the csv from a
> > > stored proc in sql server. Note users have to do this dynamically when
> > > they submit a form so cant use the query analyzer or enterprise manager
> > > tools.
> > >
> > > Thanks for your time and help
> > > :)|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly
:(
Adi wrote:
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > Thanks for your response.
> > But isnt OSQL also a command line utitlity? how would i run that from
> > inside asp. Would i use some shell command utility in vbscript?
> >
> > :)
> >
> > Adi wrote:
> > > You can use the OSQL utility in order to create a file with or without
> > > headers (you can control the behavior of the headers with the -h
> > > switch). Another tool that you can use is DTS package and run it with
> > > DTSRun utility. Also I would not run the tools in a stored procedure
> > > with xp_cmdshell, because this is a huge security problem. By working
> > > this way you give the user a way to run there own scripts and command
> > > on the operating system level with the privileges of the account that
> > > runs SQL Server's service. Since you are working with ASP anyway, it
> > > is better to run the OSQL (or if you choose a different utility, then
> > > the other utility) from the ASP code and not from a stored procedure.
> > >
> > > Adi
> > >
> > >
> > > Katie wrote:
> > > > Hi,
> > > >
> > > > I have a bunch of stored procedures (some using temp tables) which give
> > > > out tables which are then used in crystal to give out reports. These
> > > > stored procs are run dynamically depending on values users select on
> > > > forms
> > > >
> > > > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > > > trying to find a good way of outputting to csv with using either sql
> > > > server or ASP. I tried two ways but none of them were ideal
> > > >
> > > > 1st method
> > > > first way i tried was creating a record set in asp and then using the
> > > > following to output the data in a comma delimited row and writing to
> > > > file.
> > > > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > > > The problem with the above was with data sets greater than 10,000
> > > > records the processing time increases exponentially because of memory
> > > > usage ( i found other people with the same problem)
> > > >
> > > > So i tried the following
> > > > 2nd method
> > > > I used bcp using the master.dbo.xp_cmdshell command to output the
> > > > csv. The problem with that is that the bcp executes the stored proc
> > > > three times and also doesnot give out column headers. So i had to run
> > > > the stored proc once in ASP and get the headers from the record set and
> > > > store it to a text file and then run the stored proc in bcp and send
> > > > the output to a csv file. Then merge the two files into a third file.
> > > > The problem with this method is that it takes around 4 times as long to
> > > > run as just the stored proceedure and also i have to create 3 files
> > > > instead of one.
> > > >
> > > > I would appreciate any suggestions you have to output the csv from a
> > > > stored proc in sql server. Note users have to do this dynamically when
> > > > they submit a form so cant use the query analyzer or enterprise manager
> > > > tools.
> > > >
> > > > Thanks for your time and help
> > > > :)

CSV output sql server 2000

Hi,
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
Thanks for your time and help
You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
Katie wrote:
> Hi,
> I have a bunch of stored procedures (some using temp tables) which give
> out tables which are then used in crystal to give out reports. These
> stored procs are run dynamically depending on values users select on
> forms
> Some people instead of reports want Comma Seperated Files (CSVs). I am
> trying to find a good way of outputting to csv with using either sql
> server or ASP. I tried two ways but none of them were ideal
> 1st method
> first way i tried was creating a record set in asp and then using the
> following to output the data in a comma delimited row and writing to
> file.
> Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> The problem with the above was with data sets greater than 10,000
> records the processing time increases exponentially because of memory
> usage ( i found other people with the same problem)
> So i tried the following
> 2nd method
> I used bcp using the master.dbo.xp_cmdshell command to output the
> csv. The problem with that is that the bcp executes the stored proc
> three times and also doesnot give out column headers. So i had to run
> the stored proc once in ASP and get the headers from the record set and
> store it to a text file and then run the stored proc in bcp and send
> the output to a csv file. Then merge the two files into a third file.
> The problem with this method is that it takes around 4 times as long to
> run as just the stored proceedure and also i have to create 3 files
> instead of one.
> I would appreciate any suggestions you have to output the csv from a
> stored proc in sql server. Note users have to do this dynamically when
> they submit a form so cant use the query analyzer or enterprise manager
> tools.
> Thanks for your time and help
> |||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?

Adi wrote:[vbcol=seagreen]
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> Katie wrote:|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
>
> Adi wrote:|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly

Adi wrote:[vbcol=seagreen]
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:

Tuesday, February 14, 2012

CSV

I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?
This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?
|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:

> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> The
> download
> in a
>
>
|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?
|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
..NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archi.../14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]
|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:

> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?

CSV

I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > I am attempting to exports the results of a stored Procedure into a CSV.
> The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> > the file from a .NET Web Application. Is there someone that can point me
> in a
> > direction?
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
écrit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archive/2005/07/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
> > This sounds like a one-time, or not frequent need. If the results are
> > <65000 records, it is probaby easiest to highlight the results in query
> > analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to
copy
> > to the clipboard, and then paste those values into Excel. Then, in
Excel,
> > choose to "save as" type = comma delimited / CSV.
> >
> > I do this often and it's easier than your alternatives. If you need an
> > automated way that works by itself every 24 hours, then that's a
different
> > story.
> >
> > "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > > I am attempting to exports the results of a stored Procedure into a
CSV.
> > The
> > > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > > the file from a .NET Web Application. Is there someone that can point
me
> > in a
> > > direction?
> >
> >
> >|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to export
using DTS.
Thanks!
"Christian Hamel" wrote:
> Why not simply creating a DTS (or SSIS if you're on 2005) package that will
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> >I am attempting to exports the results of a stored Procedure into a CSV.
> >The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > the file from a .NET Web Application. Is there someone that can point me
> > in a
> > direction?
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?

CSV

I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to downloa
d
the file from a .NET Web Application. Is there someone that can point me in
a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:

> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to cop
y
> to the clipboard, and then paste those values into Excel. Then, in Exce
l,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com..
.
> The
> download
> in a
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/arch...7/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
>
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to expor
t
using DTS.
Thanks!
"Christian Hamel" wrote:

> Why not simply creating a DTS (or SSIS if you're on 2005) package that wil
l
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:

> I am attempting to exports the results of a stored Procedure into a CSV. T
he
> table is a temp tabel named #FinalPrivate. I need to allow a user to downl
oad
> the file from a .NET Web Application. Is there someone that can point me i
n a
> direction?