How Can I Include A Timestamp On A File Name In A Stored Procedure???

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


ADVERTISEMENT

Hi I Want To Know How To Write Stored Procedure ..then I Have To Include (IF Condition ) With SP ..

Sep 20, 2007

hi i want to know how to write  stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????

View 5 Replies View Related

Update Row Using Stored Procedure With Timestamp

Oct 10, 2005

Hi,I'm attempting to update one row using strored procedure which check timestamp field.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'tbCharakterystyki_Update')BEGINDROP Procedure tbCharakterystyki_UpdateENDGO
CREATE Procedure dbo.tbCharakterystyki_Update@CH_ID int ,@CH_CHARAKTERYSTYKA VARCHAR(30),@CH_OPIS VARCHAR(300),@CH_TIMESTAMP timestampAS
UPDATE tbCharakterystyki SET ch_charakterystyka = @CH_CHARAKTERYSTYKA,
ch_opis = @CH_OPIS
WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP
GO
Function which call stored procedure:public int CallSP(DataRow dr)    {      try       {             SqlConnection conn = new SqlConnection(this.strConnection);        SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;         cmd.CommandType = CommandType.StoredProcedure;         cmd.CommandText = "dbo.tbCharakterystyki_Update";
        SqlParameter p1 = new SqlParameter();        p1.ParameterName = "@CH_ID";        p1.SqlDbType = SqlDbType.Int;        p1.Value = dr[0];
        SqlParameter p2 = new SqlParameter();        p2.ParameterName = "@CH_CHARAKTERYSTYKA";        p2.SqlDbType = SqlDbType.VarChar;        p2.Value = dr[1];
        SqlParameter p3 = new SqlParameter();        p3.ParameterName = "@CH_OPIS";        p3.SqlDbType = SqlDbType.VarChar;        p3.Value = dr[2];
        SqlParameter p4 = new SqlParameter();        p4.ParameterName = "@CH_TIMESTAMP";        p4.SqlDbType = SqlDbType.VarChar;        p4.Value = dr[3];
        cmd.Parameters.Add(p1);        cmd.Parameters.Add(p2);        cmd.Parameters.Add(p3);        cmd.Parameters.Add(p4);                cmd.Connection.Open();        cmd.ExecuteNonQuery();        cmd.Connection.Close();        return 0;          }      catch ( Exception err )       {         ShowError(err.Message)                return null;      }     }
And function which call "CallSP()":private void Update(){DataSet ds = helper.GiveDataSetCharakterystyki()DataRow dr = ds.Tables[0].Select("ch_id=51")[0];
dr[1] = "new value";CallSP(dr);}When I fire Update(), stored procedure (dbo.tbCharakterystyki_Update) is not started, Sql Profiler doesn't show it.Whilst when I modify stored procedure, removing WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP and put only WHERE ch_id = @CH_ID , it works perfectly.What I did wrong?
 

View 1 Replies View Related

Transact SQL :: How To Get Info Which Stored Procedure Updated A Column For Particular Timestamp

Jul 31, 2015

How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?

View 3 Replies View Related

Export Stored Procedure To Flat File And Add Aggregate To End Of The Text File?

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

How To Include SSIS Into Install File?

Mar 14, 2006

I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer

Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.

What will I need to do now to use old DTS and new SSIS packages on these clients?

View 10 Replies View Related

DTS File Name To Include System Date/Time

Jul 23, 2005

We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)? I would really appreciate help on this. Thank you.

View 1 Replies View Related

Renaming A File To Include Date And Time

Jan 16, 2007

Hi

I am trying to move a flat file to an archive folder and then rename the file to include the date and time of runtime.

I have set up a File System Task to move the file which works fine and a second File System Task to rename. The IsDestinationPathVariable is set to TRUE and the DestinationVariable is called User::Error_Log_File_Rename. There is an expression as follows:

