running a text file full of sql?

I'm sure there's a trivial answer to this, but I've looked in both the TSQL
dox and the SS2000 dox, and if it's in there it's well hidden...
Is there a way to run the SQL commands inside a text file from WITHIN TSQL?
I know I can use isql to do this, but is there a syntax for running it from
within a stored proc?
I'm setting up a job to run a Log scraper at night. It's output is a .SQL
file that I then want to read in (all at 3AM). I could use Shell Command and
isql, but it would be "nicer" to avoid this and use TSQL directly (IMHO).
EXEC master..xp_cmdshell 'osql ...'
Take a look at the command line arguments for osql in Books Online.
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:EF81D2F3-ACBC-45B6-BE56-53A9670C652F@.microsoft.com...
> I'm sure there's a trivial answer to this, but I've looked in both the
TSQL
> dox and the SS2000 dox, and if it's in there it's well hidden...
> Is there a way to run the SQL commands inside a text file from WITHIN
TSQL?
> I know I can use isql to do this, but is there a syntax for running it
from
> within a stored proc?
> I'm setting up a job to run a Log scraper at night. It's output is a .SQL
> file that I then want to read in (all at 3AM). I could use Shell Command
and
> isql, but it would be "nicer" to avoid this and use TSQL directly (IMHO).
|||EXEC master..xp_cmdshell 'osql -S Servername -E -ipath:\SQLScript.sql'
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:EF81D2F3-ACBC-45B6-BE56-53A9670C652F@.microsoft.com...
> I'm sure there's a trivial answer to this, but I've looked in both the
TSQL
> dox and the SS2000 dox, and if it's in there it's well hidden...
> Is there a way to run the SQL commands inside a text file from WITHIN
TSQL?
> I know I can use isql to do this, but is there a syntax for running it
from
> within a stored proc?
> I'm setting up a job to run a Log scraper at night. It's output is a .SQL
> file that I then want to read in (all at 3AM). I could use Shell Command
and
> isql, but it would be "nicer" to avoid this and use TSQL directly (IMHO).

running a script to create a database in MSDE

I've been able to create a CD where it installs MSDE 2000
sp3 on a user machine. I need to create a database on the
users machine, how do I incorporate the SQL script to
create the database? Do I call the script within the
setup.ini file? Any sample anywhere I can take a look at?
Thank you,
Randers
hi Randers,
Randers wrote:
> I've been able to create a CD where it installs MSDE 2000
> sp3 on a user machine. I need to create a database on the
> users machine, how do I incorporate the SQL script to
> create the database? Do I call the script within the
> setup.ini file? Any sample anywhere I can take a look at?
> Thank you,
> Randers
unfortunately is not that easy as you are trying to do... nothing from the
box...
you can perhaps run oSql.exe to execute your DDL sql script, or a home built
app of yours you pass the path to your script and execute it via an
ADO/Ado.Net connection, or have a look at
http://msdn.microsoft.com/msdnmag/is...baseinstaller/ for
a custom made database installation task...
another approach is relying on tools such as
http://www.red-gate.com/sql/sql_packager.htm
or you could redistribute a full backup of your database made on your
distribution environment and, via osql.exe and/or a home built app of yours
relying on ADO/Ado.Net connection restore it on the destination instance...
the very same path could be performed using the .Mdf and .Ldf detached
database files and reattache them using sp_attach_db system stored
procedures...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

RUNNIG A JOB?

I HAVE A PROBLEM,, I``ve just deployed a package into my server..it runs without problems from my SSIS.. but when i set a job with the sql agent and the package to it,. the job just fails .. why? any solution?

please somebody help me!!!

yes, this question comes up a lot, I apologize for the confusion. We are working on a KB article to help explain issues around this. It should be ready within a few days. The short asnwer is that when you execute under agent, you are (miost likely) executing "as" a different user than when you execute in the dev studio or command line with dtexec.exe.

So the reason a different login may fail could be due to the ProtectionLevel property the package is set to AND/OR the user you execute with under agent does not have some permission needed, like writing to a certain file share where the package wants to dump a file.

You should read up on the SQL Server Agent proxy acccounts and the SSIS package protection level. Usual cause is the default protection level is encrypt sensitive with a user key, and if its a different user OR different machine you will not be able to load all of the properties in the package such as connection info. You could therefore create an agent proxy account to 'mimic' the same as the pacake author , or change to a password protection level and suppy the password in the Agent job, or change protection level to server storage which puts the package into SQL and you can control access via SQL DB roles rather than a specific user encryption key.

You may also search the forum for more on this as lots of helpful folks in this forum community have responded to similar questions and collectively there is a lot of info here.

Hope this helps

|||

i`ve tried all this options.. set the protection level to storage server when copying to the target server.. created an agent proxy account =this account has identity of an administrator in my server= after that i set the run as property of the job to this account and still won`t work

thanks for your help

|||This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.|||Ruk, as Micahel noted the KB article I referred to is now published. Please review the article. Based on experience, the article should cover all the possible cases that would result in the issue you seem to have, but if not then we will need a more information such as the messages returned to the SQL Agent Job history and error messages returned via SSIS Logging.|||

