Sunday, March 25, 2012
calling sql file from sp
execute it...
Hi,
Yes, it is possible using XP_CMDSHELL
create proc <name>
as
begin
xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
c:\file_name.sql -oc:\output.log'
end
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...
|||By default XP_cmdshell can only be called by SQL Administrators.. If you
wish others to be able to use this, an option can be turned on in SQM,
Choose SQL Agent->Properties... It will be on one of the tabs to allow
non-sysadms to do cmd and active scripting tasks.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...
|||it's also possible to use ethe xp_OA_xxx OLE automation proceedures to write
code to do this. xp_cmdshell as described will most likely be more effecient
and easier for you to use, but... I wanted to mention the option...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...
|||Thanks
>--Original Message--
>Hi,
>Yes, it is possible using XP_CMDSHELL
>create proc <name>
>as
>begin
>xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d
db_name -i[vbcol=seagreen]
>c:\file_name.sql -oc:\output.log'
>end
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
and
>
>.
>
|||Due to security reasons I would not recommend to grant xp_cmdshell rights on
everyone. This could be a potential threat for server hosting MSSQL. Better
use extended stored proc which will do the same, and evil users won't be
able to do anything but running the script you prepare
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ubG%23HwyPEHA.3348@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> Yes, it is possible using XP_CMDSHELL
> create proc <name>
> as
> begin
> xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
> c:\file_name.sql -oc:\output.log'
> end
> Thanks
> Hari
> MCDBA
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
calling sql file from sp
execute it...Hi,
Yes, it is possible using XP_CMDSHELL
create proc <name>
as
begin
xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
c:\file_name.sql -oc:\output.log'
end
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...|||By default XP_cmdshell can only be called by SQL Administrators.. If you
wish others to be able to use this, an option can be turned on in SQM,
Choose SQL Agent->Properties... It will be on one of the tabs to allow
non-sysadms to do cmd and active scripting tasks.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...|||it's also possible to use ethe xp_OA_xxx OLE automation proceedures to write
code to do this. xp_cmdshell as described will most likely be more effecient
and easier for you to use, but... I wanted to mention the option...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...|||Thanks
>--Original Message--
>Hi,
>Yes, it is possible using XP_CMDSHELL
>create proc <name>
>as
>begin
>xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d
db_name -i
>c:\file_name.sql -oc:\output.log'
>end
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
>> Is there a way to call sql file from stored procedure
and
>> execute it...
>
>.
>|||Due to security reasons I would not recommend to grant xp_cmdshell rights on
everyone. This could be a potential threat for server hosting MSSQL. Better
use extended stored proc which will do the same, and evil users won't be
able to do anything but running the script you prepare
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ubG%23HwyPEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Yes, it is possible using XP_CMDSHELL
> create proc <name>
> as
> begin
> xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
> c:\file_name.sql -oc:\output.log'
> end
> Thanks
> Hari
> MCDBA
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> > Is there a way to call sql file from stored procedure and
> > execute it...
calling sql file from sp
execute it...Hi,
Yes, it is possible using XP_CMDSHELL
create proc <name>
as
begin
xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
c:\file_name.sql -oc:\output.log'
end
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx
.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...|||By default XP_cmdshell can only be called by SQL Administrators.. If you
wish others to be able to use this, an option can be turned on in SQM,
Choose SQL Agent->Properties... It will be on one of the tabs to allow
non-sysadms to do cmd and active scripting tasks.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx
.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...|||it's also possible to use ethe xp_OA_xxx OLE automation proceedures to write
code to do this. xp_cmdshell as described will most likely be more effecient
and easier for you to use, but... I wanted to mention the option...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx
.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...|||Thanks
>--Original Message--
>Hi,
>Yes, it is possible using XP_CMDSHELL
>create proc <name>
>as
>begin
>xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d
db_name -i
>c:\file_name.sql -oc:\output.log'
>end
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
> news:1028201c43f29$daf22d90$a101280a@.phx
.gbl...
and[vbcol=seagreen]
>
>.
>|||Due to security reasons I would not recommend to grant xp_cmdshell rights on
everyone. This could be a potential threat for server hosting MSSQL. Better
use extended stored proc which will do the same, and evil users won't be
able to do anything but running the script you prepare
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ubG%23HwyPEHA.3348@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> Yes, it is possible using XP_CMDSHELL
> create proc <name>
> as
> begin
> xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
> c:\file_name.sql -oc:\output.log'
> end
> Thanks
> Hari
> MCDBA
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1028201c43f29$daf22d90$a101280a@.phx
.gbl...sql
Thursday, March 22, 2012
Calling remote batch file using 'Execute process task'
I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A. The batch file uses pgp software and encrypts a file sitting on machine B itself. The reason why my batch file is sitting on machine B, is because the PGP software is on machine B.
If I execute the batch file by itself from machine B, the script runs fine. I refer the same batch file as a UNC path from my package on machine A. But that does not work since the 'Working directory' is still machine A. I can not set machine B's folder as the working dir because it does not accept UNC path. So I say, ok , let me map a path to that UNC location and map it as drive 'Z:\'. Certainly if I do so, I will be running the process on machine A and the batch file will look for the pgp software on machine A, and hence fail.
I have tried third party remote batch execution tools (PSEXEC) but have not had success, not because of SSIS limitations, but simply because the PGP executable when run through the PSEXEC tool, does not identify the location of the public keys on machine B and hence gives an encrytion failure.
How do I get the remote batch file to execute such that it executes with its own env? Is there a better remote execution tool I can try or are there any other features of SSIS I can use to get around this issue? I need the results of the batch file and hence do not want to make it an asyncronous process.
Thanks
Sumeet
PsExec will work for this purpose, and so will rsh and ssh implementation for Win32. Psexec is easier and you can use the "-e" parameter to configure alternate profile loading. Even if the environment from psexec were the minimal default user environment, just execute a remote command/script/executable which sets up the environment necessary prior to the pgp call.|||Thanks. That helps. PsExec sends the alternate profile username/password as clear text. Can this be avoided in any way? I can not send account information to the PGP server as clear text. I am thinking about web service, but that has its own set of issues in executing a batch process, since the identiy of the executing process can not be changed (in 1.1)...
If someone can direct me to the user group where these questions can be better addressed, please let me know!
Thanks,
Sumeet
calling my connection from pages
I would like to have my connection strign in my web.config file being decralred publicly in my pages so that I dont have to repeat lines of code throughout pages.
In my web.config I have this.
<connectionStrings>
<add name="myconn" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
then in my pages I have
Imports System.Data
Imports System.Data.SqlClient
Public sqlconnection As New SqlConnection(ConfigurationSettings.AppSettings("myconn"))
Public sqlcommand As New SqlCommand
Public sqldataAdapter As New SqlDataAdapter
Public sSQL as String
Function getdata()
sSQL = "INSERT INTO tbl_Idea (Status) value (@.Status)
Dim dbcommand As New System.Data.SqlClient.SqlCommand(sSQL, sqlconnection)
Dim statusparam As New SqlParameter("@.Status", SqlDbType.NVarChar)
statusparam.Value = "PENDING"
dbcommand.Parameters.Add(statusparam)
sqlconnection.Open()
dbcommand.ExecuteNonQuery()
sqlconnection.Close()
for some reason this code does not seem to work. It keeps telling me The ConnectionString Property has not been initialized.
I do the same thing in my asp.net 1.1 pages using vs 2003. Can some one help or atleast point in the right direction.
thanks
Hi,
Here is the simple explanation:
Your connection string in the web.config is not in the AppSettings section but in the connectionStrings section.
so instead of reading it like in your code:
Public sqlconnection As New SqlConnection(ConfigurationSettings.AppSettings("myconn"))
you just read it from the connectionStrings section like so:
Public
Dim sqlconnectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings("myconn").ConnectionString)
This will do the job
Have fun
G.
|||dude ur the bomb. thanks a lot.
Calling external webservice using certificate
service.ClientCertificates.Add(X509Certificate.CreateFromCertFile(certFile));
Exception is:
Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Do you have any advices?
MiSu
In order to access the file system, your assembly must be deployed with at least an EXTERNAL_ACCESS permission grant. If you're using VStudio, you can set the target permission level via the project properties. Otherwise, see http://msdn2.microsoft.com/en-us/library/ms189524.aspx for details of the CREATE ASSEMBLY syntax.
Calling External Program from SQL Server 2000
into a SQL Server 2000 table. I was thinking that I would create a simple
windows console application in C# to handle the folder creation. I want to
run that external program from an Insert Trigger on the table. How do I do
that or is there a way to create the folder from within SQL server? Thanks
in advance for any help or guidance given.You could call xp_cmdshell or use SP_OACreate to instantiate a COM object fr
om your trigger. But
don't do that. The insert will be blocked until the trigger has finished, an
d errors will roll back
the insert. I.e., this is a good way to get bad performance. Have the trigge
r insert relevant data
into a table and then let an Agent job run every minute (or so) and read off
of this table and do
whatever needs to be done. If you were on SQL Server 2005, you would use Ser
vice Broker for this,
btw...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Greg Busby" <gbusby@.med-edge.com> wrote in message news:OWKkkPZWGHA.4972@.TK2MSFTNGP02.phx.
gbl...
>I want to create a file folder on a server when a new record is Inserted in
to a SQL Server 2000
>table. I was thinking that I would create a simple windows console applica
tion in C# to handle the
>folder creation. I want to run that external program from an Insert Trigge
r on the table. How do
>I do that or is there a way to create the folder from within SQL server? T
hanks in advance for any
>help or guidance given.
>
Tuesday, March 20, 2012
Calling another .sql file from an existing .sql file
the osql cmd.
I would like to know how to make fileA.sql call fileB.sql.
In an oracle based .sql file, I generally would call fileB.sql from within
fileA.sql using
the following script
@.@.fileB.sql
This does not seem to work against sql server.
Thanks.
--
ANeelimaHi
File calls another file? I'm not aware of such issue?
However , you can call the file to do some operations as
UPDATE/INSERT/DELETE/SELECT and then make some logic here.
D you mean that fileA does not contain any info about fileB?
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||Yes, the intention is for FileA.sql to call FileB.sql. Instead of calling
all the files individually from my VB application I wanted to call one singl
e
file called FileA.sql
and have FileA.sql call FileB.sql, FileC.sql, and so on.
I can do that when executing sql files against Oracle without any problems.
So, if it were Oracle, the script in FileA.sql will be
----
set verify off;
set echo off;
@.@.FileB.ql;
PAUSE;
@.@.FileC.sql;
EXIT;
----
If I use the same script against SQL Server I get the error saying that
@.@.FileB.sql is not recognized.
ANeelima
"Uri Dimant" wrote:
> Hi
> File calls another file? I'm not aware of such issue?
> However , you can call the file to do some operations as
> UPDATE/INSERT/DELETE/SELECT and then make some logic here.
> D you mean that fileA does not contain any info about fileB?
>
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>
>|||In file A, use OSQL (again) to call file B.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
...
To my knowledge there isn't a "built in" preprocessor as you describe.
Generally people use stored procedures instead of file level modules. I
have heard of people using a C language preprocessor with regard to
configuration management with few difficulties.
B|||In order to use osql to call file B from file A, I would have to hard-code
the database and server name in File A as in
osql -E -n -S %COMPUTERNAME%\MYINSTANCE -dENDESKTOP11 -i "FileB.sql" -o
"FileB.log"
But, my goal is to not hard-code the database nor the instance/server name
in FileA.sql since the user chooses the database and server name at run-time
from the VB application.
The VB application then uses the selected names and executes a osql command
and runs FileA.sql using the shell cmd programmatically. Thus I was hoping
that there was a way to simply set the database and server in FileA.sql
(which is the parent sql file) and have FileA call FileB, FileC, etc.
Based on the responses, it appears like I cannot do that as in Oracle.
--
ANeelima
"Arnie Rowland" wrote:
> In file A, use OSQL (again) to call file B.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>
>|||Hi, ANeelima,
Thanks for your post.
From your description, I understand that:
Your VB application used osql command to execute SQL script files. In your
case, you wanted one sql file can invoke another sql file avoiding hard
code of osql in it.
If I have misunderstood, please let me know.
I don't find other commands or functions can invoke other sql files without
login, which may be different from Oracle.
However, in my experiences, such expression @.@.fileb.sql is supported by
SQL*Plus, while it is not supported by OSQL.
You can only pass the sql file name as -i parameter of osql command.
If you want to just connect your database server only once, I recommend you
use ADO or ODBC to connect the server and the read and execute the sql
files line by line.
Also, if you don't care about the connection times of osql, you may create
a .bat file with osql command and the execute the .bat file only once.
Also, for such feature request, you may click the link below and give
Microsoft feedback for product improvement. Your suggestions are most
valuable to Microsoft.
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang[MSFT]
Microsoft Developer Online Support
Calling another .sql file from an existing .sql file
the osql cmd.
I would like to know how to make fileA.sql call fileB.sql.
In an oracle based .sql file, I generally would call fileB.sql from within
fileA.sql using
the following script
@.@.fileB.sql
This does not seem to work against sql server.
Thanks.
--
ANeelimaHi
File calls another file? I'm not aware of such issue?
However , you can call the file to do some operations as
UPDATE/INSERT/DELETE/SELECT and then make some logic here.
D you mean that fileA does not contain any info about fileB?
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||Yes, the intention is for FileA.sql to call FileB.sql. Instead of calling
all the files individually from my VB application I wanted to call one single
file called FileA.sql
and have FileA.sql call FileB.sql, FileC.sql, and so on.
I can do that when executing sql files against Oracle without any problems.
So, if it were Oracle, the script in FileA.sql will be
----
set verify off;
set echo off;
@.@.FileB.ql;
PAUSE;
@.@.FileC.sql;
EXIT;
----
If I use the same script against SQL Server I get the error saying that
@.@.FileB.sql is not recognized.
ANeelima
"Uri Dimant" wrote:
> Hi
> File calls another file? I'm not aware of such issue?
> However , you can call the file to do some operations as
> UPDATE/INSERT/DELETE/SELECT and then make some logic here.
> D you mean that fileA does not contain any info about fileB?
>
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
> >I have a vb application that executes a .sql file called "fileA.sql" using
> > the osql cmd.
> > I would like to know how to make fileA.sql call fileB.sql.
> >
> > In an oracle based .sql file, I generally would call fileB.sql from within
> > fileA.sql using
> > the following script
> >
> > @.@.fileB.sql
> >
> > This does not seem to work against sql server.
> >
> > Thanks.
> > --
> > ANeelima
>
>|||In file A, use OSQL (again) to call file B.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
...
To my knowledge there isn't a "built in" preprocessor as you describe.
Generally people use stored procedures instead of file level modules. I
have heard of people using a C language preprocessor with regard to
configuration management with few difficulties.
B|||In order to use osql to call file B from file A, I would have to hard-code
the database and server name in File A as in
osql -E -n -S %COMPUTERNAME%\MYINSTANCE -dENDESKTOP11 -i "FileB.sql" -o
"FileB.log"
But, my goal is to not hard-code the database nor the instance/server name
in FileA.sql since the user chooses the database and server name at run-time
from the VB application.
The VB application then uses the selected names and executes a osql command
and runs FileA.sql using the shell cmd programmatically. Thus I was hoping
that there was a way to simply set the database and server in FileA.sql
(which is the parent sql file) and have FileA call FileB, FileC, etc.
Based on the responses, it appears like I cannot do that as in Oracle.
--
ANeelima
"Arnie Rowland" wrote:
> In file A, use OSQL (again) to call file B.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
> >I have a vb application that executes a .sql file called "fileA.sql" using
> > the osql cmd.
> > I would like to know how to make fileA.sql call fileB.sql.
> >
> > In an oracle based .sql file, I generally would call fileB.sql from within
> > fileA.sql using
> > the following script
> >
> > @.@.fileB.sql
> >
> > This does not seem to work against sql server.
> >
> > Thanks.
> > --
> > ANeelima
>
>|||Hi, ANeelima,
Thanks for your post.
From your description, I understand that:
Your VB application used osql command to execute SQL script files. In your
case, you wanted one sql file can invoke another sql file avoiding hard
code of osql in it.
If I have misunderstood, please let me know.
I don't find other commands or functions can invoke other sql files without
login, which may be different from Oracle.
However, in my experiences, such expression @.@.fileb.sql is supported by
SQL*Plus, while it is not supported by OSQL.
You can only pass the sql file name as -i parameter of osql command.
If you want to just connect your database server only once, I recommend you
use ADO or ODBC to connect the server and the read and execute the sql
files line by line.
Also, if you don't care about the connection times of osql, you may create
a .bat file with osql command and the execute the .bat file only once.
Also, for such feature request, you may click the link below and give
Microsoft feedback for product improvement. Your suggestions are most
valuable to Microsoft.
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang[MSFT]
Microsoft Developer Online Support
Calling an executable file from stored procedure
I have this issue
I'm on a application server and i need to run a executable file from a SP, my problem is if i use xp_cmdshell (from the SP) it will try to run it on the SQL Server not on a App server, this a third party executable so cannnot be run on the sql backend.
Do you know any workaround for this?have the client launch it on it's own.
what business does the server have launching apps on the client anyway?|||jez,
you never call a report engine or a command line DOS thingie from you application code?|||Hi people.
I have this issue
I'm on a application server and i need to run a executable file from a SP, my problem is if i use xp_cmdshell (from the SP) it will try to run it on the SQL Server not on a App server, this a third party executable so cannnot be run on the sql backend.
Do you know any workaround for this?
Is this executable a GUI? If so, don't do it...it could hose everything
If it's just like a bat file, or a batch process then you should be fine|||Actually is a exe file with some parameters (not GUI)
Picture this
Server A
Application Server
Server B
SQL Server 2005
on Server A, i'm executing a SP wich is located on Server B if i use xp_cmdshell is going to look on server B but the exe file is located on server A... can i do like a Remote Execution?|||Why wouldn't you place the executable on both servers?|||won't work the executable on the SQL Server by itself, i need to install the whole application and we don't have the license for it.|||Can't your xp_cmdshell just include the complete path to the executable?
as in xp_cmdshell '\\MyRemoteServer\MysteryJob\DoSomething.bat' works fine where I live.
Worst case you could have your bat file start the exe. We do some of that, though it's too close to 5:30 on Friday for me to make a decent effort at finding the applicable code at this time.|||jez,
you never call a report engine or a command line DOS thingie from you application code?
sure, but that's not how I read the question.
how I read it was, "how do I launch an exe that lives on the client from a stored procedure on the server?"
perhaps I misinterpreted.
Monday, March 19, 2012
Calling a Web Service from DTS?
inserts some of the records into the database, it will need a call a web
service to get photo information for each record it inserts to also write as
a blob to the database.
How/Can I have a DTS call a WS to get additonal data'
JP
.NET Software DeveloperThe first hit should get you started.
http://groups.google.co.uk/groups?h...rver+oj+xmlhttp
-oj
"JP" <JP@.discussions.microsoft.com> wrote in message
news:BFB437D7-D5FB-47B3-AEDD-0272C224B34B@.microsoft.com...
>I have a DTS that will cycle though records coming from a FTP file. As it
> inserts some of the records into the database, it will need a call a web
> service to get photo information for each record it inserts to also write
> as
> a blob to the database.
> How/Can I have a DTS call a WS to get additonal data'
> --
> JP
> .NET Software Developer|||Now you can all a Webservices from SQL (DTS, triggers, SQL Analizar,
Function, Procedure)
Call a WebService from SQL 2000
http://www.rdlcomponents.com/EXSP/default.aspx
Thanks
Jerry
"oj" wrote:
> The first hit should get you started.
> http://groups.google.co.uk/groups?h...rver+oj+xmlhttp
> --
> -oj
>
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:BFB437D7-D5FB-47B3-AEDD-0272C224B34B@.microsoft.com...
>
>
Sunday, March 11, 2012
Calling a package at Runtime
I am working on a package that will sense a text file in a folder. From this text file I will be extracting the file name which will tell me what task I need to run. How can I create a package that, depending upon what the file name is, call a specific package. To be more clear, I will have 3 different flat files that is each in a standard form. Their names will be unique. To transform their data will require different ETL logic. I will be using a loop to constantly check to see if a file has been put into the directory. I just need to know how I can direct the input file to the proper ETL task.
Use precedence constraints between the task that reads the file and three Execute Package tasks. Store the filename in a variable and use that in an expression on the precedence constraint. Make the expression dependent on the variable being equal to the appropriate file name.|||As an aside, you should investigate using a WMI Event Watcher Task at control flow level to detect the files' presence rather than a loop. I believe it is much more efficient.
Calling a SQL script from T-SQL code
I am using Win2K and Microsoft SQL Server 2000.
I have a text file of more than 10000 lines of T-SQL.
Depending on some condition to be determined in another SQL script at run
time,
I should like to have the T-SQL in that text file executed or not.
Can I have a SQL script executed from within another SQL script ?
Thanks for any helpful remarks.
PeterWhy can't you put the script into a proc and then execute the proc
conditionally?
I guess you could make a call to OSQL via xp_cmdshell if you must
execute a script file. I can't think of a good reason to do it that way
but I can think of plenty of good reasons why you *shouldn't* do it
that way!
David Portas
SQL Server MVP
--|||peshrad wrote:
> Hi !
> I am using Win2K and Microsoft SQL Server 2000.
> I have a text file of more than 10000 lines of T-SQL.
> Depending on some condition to be determined in another SQL script at run
> time,
> I should like to have the T-SQL in that text file executed or not.
> Can I have a SQL script executed from within another SQL script ?
> Thanks for any helpful remarks.
> Peter
>
you can create a job with first step to check condition and second to
run the script (if the condition is met)
calling a exe . reading from a text file from a stored procedure
Is it possiable to call a exe file from a sql stored procedure, and what
do i need to look into to do this.
also
Is it possiable to read a text file from a sql stored statement?Tdar,
You can use xp_cmdshell. Do not use this with a graphical application that
required end-user interaction
For the latter, try:
CREATE TABLE #T (TEXT VARCHAR(500))
INSERT INTO #T EXEC MASTER..XP_CMDSHELL 'TYPE C:\TEXTFILE.TXT'
SELECT * FROM #T
--DROP TABLE #T
HTH
Jerry
"Tdar" <Tdar@.noemail.nospam> wrote in message
news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and
> what do i need to look into to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||Look into xp_cmdShell for calling exe's
For reading files use BCP or DTS
http://sqlservercode.blogspot.com/
"Tdar" wrote:
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and wh
at
> do i need to look into to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||1: Read in Books Online about xp_cmdshell. Need to be a console application,
not a Windowing app.
2. Sure. What do you want to do with the data that you read from the text fi
le? Import to a table?
Use BULK INSERT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tdar" <Tdar@.noemail.nospam> wrote in message news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possiable to call a exe file from a sql stored procedure, and wha
t do i need to look into
> to do this.
> also
> Is it possiable to read a text file from a sql stored statement?
>
>
>|||The data will just be on the second line of the text file and semicolon
delimited, that data
Will just be recorded into a table. The Program is an old Dos app so I guess
that is as good as console
As you can get.
Thanks everyone this should help a lot.
David
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uHJlOhEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> 1: Read in Books Online about xp_cmdshell. Need to be a console
> application, not a Windowing app.
> 2. Sure. What do you want to do with the data that you read from the text
> file? Import to a table? Use BULK INSERT.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tdar" <Tdar@.noemail.nospam> wrote in message
> news:e1bd0UEyFHA.2228@.TK2MSFTNGP11.phx.gbl...
>|||If you want to read into a table, check out BULK INSERT, BCP and DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tdar" <Tdar@.noemail.nospam> wrote in message news:OMEuCGFyFHA.2960@.tk2msftngp13.phx.gbl...
> The data will just be on the second line of the text file and semicolon
> delimited, that data
> Will just be recorded into a table. The Program is an old Dos app so I gue
ss
> that is as good as console
> As you can get.
>
> Thanks everyone this should help a lot.
>
> David
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uHJlOhEyFHA.2212@.TK2MSFTNGP15.phx.gbl...
>
Calling a DTS Stored Procedure from ASP.NET
Stored Procedure:
CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUS\sqlServer113 /NdtsPackage /UuserID /Ppassword'
GO
VB to run DTS:
Dim myCommand As SqlCommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "spSampleData"
myCommand.ExecuteNonQuery()
I'm not sure what I am doing wrong but any help would be great.
Thanks!Your code seems ok on the face of it. What is the error message that is generated? Or does just nothing happen?
These links might be helpful:
DTS Cookbook for .Net
Execute a package from T-SQL This one describes your current method but offers another alternative too.|||Thank you so much for the links - I'll check them out. So here are the errors...
When running in Query Analyzer:
-"DTSRun: Cannot create COM Server to load and execute DTS Package. Error -2147024769 (8007007F): The specified procedure could not be found. "
I thought this was because some of the DLLs weren't registered but that isn't it.
-"I tried to schedule it as a job, before I got this just to see, and it gave me a a long hex dec error that was in a windows alert box, so I didn't even worry with that. But it was:"
The procedure entry point ?SQLUIUpdateRegistryFromResource could not be located in the dynamic link library SQLRESLD.dll
In the Webform, it was your typical:
"Object reference not set to an instance of an object" error... So I'm at a loss.|||I hate those sorts of problems. I Googled the error message and ended up with this link.
Wednesday, March 7, 2012
Call SSIS remotely to run exe task in server by C#
I would like to build a C# program to connect to a remote SQL2005 server.
Then call the server SSIS to run a .exe in the server (The .exe file already exist in the SQL2005 server)
I have refer to the following webpage http://www.codeproject.com/useritems/CallSSISFromCSharp.asp
I can connect to the SQL server by the "LoadFromSqlServer" method with SQL server login A/C.
However, the SSIS failed. I believe it should be due to the window authentication issue. During SSIS execution, the SQL server login A/C does not have the access right to run the .exe file
Could anyone suggest the solution?
Thanks.
If you are running the C# executable on your computer, it is not running the SSIS package on the server,it is executing it locally. So it would be running under your account, or it may be that the .exe on the server is not visible from your computer.
To actually run the package locally on the server, you'd need to create a web service, or use SQL Agent to run the package. This post from Michael Entin has more details: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx
Saturday, February 25, 2012
Call a DLL or EXE file from SQL Trigger
I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE TRIGGER CallProgIfParentID
ON Toc
FOR INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57660')
-- I want to be able to programmatically change '57660' from an ASPX page
EXEC
-- This is where the call function goes
GO
Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?
Thank you in advance
I have to warn you first: although calling external exes is possible, it is not recommended as there are at least a couple obvious drawbacks:
1. You would possibly lose data integrity because those external processes are NOT bound to the SQL transaction that triggers run in. For example, if the INSERT statement that fires the insert trigger is rolled back, regular trigger actions would be rolled back too, but those external processes would not. Similarly, triggers might not be able to detect errors that happen to those external processes and it ends up with the external exe failed but the trigger (as well as the firing statement) succeeded.
2. These processes will run outside of SQL Server, so you would lose total control of them. E.g. they might come back and compete with SQL Server for resources like CPU and memory.
I am wondering what kind of scenarios you have, but there got to be a better way to do it :)
Anyway, if you still decide to go with this route, you could call external exes from a trigger by:
- xp_cmdshell '<some exe>'
- For a *unsafe* CLR trigger, you could practically do anything, including calling external processes (e.g. Process class)
For you other question, yes, you can create triggers programmatically from wherever you can connect to the server.
|||set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER SendMailIfParentID
ON Toc
AFTER INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57660')
EXEC master ..xp_cmdshell 'C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe 57660'
Thats what I have for my trigger now (The 57660 is a test ID number and isn't of much relavence), either the trigger isnt being called or the EXE isn't being called. I can't figure out why it's not working.
|||I figured out what wasn't working and fixed it. Now I can't get a variable to append to the end of the command line. It keeps telling me the + isn't valid. Does anyone have any ideas?
ALTER TRIGGER SendMailIfParentID
ON Toc
AFTER INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57750')
DECLARE @.MyTocId varchar(12)
SELECT @.MyTocId = (SELECT TocId FROM inserted)
EXEC master ..xp_cmdshell '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + @.MyTocId
GO
|||Hi,for debugging and better handling purposes, I would suggest first putting evverything in a varaible and executing this afterwards:
IF ((select ins.[ParentID] FROM inserted ins) = '57750')
DECLARE @.MyTocId varchar(12)
SELECT @.MyTocId = '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + TocId FROM inserted
EXEC master ..xp_cmdshell @.command = @.MyTocId
Warning: Triggers are fired per statement not per row, you will in addition make sure that your trigger is able to handle multiple rows affected in a trigger. In further addition you will have to make sure that the command is not executed as no row is affected as the trigger is fired (as already said) on a statement basis (even if the affected rowcount is 0).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Sunday, February 19, 2012
calculation on a varchar field
Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?I'm importing data from a text file into SQL as a varchar, and I'm leaving it a varchar in its final destination table. It is essentially a price, i.e., $25.65. I'm using this price field (varchar) to perform a calculation...
Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?
Why use VARCHAR? Sounds like you want to use currency. You can use CAST at runtime. However, if there are formatting problems, I would think you would want to deal with such errors at import time rather than afterwards during calculations.