How To Make BAT File Of Stored Procedure
Mar 29, 2013
I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.
Here is my stored procedure:
USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO
[code]....
View 10 Replies
ADVERTISEMENT
Jan 7, 2007
Hi
In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this
Alvin
View 1 Replies
View Related
Feb 15, 2000
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies
View Related
Jan 22, 2008
Hi,
I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.
Thanks in advance for any help.
Regards,
Sangeetha
View 6 Replies
View Related
Dec 11, 2006
Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well.
Thanks...
View 1 Replies
View Related
May 8, 2012
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
View 7 Replies
View Related
Jun 22, 2006
Hello.
I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.
Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!
--------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure aes.Check_Dependent_Rows_Exist
(@RowID int,
@has_rows int OUTPUT
)
AS
BEGIN
DECLARE @Colname varchar(200), @Tablename varchar(200)
DECLARE @cnt int
DECLARE @temp_row int
DECLARE @owner varchar(25)
DECLARE @ownerid int
DECLARE @lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR
SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'
OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not
SET @has_rows = 0
FETCH NEXT FROM lcur INTO @Colname,@Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_row = 0
SELECT @ownerid = uid from sysobjects where name = @Tablename
SELECT @owner = [name] from sysusers where uid = @ownerid
SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' +
@Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + ''
--print @lstrSql
EXEC (@lstrSql)
SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @has_rows = 1
BREAK
END
FETCH NEXT FROM lcur INTO @Colname,@TableName
END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------
error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.
(1 row(s) affected)
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
View 6 Replies
View Related
May 21, 2008
hi,
I need to convert a stored procedure in to a SSIS package, do any body have an idea on this. thanks in advance
View 1 Replies
View Related
Jul 9, 2006
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
View 1 Replies
View Related
May 1, 2008
Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) )
AS
UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ?
View 2 Replies
View Related
Jul 20, 2005
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies
View Related
May 10, 2007
I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies
View Related
Jan 31, 2008
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
View 7 Replies
View Related
Oct 13, 2006
I have defined an email as a .html file on my server: /Emails/email.htmlThis file defines what the email will look like and in the text I have placed tags that need to be replaced with values.A tag that requires replacement looks like: <#SENDERNAME> or <#RECEIVERNAME>I want to replace these tags with the names of the sender and the receiver respectively.AFTER this is done the email needs to be sent to the receiver's address, let's say: receiver@yes.comI want to create a stored procedure that takes as input the sender and receivername AND receiveraddress.It then uses the file I have defined on my server, replaces the tags and sends the email.How can I do this?!
View 1 Replies
View Related
Mar 23, 2008
Is there a way I can use one SQL server 2005 (full edition not express) Stored procedure that can execute a .SQL file as if it were in the SQL management studio Query window?
I have the situation where I have some .SQL file which were created with the 'Create to File' option in SQL Management studio. I then want to send them to a user who can execute them to create a SP on his SQL server without having to run the SQL management tools.
Regards
Clive
View 8 Replies
View Related
Apr 3, 2008
Hi..
I have a website in which the user uploads a text file to the webserver. and then i am calling a stored procedure which loads the data into the staging database using bulk copy. since my sql database and the web server are in different machines but on the same domain.. i am accessing the .txt file from the stored procedure by giving http://Ipaddress/ApplicationName/Directory/plan.txt
and the format file using the same address i.e http://Ipaddress/ApplicationName/Directory/plan.fmt
But when i press ctrl + click on the text file it take me to that location but when i do the same for the format file it says the page cannot be found.. Since it cannot access the .fmt file my import doesnt work.
Any help will be appreicated..
Thank you,
Karen
View 4 Replies
View Related
Aug 24, 2004
hi to all,
i have a stored procedure and i want to run a program from it..
i think that i need use api functions but how can i do that..
if there is a nother ways please tell me..
thanx..
View 3 Replies
View Related
Nov 30, 2005
hi,i am passing a xml file name to the stored procedure. the SP parses the file. but it is giving the error' INVALID AT THE TOP LEVEL OF THE DOCUMENT 'I expect this because of + and - in the xml file bafore the parent tags.how can i do the parser to eliminate these.
View 1 Replies
View Related
Aug 5, 2002
Anyone have the code used in DTS to make a connection to Excel? Or perhaps a better idea? I need to pass different file names to a stored procedure which would upload the spreadsheet data into SQL7 on demand.
View 1 Replies
View Related
Sep 7, 2000
Does anyone know how to print to a file or printer using T-SQL or stored procedure?
Thanks
View 1 Replies
View Related
Mar 18, 2002
I've been administering SQL Server for quite a while, but have managed until now to stay out of the development side. I find myself finally writing some complex SProcs. What I want to do is to create a "switch", i.e., a parameter that will output a few lines of text characterizing the SProc, with such information as required & optional parameters, potential "gotchas", and a handful of examples of usage. I want this to be able to work like this:
EXEC spMySProc '?'
or
EXEC spMySproc 'help'
This has to work even if there are multiple required parameters.
Thanks for your help!
View 1 Replies
View Related
Jun 16, 2008
Reading through the forums, I found some great imformation for importing/exporting an excel spreadsheet via a stored procedure, however, the amount of data I have won't fit in excel. Can someone help me export a CSV file via a stored procedure? I don't know if XML is the answer or if there is another way. I am able to use Bulk for an insert of a csv via stored procudure, although it doesn't allow me to use a variable for the file name and pass it in. We are currently using SQL Server 2000.
Thank you,
Stacy
View 1 Replies
View Related
Jan 28, 2008
Hi there,
Does anyone know any possible way of creating a dbf file from within a stored procedure?
Any guidence, articles, examples, topics to revice would be a great help.
Thanks guys
Butterfly82
View 6 Replies
View Related
Jul 23, 2005
Hello everybody,I have a file with several asp functions where some depend on others.Some of the functions I will need to transform a string value.How can I include these functions in a stored procedure? (in SqlServer 2000)Do I have to add all the functions as userdefined functions?Or is possible to reference the external file like include files inasp?Thank you for your help!Regards,Michael
View 3 Replies
View Related
Jul 20, 2005
I need to develop some crystal reports on some .NET ado datasets.This is easy to do if I actually had data to work with. It is mucheaser creating reports with you have data.Therefore, I would like to run the stored procedure that the .NET adodataset is built on and generate an XML file. I could run the projectand try to figure out where the developers are making the call to thestored procedure and insert a line to writetoxmlfile. I would rathernot have to mess with their code.Is there a way working with SQL Server (either query analyzer orenterprise manager, dts, or whatever) that I can generate an xml file.I see that I can run a stored procedure and get an xml style return inquery analyzer, but I don't know how to save that as an actual file.Thanks for the help.Tony
View 3 Replies
View Related
Jul 19, 2006
All I want to do is copy a text file and add a 1 after the name in a stored procedure that already cretes this file.
CREATE PROCEDURE [dbo].[sp_export_req] AS
DECLARE @trenutniRed varchar(30),
@tableRow1 varchar(3000),
@sql varchar(4000),
@sql1 varchar(4000),
@firstColumnName varchar(30),
@HTMLfile varchar(1000),
@columnNumber varchar(30),
@fs int,
@ole int,
@file int
declare @body as nvarchar(225)
CREATE TABLE #temptableHTML (code nvarchar(225))
select @HTMLfile = 'c: est_new.txt'
This is the start of the procedure that creates the test_new.txt file. I want to copy the test_new.txt file and name it test_new1.txt. Any help is appreciated. thank you
View 1 Replies
View Related
Apr 3, 2008
Hi..
I have a website in which the user uploads a text file to the webserver. and then i am calling a stored procedure which loads the data into the staging database using bulk copy. since my sql database and the web server are in different machines but on the same domain.. i am accessing the .txt file from the stored procedure by giving http://Ipaddress/ApplicationName/Directory/plan.txt
and the format file using the same address i.e http://Ipaddress/ApplicationName/Directory/plan.fmt
But when i press ctrl + click on the text file it take me to that location but when i do the same for the format file it says the page cannot be found.. Since it cannot access the .fmt file my import doesnt work.
Any help will be appreicated..
Thank you,
Karen
View 5 Replies
View Related
Jul 25, 2007
Hello all,
I have a big stored procedure which is going to alter many tables,insert data, basically lot of changes.
So, i want to have a text file (or) any log file which will display, what all the changes does the stored procedure has done ( They dont want profiler output )
Can anybody know how to log the results of execution of stored procedure to a text file.
Thanks.
View 2 Replies
View Related
Apr 26, 2007
In XSD file keep specification of each tables
So I think It would have some class or method to generate common stored procedure from xsd file.
Please help me.
View 1 Replies
View Related
Oct 24, 2007
ok...i give up.....
can this be done? (sql server 2000)
all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.
here's my current working file output code:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P'
i'd like it to be something like this:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P'
but nothing seems to work.
View 16 Replies
View Related
Dec 18, 2007
i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
View 7 Replies
View Related
Mar 15, 2008
hi all
I have to execute the stored procedure from code file
string constr = ConfigurationSettings.AppSettings["ConnectionString"];SqlConnection con = new SqlConnection(constr);con.Open();SqlCommand cmd = new SqlCommand("GetTax",con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter paramFrom = new SqlParameter("@from", SqlDbType.VarChar, 50);paramFrom.Value = "JFK";SqlParameter paramTo = new SqlParameter("@To", SqlDbType.VarChar,50);paramTo.Value = "HOU";SqlParameter paramAirline = new SqlParameter("@Airline", SqlDbType.VarChar,50);paramAirline.Value = "US";SqlParameter rpTax = new SqlParameter("@Tax",SqlDbType.Int);rpTax.Direction = ParameterDirection.Output;cmd.Parameters.Add(rpTax);
insted of this way can i execute stored procedure any other way
like
exec MystoredProc "param1"."param2","param3"
i appreciate u r help
View 2 Replies
View Related
Aug 4, 2004
Hi guys,
I need to be able to send a text file with data seperated by tabs to a stored procedure that populates my table.
I am new to SQL and SPROCs that I am not sure how to even start. If you guys have any ideas I would really like to hear them or maybe any on-line docs and examples.
Thanks in advance!
View 2 Replies
View Related