i've tried to catch the error by running each task of my dts separately.. then i found the one that is causin troubles.. it looks like by using data flow tasks something unknown happens.. i'm using an OLE DB source to retrieve some data from the db... netx step i use this data conversion task because some of the results of the query aren`t supported by mi destination which is an OLE DB destination that maps to an excel file in the target server.. this is weird cause i run this taks in my bids and runs ok.. i run this again from SSIS in the target server and runs ok.. but when i scheduled it in a job it just fails.... any solution?

thanks for your help!!!!

|||Have you followed the part of the KB article where it describes how to configure logging using CmdExec Agent subsystem, or SSIS logging? What are the errors reported?|||

these are some errors that the log provided

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
--

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXX\Administrator
Source Name: DESIE_CatalogoDeSucursales(Semanal)
Source ID: {D1025489-4A06-4347-9674-0A73A1847829}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Name: OnError
Message: component "OLE DB Destination" (40) failed validation and returned error code 0xC020801C.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1073450985
For more information, see Help and Support Center at

"Sucursales" is the name of the connection manager that maps the xls file destination. the job just fails when trying to validate it

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

|||

ruk_walled wrote:

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

Where is XLS file located: local disk, UNC network drive (\\machine\drive\file), mapped network drive?

When you've tried changing destination to flat file - was the CSV file in the same location as XLS? If not, try placing the CVS to the same folder (to check whether it is related to destination-type or the problem is with access to this location).

|||

the xls file is located in a local disk.... and the flat file is created in the same path with no problems,.. the problem is not at the moment of creating the file.. but at the moment of validate the connection manager that refers to this local path...when using xls

|||

I can't fit some parts together:

my destination which is an OLE DB destination that maps to an excel file in the target server|||

i'm sorry my english is not good enough

here is the thing.. i'm doing a report from some data and try to write it in an xls file.

i have a data flow task that connects to a db in the target server to retrieve the data. this uses an OLE DB connection manager which has the server name.. the db. and the user to connect. --this part parses ok.

since there are some data that doesn′t match with the predefined ones.. i use a data conversion transformation task

then..

i'm using an ole DB destination which uses an OLE DB connection manager that maps to the file that is located in a local disk on the target server.. the file exists and it contains only the headers .

the package is stored in the server.. so is the file destination. i also changed the OLE DB destination for an Excel Destination but i happened the same..

i copied this dts from mi computer to the target server using the option save a copy of. from the bids and setting the protection level to server storage.

when i schedule the job it seems to run but as the log shows.. something happens when trying to validate the connection manager that maps the path of the file.. but the path and the file exists...

|||

i have finally found the problem... all my dts were programmed under 32 bits environment ..<my target server is running under 64 bits so this was the problem.. that i'm using jet 4.0 which is not supported for 64bits so this is the answer:

thanks to michael Entin..

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"

|||

Craig,

Where is the KB Article? Is it at http://support.microsoft.com/kb/918760 b/c this was not helpful for the problem that I face.

|||

Hi,This is dinesh here.I need to create a job and schedule it in sql server 2005.But I dont find the sql server agent option under the database instance in the object explorer. I need to write a simple job and just schedule it. Do i need to instal some extra components of sql server 2005?

I would appreciate if you could help me.

RUNNIG A JOB?

I HAVE A PROBLEM,, I``ve just deployed a package into my server..it runs without problems from my SSIS.. but when i set a job with the sql agent and the package to it,. the job just fails .. why? any solution?

please somebody help me!!!

yes, this question comes up a lot, I apologize for the confusion. We are working on a KB article to help explain issues around this. It should be ready within a few days. The short asnwer is that when you execute under agent, you are (miost likely) executing "as" a different user than when you execute in the dev studio or command line with dtexec.exe.

So the reason a different login may fail could be due to the ProtectionLevel property the package is set to AND/OR the user you execute with under agent does not have some permission needed, like writing to a certain file share where the package wants to dump a file.

You should read up on the SQL Server Agent proxy acccounts and the SSIS package protection level. Usual cause is the default protection level is encrypt sensitive with a user key, and if its a different user OR different machine you will not be able to load all of the properties in the package such as connection info. You could therefore create an agent proxy account to 'mimic' the same as the pacake author , or change to a password protection level and suppy the password in the Agent job, or change protection level to server storage which puts the package into SQL and you can control access via SQL DB roles rather than a specific user encryption key.

You may also search the forum for more on this as lots of helpful folks in this forum community have responded to similar questions and collectively there is a lot of info here.

Hope this helps

|||

i`ve tried all this options.. set the protection level to storage server when copying to the target server.. created an agent proxy account =this account has identity of an administrator in my server= after that i set the run as property of the job to this account and still won`t work

thanks for your help

|||This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.|||Ruk, as Micahel noted the KB article I referred to is now published. Please review the article. Based on experience, the article should cover all the possible cases that would result in the issue you seem to have, but if not then we will need a more information such as the messages returned to the SQL Agent Job history and error messages returned via SSIS Logging.|||

i've tried to catch the error by running each task of my dts separately.. then i found the one that is causin troubles.. it looks like by using data flow tasks something unknown happens.. i'm using an OLE DB source to retrieve some data from the db... netx step i use this data conversion task because some of the results of the query aren`t supported by mi destination which is an OLE DB destination that maps to an excel file in the target server.. this is weird cause i run this taks in my bids and runs ok.. i run this again from SSIS in the target server and runs ok.. but when i scheduled it in a job it just fails.... any solution?