@[User::Error_Log_File_Rename] = "Y:\SSIS_PUD_Upload\Error_Log_Archive\Update_Error_Messages - " +REPLACE((DT_WSTR, 30)(DT_DBDATE)GETDATE() + "-" + (DT_WSTR, 30)(DT_DBTIME)GETDATE(),":","-") +".txt"

which is the required file name.

If I go into the expression and evaluate it it will give me the current date and time. However when I run it as part of the package say 10 minutes later the file is renamed using the same date and time from 10 minutes before when I evaluated the expression. If I wait another 10 minutes and run the package again it still retains the original date and time.

Is there a way to get the expression evaluated with the current date and time during execution of the package?

Thanks in advance

Scott



View 3 Replies View Related

How To Include Looked Up Value In Excel Output File Name

Aug 3, 2007

I have set up an export package in SSIS and I would like to assign a file name based on the value in a table on my SQLServer. For example, my file name might be Monthly Reporting Package 20070731.xls where 20070731 is a value in my date table. I can retrieve my date by the following select statement: "SELECT Date FROM DecodeTables.dbo.Date". Can I set up a variable to look up this value and then concatenate it into my file name?

View 3 Replies View Related

Include Code In Various Stored Procedures

Feb 2, 2006

I have some lengthy code that creates a #Temp file. I would like to access this code from different stored procedures. Is there such a thing as an INCLUDE statement for stored procedures, or is this what the EXEC statement accomplishes?

View 12 Replies View Related

Add Timestamp To The File

Sep 24, 2007

Hi,

I am moving a file to another folder after processing. I want to append the month, year and time. I have been able to append yr and month using this

(DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )

How to append the time to this?

thanks

View 3 Replies View Related

Integration Services :: SSIS Package Needs To Include A Target File To Another Server

Apr 24, 2015

I am created a SSIS package to export data.  I am exporting query data to a flat file to a different server.  I tried to use the UNC path and it failed saying could not access the file.  How can I create a SSIS package to export data from one server to another?

View 5 Replies View Related

Stored Procedure That Uses File On Server

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

Creating A Stored Procedure From A File

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

Cannot Open .fmt File Using A Stored Procedure

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

How Can I Execute A N Exe File From Stored Procedure?

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

How To Pass Xml File To A Stored Procedure

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

Excel File & Stored Procedure

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

How To Print To A File Using T-SQL Or Stored Procedure

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

&#34;Help&#34; File For User Stored Procedure

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

Exporting A Csv File Via A Stored Procedure

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

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 View Related

How To Create A Dbf File From A Stored Procedure?

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

Use Function From Asp File In Stored Procedure

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

Generate XML File From Stored Procedure

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

Copy File In Stored Procedure

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

Cannot Open .fmt File Using A Stored Procedure

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

Logging Stored Procedure Changes Into File

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

SQL Server 2012 :: Include Source Feature In More Than One Stored Proc

Sep 2, 2015

I need to use the same SQL source code in more than one stored proc.

Is there a way to -include mysql.sql or something like that ?

Is InsertSnippet the only way to do this ?

,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal

View 9 Replies View Related

SQL 2012 :: How To Get Name And Timestamp Of File From A Directory

Mar 18, 2015

I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.

Is there any other way at all to achieve this directly from SQL Server using T-SQL?

View 3 Replies View Related

Timestamp Ans Status Of Database File

Apr 21, 2007

Our database is running in full recovery mode and we backup the logfile every hour and the database file every night. I notice thetimestamp of database file *.mdf remains unchanged a few days. Forexample one database has its timestamp at Apr 15 while it is Apr 20already.I am wondering if this is acceptable. How do I force the system toflush the data and dirty blocks to the disk more frequently? What isthe downside to this?Thanks for any idea.

View 1 Replies View Related

Retrieving And Storing File Timestamp

Feb 20, 2006

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

View 1 Replies View Related

Data Source File Name Has A Timestamp

May 3, 2006

I have 2 years worth of data that are stored in individual .dbf files for each day. Is there a way to 1 quickly import all of these tables into one and 2. move the timestamp from the file name to a date column?

Any help would be greatly appreciated

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved