How To Execute Stored Procedure By Command Line In Client's Computer ?
Nov 28, 2006I've a S.P. that need to execute in local PC, how can I execute ?
View 1 Replies
ADVERTISEMENT
Can Stored Procedure Execute A Dos Command?
Jul 14, 2001Is it possible to create a stored procedure to run a custom dos command (eg. c:ProgramName param1 param2)?
Thanks,
Ben
Execute Stored Procedure (with Parameters) With An Exec Command
Feb 21, 2004Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
Execute DTS Package Via Command Line
Mar 7, 2002hello to all, and I hope you can help.
this is the code from BOL
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Mpackage_password /Rrepository_name
I have followed it and come up with ...
dtsrun /SPC-409 /Usa /Pmypassword /Nemployee_export /M? /R?
where can I know the repository_name, is it one of the following:
DTS package
Meta data service package
Meta data
all under Data Transformation Service folder in SQL Server
Thanks
Al
C# UI Escape Character Problem To Execute Command Line
Aug 3, 2006
Hi!
Thanks For your reply!
but this is very urgent please help!!!!!!!
I need one more help in this issue. what if i do not need any "
for e.g: i am trying to set conn string and varaible value
jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f "" + path + "" /Conn "" + Packconn + "" "" + connect + "" '",cconn);
i am getting some value like this :
CommandText "xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "SE413695\AASQL2005.TestDB;" "Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;" '" string
I do not need the highlighted escape characters in it. what should i do??????
i need some thing like this :
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn SE413695AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
Thanks,
Jas
Passing Command-line Options Through To Package.Execute()
Mar 8, 2008
Hi,
I'm looking into the idea of building an enhanced version of dtexec.exe that builds in some extra logging features. My utility will execute packages using the Package.Execute() method.
Thing is, I'd still want to support all of the command-line options that dtexec supports. For example, my utility should accept "/set package.variables[myvariable].Value;myvalue" and pass it through to the executing package but I can't find a way of doing it using Package.Execute().
Am I missing something or is this just not possible?
Thanks
Jamie
[Microsoft follow-up]
How To Pass Command Line Parms To The Execute Package Task
Oct 2, 2006I have a ForEachLoop parent pkg that reads thru the file system of dtsx to be executed.
I would like to pass CLI parms to the command itself (dtexec). I'm not looking at passing values to the child pkgs but rather additional command line values such as:
/CONFIGFILE "C:myconfig.dtsConfig.
In my expression builder for the PackageName I added the additional string but during the execution, the reference to my dtsconfig is ignored without any errors and the child pkg runs using the default values provided at design time.
@[User::PackageToRun] + " /CONFIGFILE C:\myconfig.dtsConfig ".
How does one add additional CL values during the runtime for Execute Package Task?
Thanks,
Anatole
Job To Execute A SSIS Package Fails - The Command Line Parameters Are Invalid
Jul 28, 2005I have created a job to execute a SSIS package located in the SSIS Package Store. When starting the job I receive an error. The history log reports:
View 12 Replies View RelatedIntegration Services :: Illegal Characters In Command Line On Execute Process Task Editor
Aug 26, 2015I'm trying to execute a simple VBS file from the Executable command line in the Execute Process Task Editor.
My line is this : cscript.exe "c:convertcsvssisXlsToCsv.vbs"
SSIS keeps saying there are illegal characters here. I've Googled and looked about 20 articles and I can't resolve it.
I have a ForEach that loops through Excel files and changes them to CSV files using code i found. This script takes an original Excel file and transfers it to a new CSV file in a new directory.
So in DOS at the CMD line I would type : XlsTocsv.vbs originalfile.xls newfile.csv
I have the original file and new file in the Arguments line so I'm assuming that after the script executes it will look at the filepaths in the loop and loop through them so I want it do to this when it runs:
XlsTocsv.vbs [User::@ExcelFile] [User::@CSVFile]
I just can't get it to execute and I keep getting illegal characters.
Dtsrun Works From Command Line, Not From Stored Proc
Jun 30, 2000I need to execute a dts package from a stored procedure. I can call up the command prompt, enter the dts command, and it executes perfectly. Here is what I am attempting to do from the stored procecure:
EXEC master..xp_cmdshell 'dtsrun /S MyServer /E /N PackageTest'
On execution, the following error is returned:
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21)
Error string: Error opening datafile: Access is denied.
I would appreciate any assistance.
Execute SSIS Package Stored In Remote SQL Server From A Client Machine.
Mar 3, 2006I have written a VB component to execute a SSIS package stored in SQL server.
I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand
and executereader and everything works perfect on the local machine. This is descibed in a msdn site .
But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.
My question is: Can you still use dtsclient or do you have to do in some other way?
rose-marie
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Sep 13, 2007Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
TCP-Client As Clr Stored Procedure?
Aug 22, 2007
Can messages be sent from sqlclr to an TCP-Server?
Regards
pamelia
CLR Stored Procedure And Stack Trace Line Numbers
Sep 27, 2007Hello you all CLR stored procedure experts,
When a self-developed CLR stored procedure throws an Exception and the Exception is caught in the code and for example logged, there only are class and method names in the stack trace lines, but not the line numbers. Can the line numbers somehow be included in the stack trace?
I'm not familiar with .NET framework stack traces, but when I tried to find out more information I hit concepts like PDB files, are such things somehow needed to enable line numbers in stack traces?
Big thanks already in advance,
J
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedDeploy Database Application To Client Computer?
Oct 1, 2015I want to deploy my database application to my client's computer. My application uses SQL Server 2014. My client has to run that software on a single PC. I was looking to download a lightweight version of SQL Server that can fulfill my requirements, so when I was looking for SQL Server downloads, I saw these files there, which file should I use in my situation
Express 32BIT WoW64SQLEXPR32_x86_ENU.exe
Express 32BITSQLEXPR_x86_ENU.exe
Express 64BITSQLEXPR_x64_ENU.exe
ExpressAdv 32BITSQLEXPRADV_x86_ENU.exe
ExpressAdv 64BITSQLEXPRADV_x64_ENU.exe
ExpressAndTools 32BITSQLEXPRWT_x86_ENU.exe
ExpressAndTools 64BITSQLEXPRWT_x64_ENU.exe
LocalDB 32BITSqlLocalDB.msi
LocalDB 64BITSqlLocalDB.msi
MgmtStudio 32BITSQLManagementStudio_x86_ENU.exe
MgmtStudio 64BITSQLManagementStudio_x64_ENU.exe
EXEC Stored Procedure For Every Line Of SELECT Result Table - How?
Jul 23, 2005Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies View RelatedExecute Stored Procedure From Stored Procedure With Parameters
May 16, 2008Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated.
Thank you,
Dave
Connecting To Sql Server From Local Computer Using Client Tools
Jul 15, 2004Hi,
I just installed SQL Server for the first time on a windows 2003 OS which is on its on server. Then, I installed the client tools on my local computer. Now how do I connect to the sql server with my client tools? What I wanted to do is from my local computer use the enterprise manager and pull up the databases that are located on the server that contains the sql server.
Thanks a bunch,
Laura ;)
How To Insert Record To SQL Server Which Is On The Other Machine From Client Computer?
Jan 14, 2007I want to write simple sql database project that that works in the network. and i need to insert record from a client to a server that is on the other machine.
View 1 Replies View RelatedStored Procedure Or Command In Sql
Oct 23, 2007Hi fellows
I am working in a formula about drilling machine. This machine sends the inofrmation to the server basically the name of a blasthole, revolution,pulldown and so on. I have another database with information of the same blasthole, but with different items such as drill diameter,site,area and so on, so i have two tables with information, but both of them have the same blasthole that means that i can relate with a join and identify each blasthole. but teh main point is that i have a specific column con a distance that the machine is drilling for example in a certain moment this machine is drilling 1 feet 3 feet 4 feet until to reach the bottom of a limit. his
My problem is that i want to create a from to for those distances.
table 1
pk diameter bench
dh1 12.5 4050
table 2
blasthole depth
dh1 1.2
dh1 3.5
dh1 4.7
dh1 5.4
dh1 8.3
dh1 15
with a join i can obtain something like this
view 1
blasthole diameter bench depth
dh1 12.5 4050 1.2
dh1 12.5 4050 3.5
dh1 12.5 4050 4.7
dh1 12.5 4050 5.4
dh1 12.5 4050 8.3
dh1 12.5 4050 15
the most difficult part is to include a new colum taht can start with 0 and to obtain teh previus value from depth something like this
view 1-A
blasthole diameter bench from depth
dh1 12.5 4050 0 1.2
dh1 12.5 4050 1.2 3.5
dh1 12.5 4050 3.5 4.7
dh1 12.5 4050 4.7 5.4
dh1 12.5 4050 5.4 8.3
dh1 12.5 4050 8.3 15
I was thinking to create a pk2 in order to know how many values have each blasthole and create a loop and maybe i can organize the column from something like this
pk2 blasthole diameter bench from depth
1 dh1 12.5 4050 0 1.2
2 dh1 12.5 4050 1.2 3.5
3 dh1 12.5 4050 3.5 4.7
4 dh1 12.5 4050 4.7 5.4
5 dh1 12.5 4050 5.4 8.3
6 dh1 12.5 4050 8.3 15
I hope that somebody can understand my question, but i think is a hard one for the forum
cheers
Edwin
SQL Server 2012 :: Displaying Code Of A Stored Procedure To A Single Line
Sep 14, 2015Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
ORDER BY Command In SQL Stored Procedure
Nov 5, 2003How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%'
)
As
SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost
FROM
SS_Sendback
WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO
GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO
ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%',
@Order varchar(20)
)
As
SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost
FROM
SS_Sendback
WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO
GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO
ORDER BY
@Order
GO
and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanks
BCP Command Hangs Stored Procedure
Sep 30, 2013I have a problem where bcp command is called and executed successfully sometimes but fails randomly.My stored procedure looks like this:
select @SQL = 'select * from [' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'
execute(@SQL)
set @Count = @@ROWCOUNT
if @Count > 0
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
[code]....
The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.
ADODB Command (Stored Procedure)
Jun 4, 2007Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.
View 8 Replies View RelatedSQL Server 2012 :: Using CTAS Create Stored Procedure For Client
May 14, 2014I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.
I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.
I want to create sp as like execute this way Exec @MICHELIN_US_
So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_
And it will change all table names to UNITED_ACTIVITY
I want to create this SP for different client.
Execute URL From Stored Procedure!
Apr 14, 2004Hi,
Can anybody please tell me if it is possible to execute an url from stored procedure.
thanks in advance,
preeth
Execute Stored Procedure
Aug 10, 2000Hello:
I have created a stored procedure that will backup and restore a database.
I would like to be able to execute this sp using public rights - any ideas?
Thanks
Execute Stored Procedure
Jun 14, 2008How can I use in stored procedure the faction ‘’in’’ to select values, using execute and the values must not be specified before in the stored procedure
View 6 Replies View RelatedExecute A Stored Procedure
Jul 23, 2005How do you execute a stored procedure in MS SQL Server?I can design and execute them from MS Access dev front end, but cannot seemto find how to run them in the Enterprise Manager.TIA.~ Duane Phillips.
View 2 Replies View RelatedExecute Stored Procedure
Mar 29, 2006I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!
View 3 Replies View RelatedHow To Call A Stored Procedure In An Insert Command
Feb 22, 2006hi,i had a small doubt , i have a table xxx with two columns (a int,b int) and i have inserted 5 rows my query is to add the two colums using astored procedure and the result has to be displayed in an separatecolumn --this has to be done only stored procedures ---i know how tosolve the problem using computed columns conceptlike thiscreate table yyy(a int ,b int ,total as a+b)insert into yyy values (12,13)select * from yyyi need the answer using stored procedures---is it possiblepls help mesatish
View 1 Replies View RelatedOLE DB Command And Stored Procedure That Returns Value And/or Error
May 23, 2006Guys,
could someone please tell me : am I supposed to use the OLE DB
Command in a dataflow to call a stored procedure to return a value? Or
is it just supposed to be used to call a straightforward insert
statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a
dataflow I would like to effect an insert of a record to this table and
retrieve the identity value of the inserted record... and I'd like to
store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by
clicking on Add Column) I just get an error dialog that says "the
component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