thanks for your help!!!!

|||Have you followed the part of the KB article where it describes how to configure logging using CmdExec Agent subsystem, or SSIS logging? What are the errors reported?|||

these are some errors that the log provided

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
--

Event Name: OnError
Message: The AcquireConnection method call to the connection manager "Sucursales" failed with error code 0xC0202009.
Operator: XXXXX\Administrator
Source Name: DESIE_CatalogoDeSucursales(Semanal)
Source ID: {D1025489-4A06-4347-9674-0A73A1847829}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1071611876
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Name: OnError
Message: component "OLE DB Destination" (40) failed validation and returned error code 0xC020801C.
Operator: XXXXXXX\Administrator
Source Name: DTSTask_DTSDataPumpTask_1
Source ID: {5F097B16-5066-4461-AEEA-B8E96B3AFCF5}
Execution ID: {7A4CD1D3-3C2D-4644-92DF-0742CF042B3C}
Start Time: 5/8/2006 12:50:59 PM
End Time: 5/8/2006 12:50:59 PM
Data Code: -1073450985
For more information, see Help and Support Center at

"Sucursales" is the name of the connection manager that maps the xls file destination. the job just fails when trying to validate it

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

|||

ruk_walled wrote:

i,ve tried changing the destination to a flat file (.csv) and the job worked fine.. but why it doesn′t run when i use and xls file destination? all these errors about connection doesn′t appear when i execute the package from SSIS but when i use the job this just won′t work.. it is not a problem of permissions cause i'm using a local administrator account when i log on into the server so.... do i need to have Microsoft Excel installed in the server or some kind of service pack or system file updates????

please!!!!

Where is XLS file located: local disk, UNC network drive (\\machine\drive\file), mapped network drive?

When you've tried changing destination to flat file - was the CSV file in the same location as XLS? If not, try placing the CVS to the same folder (to check whether it is related to destination-type or the problem is with access to this location).

|||

the xls file is located in a local disk.... and the flat file is created in the same path with no problems,.. the problem is not at the moment of creating the file.. but at the moment of validate the connection manager that refers to this local path...when using xls

|||

I can't fit some parts together:

my destination which is an OLE DB destination that maps to an excel file in the target server|||

i'm sorry my english is not good enough

here is the thing.. i'm doing a report from some data and try to write it in an xls file.

i have a data flow task that connects to a db in the target server to retrieve the data. this uses an OLE DB connection manager which has the server name.. the db. and the user to connect. --this part parses ok.

since there are some data that doesn′t match with the predefined ones.. i use a data conversion transformation task

then..

i'm using an ole DB destination which uses an OLE DB connection manager that maps to the file that is located in a local disk on the target server.. the file exists and it contains only the headers .

the package is stored in the server.. so is the file destination. i also changed the OLE DB destination for an Excel Destination but i happened the same..

i copied this dts from mi computer to the target server using the option save a copy of. from the bids and setting the protection level to server storage.

when i schedule the job it seems to run but as the log shows.. something happens when trying to validate the connection manager that maps the path of the file.. but the path and the file exists...

|||

i have finally found the problem... all my dts were programmed under 32 bits environment ..<my target server is running under 64 bits so this was the problem.. that i'm using jet 4.0 which is not supported for 64bits so this is the answer:

thanks to michael Entin..

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"

|||

Craig,

Where is the KB Article? Is it at http://support.microsoft.com/kb/918760 b/c this was not helpful for the problem that I face.

|||

Hi,This is dinesh here.I need to create a job and schedule it in sql server 2005.But I dont find the sql server agent option under the database instance in the object explorer. I need to write a simple job and just schedule it. Do i need to instal some extra components of sql server 2005?

I would appreciate if you could help me.

sql

RunCommands problem in Setup.lst

Im trying to create a setup cd using office setup wizard. I want the setup to
install my MSDE SP3 at the end of the installation. So I've placed the
following command in the setup.lst under [RunCommands]
MSDERelA\setup.exe InstanceName="TaShel" SAPWD "sa" SecurityMode="SQL
MSDERelA is a folder on my setup cd containing the MSDE files.
I can use the same command to run it from the command prompt but the command
is not running during the setup process.
If anyone could shed some light on this problem of mine.
Thanks
Dorji
hi Dorji,
Dorji wrote:
> Im trying to create a setup cd using office setup wizard. I want the
> setup to install my MSDE SP3 at the end of the installation. So I've
> placed the following command in the setup.lst under [RunCommands]
I'm not prepared about office setup wizard... try asking in the Office ng
hyerarchy...
BTW...
MSDERelA\setup.exe InstanceName="TaShel" SAPWD "sa" SecurityMode="SQL
=
MSDERelA\setup.exe InstanceName="TaShel" SAPWD="sa" SecurityMode=SQL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply