Issues with Export to Excel file

I need to do a simple task. I have a oledb source which pulls data from sql server, creates an excel file abc.xls and dumps it with the Excel destination at remote location.

I need to create this file at runtime. However, when i give the location, the Excel Destination expects the file to be there for the Mapping of the source columns with destination ones.

My tasks are

1. Creating a table for the worksheet in Excel connection

2. Select from source table and write in Excel Destination

Will appreciate your response.

You can create the xls file at run time without problem; but the file need to exists at design time as SSIS needs to get the metedata from it.

I have a couple of posts that show some examples using excel files; I hope you find them helpful

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
Pushkar
I don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?
|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?
|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:
> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues copying data to linked server

I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

EXEC procStmtDataToReno 'B'

and some snippets of the SP:

CREATE PROCEDURE [dbo].[procStmtDataToReno]

@.CoCode as varchar(2)

AS

--various declarations and such

DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @.CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )

--code to build temp table

INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO

TIAI normally would create a stored procedure on the remote server side and call it via openquery() or sp_executesql. That is the *fastest* way to do and DML.

Anyway, I suggest you take a look at the following article for guidance on resolving 7391 error.

http://support.microsoft.com/kb/306212|||Thanks for that. Because some companies worked, my suspicion all along was that it was "data" related rather than "process" related. I finally stumbled into the answer late last night. I had tried changing the SP to simply select the records, and it ran fine from Query Analyzer. I changed the SP back to an INSERT and executed it again from QA, and it failed, but I finally got a message that made sense:

Server: Msg 8152, Level 16, State 4, Procedure procStmtDataToReno, Line 389
String or binary data would be truncated.
The statement has been terminated.

My problem was as simple as tracking down the field that was wider on the source side than it was on the destination side. The process now works fine. I just didnt know what tool to use to get better info on what was going wrong.

I am interested in your recommendation to set it up on the remote side. I would have thought it was better to have the SP on the source side, so it would only have to transfer the results of the process over the wire. If the SP were on the destination side, wouldnt it have to bring all the data across to work with?|||yes, you have to pass the data to the remote server in order for it to be processed.

e.g.

@.sql='exec my_sp ' + @.inputvar

exec linked.db..sp_executesql @.sql

Issue with passing a parameter to Stored Procedure using IN keywor

Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:

> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>

isql.exe vs isqlw.exe vs osql.exe

I've tried running a script (creates a bunch of SPs) using all three
command-line utilities, but have observed very different performance on
each.
isql.exe isqlw.exe osql.exe
11:14 134 01:20 773 05:01 650
As you can see, the time to execute varies quite a bit.
Can anyone offer an explanation for this?
When should you use one or the other?
Any guidance would be much appreciated. Thanks.
FYI - Here are the command line switches used:
isql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase -iMyInputFile.sql -oMyOutputFile.log
-a4096 -c -n -w8000
isqlw.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase -iMyInputFile.sql
-oMyOutputFile.log
osql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase -iMyInputFile.sql -oMyOutputFile.log
-a4096 -c -n -w8000
Henri Fournier
Henri Fournier wrote:
> I've tried running a script (creates a bunch of SPs) using all three
> command-line utilities, but have observed very different performance
> on each.
> isql.exe isqlw.exe osql.exe
> 11:14 134 01:20 773 05:01 650
> As you can see, the time to execute varies quite a bit.
> Can anyone offer an explanation for this?
> When should you use one or the other?
> Any guidance would be much appreciated. Thanks.
> FYI - Here are the command line switches used:
> isql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase
> -iMyInputFile.sql -oMyOutputFile.log -a4096 -c -n -w8000
> isqlw.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase
> -iMyInputFile.sql -oMyOutputFile.log
> osql.exe -SMyServer -UMyUserId -PMyPassword -dMyDatabase
> -iMyInputFile.sql -oMyOutputFile.log -a4096 -c -n -w8000
I believe OSQL uses ODBC. ISQL uses DB-Library. ISQLW uses ODBC as well,
but is a Windows application (although it can run in a pseudo-console
mode).
Not sure what you are running or why you're seeing such poor performance
with OSQL. Can you elaborate? In any case, make sure SET NOCOUNT ON is
the first item in your script.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:egk11OTcFHA.1148@.tk2msftngp13.phx.gbl...
> I believe OSQL uses ODBC. ISQL uses DB-Library. ISQLW uses ODBC as well,
> but is a Windows application (although it can run in a pseudo-console
> mode).
> Not sure what you are running or why you're seeing such poor performance
> with OSQL. Can you elaborate? In any case, make sure SET NOCOUNT ON is the
> first item in your script.
>
We have a series of scripts to update the database, where one contains all
the schema changes, another contains triggers, etc. The one I'm using to
test does a drop/create for some 500+ Stored Procedures, nothing fancy.
And yes, SET NOCOUNT ON is the first line in the script.
I just can't see why OSQL is so much slower. I've got the packetsize set to
4096, which is what the "Network package size" is set to in QA (assuming
those are in fact the same things).
I've got the columnwidth set to 8000, cause I don't want any wrapping in the
output file. Don't think this should effect performance.
Can't see any switches set or not set that can account for the performance
difference. Right?
Henri