Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Generate Text File With Timestamp


 
 I am trying to generate a text file with current timestamp uisng SSIS. What is the best way to do this? can you show me some examples?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Generate A Text File From SQL Server
Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char  and uses "". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161

I am no idea about this problem. Please give me some suggestions. Thanks a lot


regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

View Replies !   View Related
Generate SPs As Text
Is there an easy way to loop through every stored procedure in adatabase and create a file containing all of the SP code?lq

View Replies !   View Related
Generate A Separate Txt File For Each Account In A Table, Need To Join Tables To Get Details, And Specify Output File Name?
Hey,

 

I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?

 

I have table1 contain account numbers and output-filename for each account,

I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.

 

Here is my code using bcp (bulk copy) 
 
DECLARE @RowCnt int,
            @TotalRows int,
            @AccountNumber char(11),
            @sql varchar(8000),
            @date char(10),
            @ArchPath varchar(500)
 
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT      @TotalRows = count(*) FROM table1
--select @ArchPath
 
WHILE (@RowCnt <= @TotalRows) 
      BEGIN
            SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
            --PRINT @AccountNumber --test
            SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD    FROM table2 d     INNER JOIN table3 h ON d.HeaderID = h.HeaderID  WHERE d.ccountNumber = '''
                                    + @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
            --PRINT @sql
            EXEC master..xp_cmdshell @sql
            SELECT @RowCnt = @RowCnt + 1
      END
 

View Replies !   View Related
Add Timestamp To The File
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 Replies !   View Related
Timestamp Ans Status Of Database File
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 Replies !   View Related
Capture Date And Timestamp Of A File?
Hi,

I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.

Any ideas?

Thanks in advance for your help.

$wapnil

View Replies !   View Related
Data Source File Name Has A Timestamp
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 Replies !   View Related
Retrieving And Storing File Timestamp
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 Replies !   View Related
How Can I Include A Timestamp On A File Name In A Stored Procedure???
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 Replies !   View Related
How To Redirect The Error Of A Source Text File To The Other Text File?
Hi all,

I m using SSIS and i am transfering the data from Flat File Source to the OLE DB destination File. The source file contain some corrupt data which i transfering to the other Flat file destination file.

Debugging is succesful but i am not getting any error output in the Flat file destination file.

i had done exactly which is written in the msdn tutorial of SSIS.

Plz tell why i am not getting the error output in the destination flat file.

thanx

View Replies !   View Related
Importing DATE With Timestamp(In A Flat File) Column Using SSIS
Hi

SSIS is brand new for me.. Playing with since a few hours..

Iam trying to import a Flat File into the SQLSERV DB using SSIS..
One of the column is in this format -- "YYYYMMDDHH24MISS"

How do i get around this to import the data in a readable fashion into the Destination?

Thanks!
MKR

View Replies !   View Related
How To Generate Txt File
hi all,

how to generate txt file from sql table. I want text file with respective columns without any seperator between columns. and if for an example a column of int having 5 digits but i want it in text file with 10 digits with remaining all as blanks. guide me how to do this.
thnks in adv

View Replies !   View Related
Generate A .txt File
This is my first time on sqlteam forum so a big hello to everyone!!

Firstly I am very noive user of sql to say the least, but i have be requested to create a .txt file

Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc

MR
JOE
BLOGGS

Here is the my code so far, like i said i am a very novice user so try to help me and not condem me for my lack of knowledge!

SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not to how to do that and how to generate it in a txt file?!

Keith

View Replies !   View Related
Generate Script File
i want to generate a sql script file along with the data

how to generate it?

View Replies !   View Related
Generate SQL Scripts To File Using DTS
Hi everybody
EM has ability to Generate SQL Scripts to file(S)
I traced it with profiler but can not see how Sql server output objects to file(s)

I want to run DTS and sript all procedures to files every night
I don't want to use
xp_cmdshell 'osql -UTheUser myPass-P -Qsp_helptext 'Mystproc -oc:est.txt'
Could it be done another way ?

View Replies !   View Related
Process Of Generate Xml File
 

hi i was trying to generate a .xml file by using this command below
 
 

DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)

SET @Table ='Authors'

SELECT TOP 1 @Filename = au_fname FROM Authors

SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'

SELECT @FileName

DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)

SET @Table ='Authors'

SELECT TOP 1 @Filename = au_fname FROM Authors

SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'

SELECT @FileName



SET @SQL = 'EXEC master..xp_cmdshell '+ ''''+'bcp'+ ' "SELECT ''au_fname '' AS ''Name/First'', au_lname AS ''Name/Last '' FROM pubs.dbo.authors ORDER BY au_lname FOR XML PATH (''Books''), ROOT(''Authors''), TYPE, ELEMENTS " queryout '+ @FileName + ' -c -SlocalHost -Usa -P'+ ''' '

SELECT @SQL

EXEC(@SQL)



 
 i got an error saying

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'au_fname'.
 
i could no tfigure it out my mind window is closed.can any one help me put to find a bug.
 
 
many many thanks

View Replies !   View Related
Generate A Strong Name Key File
I am a Newbie to programming and databases and would like to create a simple program for doing full text searching on a SQL database. 
 
I have downloaded and installed Visual Basic 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express.  I have watched several hours of video tutorials and done numerous tutorials. I feel I am making progress and having a lot of fun.  My machine is running XP home edition.
 
I have found the following resource that looks like a good place for me to start with creating a full text search program:
 
€œSQL Server 2005 Books Online 
Item Finder Sample 
http://msdn2.microsoft.com/en-us/library/ms160844.aspx
 
However I am stuck at the beginning with trying to €œgenerate a strong name key file.€? I don€™t know how to €œopen a command prompt€? (I did confess to being a Newbie!!).    I searched the database for this forum and found a relevant thread stating that €œSQLCMD is the command-line utility for SQL Server and is included with SQL Server 2005 Express Edition.€?  I found the SQLCMD program on my computer but when I opened it, it does appear to provide the options listed below in the tutorial (i.e., €œclick start,€? €œPoint to all programs,€? etc.).
 
Initially here is what I need based on the first section of the tutorial:
 
Building the Sample
 
If you have not already created a strong name key file, generate the key file using the following instructions.
 
To generate a strong name key file
 
Open a Microsoft Visual Studio 2005 command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
 
-- or --
 
Open a Microsoft .NET Framework command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
 
Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.
Note:
To determine the folder where samples are located, click theStartbutton, point toAll Programs, point toMicrosoft SQL Server 2005, point toDocumentation and Tutorials, and then clickSamples Directory. If the default installation location was used, the samples are located in <system_drive>:Program FilesMicrosoft SQL Server90Samples.
 
At the command prompt, run the following command to generate the key file:
sn -k SampleKey.snk
 
Important:
For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
 
How do I complete this initial step of generating a strong name key file for the tutorial?  I guess I am missing something simple and once I get past this I will be able to complete the tutorial.  Any help will be greatly appreciated.
 
 
 

View Replies !   View Related
What Is The Best Way To Combine Two Text File Into One Text File In SSIS
 

Hi,
 
I am looking for a way to combine two text files into one file. I am thinking of using a batch file (DOS command ) to do it. Any suggestion please?
 

View Replies !   View Related
Generate A Xml File From Sql Server 2000?
hi there.
i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?
generate xml and cache it and use the xml file rather going to sql server database?
has anybody have any help on this?
the steps invloved:
1) first generate a xml file from table something like this:
select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?SELECT * FROM dbo.LOOK_UP  FOR XML RAW, ELEMENTS  ?SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?
which one should i use and how do i access after i gnerate a xml file
 
thanks.
 

View Replies !   View Related
Generate XML File From Stored Procedure
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 Replies !   View Related
How To Generate .sql File Out Of SQL Server Project?
Hi,
 

We have certain CLR stored procedures written using Visual Studio 2005 Team Suite. Most simple method of deploying CLR procedures is to right click on sql server project and select deploy.
 
We use another method as explained here:
We have team project file called TFSBuild.proj. We check-in the code changes and run a build which takes around 20 mins. This build then creates xyz.sql file with machine codes within it. We simply copy-paste the contents of this file and deploy it on production sql server. In order to generate sql file out of sql server project we have following code written inside TFSBuild.proj.
 
<ClrSqlDeploy AssemblyPath="$(SolutionRoot)in" AssemblyName="xyz.dll" ScriptOutputName="xyz.sql" ScriptOutputPath="$(SolutionRoot)folder1" />
 
I wanted to know which commands (at command prompt) will enable me to generate .sql file out of sql server project without using visual studio 2005 or team suite's build facility?

View Replies !   View Related
Flat File To Generate UTF8
 

By changing the properties of Flat-file connection I am able to create file with encoding as utf16 or western-European, but not UTF8. What may I be missing?
 
The code page is set to '65001' for UTF8 and when we uncheck the UNICODE check box then the file generated is "Western European" and If UNICODE checkbox is checked then the encoding is UTF16. 
 
Please help me to generate flatfile with encoding as UTF8.
 
 

View Replies !   View Related
Generate File Task With 2 Files
Hi Guys,
 
I've one Dafta flow task where I'm getting data from OleDb source and then doing some scripting using script component and then generating a file.
Now I would need to get the same data and apply some different things and generate another file.
Can I used this same task for doing the secondry work? If yes how woulld I put the thing in place, I would need to get the same data but I would need to use a seperate scripting and generate a seperate file?
 
TA
Gemma

View Replies !   View Related
SSRS: Generate Report Without RDL File
Hi all the good people here,

Can I generate a report without having RDL file physically at the server?

According to any "Generic Report Builder" logic, the report builder must stores the properties of Report in one form (RDL/XML) or the other (in database) and at the time of generating the "Report Output", builder must be reading those properties, assigning those to any class object's (say objRPT)  properties/members and calling the method to generate the report in perticular file format.

I need a way  where I can assign these values stored in the database (i.e. meta data of report is stored in database) to the objRPT directly. Can I do that.

I need this because I want to see whether I can switch from "Active Reports for .Net" to "SSRS" or not. I store all the meta data of the report into the database (including its control information, parameter mapping etc.) I have used Active Report's Events as well to show/hide controls/sections conditionally.

So in short as the metadata is in predefined-database, i don't want to bother with the RDL files. Can I do that?

Please help me with this.

View Replies !   View Related
Generate Excel-html File From SQL SERVER
Generate an html-excel file with any table. Does not need Excel to generate , because does not use Excel automation.

http://www.databasejournal.com/scripts/article.php/3300831

View Replies !   View Related
Excel Vba To Generate Flat File Definition
What object do I reference to use SSIS from Excel.  I want to generate a flat file definition based on Excel.  I have a lot of fields to import and I don't feel like creating them as flat file columns.  I have a few tables and I get the source file format from the vendor in an Excel format.  What I would like to do is generate a flat file connection in an empty package using VBA.

View Replies !   View Related
Can We Extract Data From A XL File To Generate A Sql Report
 

Hi All,
 

can we extract data from a XL file to generate a sql report?
 
Thanks
 
 

View Replies !   View Related
Programmatically Generate Rdl Files With Image File
Hello,
 
 I would like programmatically generate rdl files with image.
 
I am using this XML Text Writer to create the rdl file.
 
But i have no idea how can i carryon on this task.
 
Can anyone help me in this topic?
 
Thanks.
 

View Replies !   View Related
Generate Flat File Via Stored Procedure
I have a need to do the following:

Generate a Stored Procedure and have the output written in a csv format.

I have everything I need to capture the data via stored procedure, but I am lost on a way to 'INSERT' the data values into a csv file.

This stored procedure will be triggered by another application.

Could someone please help.

thanks

View Replies !   View Related
Error While Converting Oracle Timestamp To Sql Server Timestamp (datetime) - &&"Invalid Date Format&&"
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description:
 "Invalid date format".

 

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

 

 




To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

 

I don't know if my code is right . Please inform, how i can achieve this.

View Replies !   View Related
Generate SQL Scripts Creating A Separate File Per Object
Hello all,
What is an easy way of scripting all the database tables and sprocs automatically to a separate file each one? I can't find this option in SQL Server Management Studio.
Or is any other way of uploading all objects to a Visual Source Safe project that does not require to copy and paste each script into a VSS sql script?
Thanks!
David

View Replies !   View Related
Unable To Rename File Append With Timestamp While Downloading Exported Data Into Excel/pdf In SQL Server 2005 Reporting Service.
hi fiends,
Can any one help me on this issue please. I am new to this SQL Reporting service.

I am Using SQL Sever 2005 , windows 2000 professional.
Not able to rename the file , when i export the data into excel/pdf format. I tried with Subscription properties. It's giving the RDL name only.
My requirement is need to display filename appending with timestamp.
 
Steps:
1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp)
Can you please help me.
 
 

regards
bhushanam

View Replies !   View Related
SQL Server To Generate XML Instead Of Redendant Query..file Open During Write Problem Or Job Not Running...
Here's a challenge that is killing me:I've got 2 web servers and a SQL Server and about 5,000 'users' whostay connected to the site all day. I have a page that is supposed tobe 'real-time', so to do this, I have a 1px frame that refreshes every15 seconds (so the other frame doesn't have to reload all the time--thetop only reloads when a new record or a changed record hits the db).The real time data can be filtered in about 8 different ways.Currently, I have each user querying a table that contains 1 record,including the max ID and the most current insert/update posting date.The browser contains a cookie with that date. When the browser receivesnotification that there is some new info on the server, it refreshesthe top page and reloads the data. This is happening for all users. So,I thought to eliminate the 5,000 users running the same (or closevariations) of the same query each time a records is inserted/updated,that I would generate an XML file with the current day's data.In a dev environment this works 'ok'. I'm doing this by running anActiveX job on the SQL Server that calls a stored proc (FOR XML) andwrites the content to a file. Then from the web servers, I'm queryingthis file for the new timestamp and then if newer than the cookie,grabbing the XML (using the httprequest in the ASP XMLDOM) and usingXSLT to transform the data instead of parameterizing the queries.Theoretically I love this solution. Problems happen in a LIVEenvironment where either the file is being written to or the job isn'table to run. When 2 records are trying to be written within the samesecond, the file isn't being written (or maybe that the http requestingthe XML is keeping the file locked?)....anyway...this is a HUGE problethat I can't seem to solve. Once we roll to .NET I think storing thedataset in cache and updating cache (still don't know how I'll triggerthat without each user checking the db)....Long winded, sorry...help?

View Replies !   View Related
Inserting Text String Into Text File From DTS
I am currently using a DTS package to extractexport a SQLserver view to a fixed width textfile.
My problem is that now I need to insert the date and and some other characters into the first line of the text file.
The reasons for this are irrelevant, but its so another application can identify the file before it loads it.
Somebody please help! I'm having a tought time finding a GOOD way to do this. I was hoping DTS has the ability, because I need to make this process manageable.
Thanks.

View Replies !   View Related
For Loop - Iterate From Older Files To Newer Files Based On File's Timestamp
In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
 
Any Suggestions?

View Replies !   View Related
SQL Server 2005 Notification (Text File Not Updating Using File Channel Protocol)
Hi Alls!

I m new to SQL Notification Services. I m trying out an example as mentioned on the following article.

http://www.devx.com/dbzone/Article/28522

I created an Notification Application using nsControl.exe in SQL 2K5 which is based on the File Channel Protocol. The scenerio is. When we add an XML file in the watched directory. A text file in my case "Notification.txt" should be updated.

What i m getting at the end is:

My Notification Table is getting updated, but it seems like the distributor is not storing the information in the text file.

One thing more, I m using the main notification tables rather than views in my Event rule.

The following are my ICF and ADF Files.

Instance Configuration File

<?xml version="1.0" encoding="utf-8" ?>
<NotificationServicesInstance xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ConfigurationFileSchema">
<InstanceName>DM</InstanceName>
<SqlServerSystem>
WFW050
</SqlServerSystem>
<Applications>
<Application>
<ApplicationName>StockPrice</ApplicationName>
<BaseDirectoryPath>C:WorkingNotificationServicesSample</BaseDirectoryPath>
<ApplicationDefinitionFilePath>appADF.xml</ApplicationDefinitionFilePath>
</Application>
</Applications>
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>
FileChannel
</DeliveryChannelName>
<ProtocolName>
File
</ProtocolName>
<Arguments>
<Argument><Name>FileName</Name>
<Value>C:ResultsStockNotification.txt</Value>
</Argument>
</Arguments>
</DeliveryChannel>
</DeliveryChannels>
</NotificationServicesInstance>

Application Definition File

<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ApplicationDefinitionFileSchema">
<EventClasses>
<EventClass>
<EventClassName>StockEvt</EventClassName>
<Schema>
<Field>
<FieldName>
StockCode
</FieldName>
<FieldType>
varchar(10)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
ExchangeCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
Price
</FieldName>
<FieldType>
decimal(18,5)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
</Schema>
</EventClass>
</EventClasses>

<Providers>
<HostedProvider>
<ProviderName> StockEP </ProviderName>
<ClassName> FileSystemWatcherProvider </ClassName>
<SystemName> WFW050 </SystemName>
<Arguments>
<Argument>
<Name>WatchDirectory</Name>
<Value>C:Events</Value>
</Argument>
<Argument>
<Name>SchemaFile</Name>
<Value>C:WorkingNotificationServicesSampleSchema.xsd</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>StockEvt</Value>
</Argument>
</Arguments>
</HostedProvider>
</Providers>

<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>
StockPriceSubscriptions
</SubscriptionClassName>
<Schema>
<Field>
<FieldName>
DeviceName
</FieldName>
<FieldType>
varchar(255)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
SubLocale
</FieldName>
<FieldType>
varchar(10)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
StockCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
ExchangeCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
<Field>
<FieldName>
TriggerVal
</FieldName>
<FieldType>
decimal(18,5)
</FieldType>
<FieldTypeMods>
not null
</FieldTypeMods>
</Field>
</Schema>
<EventRules>
<EventRule>
<RuleName>EvtRule</RuleName>
<Action>
INSERT INTO NSStockPriceNotificationsNotifications(DeliverySta tusCode,NotificationBatchId,SubscriberId, DeviceName, SubscriberLocale, StockCode, ExchangeCode, Price) SELECT 0,1,s.SubscriberId, s.DeviceName, s.SubLocale, e.StockCode, e.ExchangeCode, e.Price FROM NSStockEvtEvents e, NSStockPriceSubscriptionsSubscriptions s WHERE e.ExchangeCode = s.ExchangeCode AND e.StockCode = s.StockCode AND e.Price &gt; s.TriggerVal
</Action>
<EventClassName>StockEvt</EventClassName>
</EventRule>
</EventRules>
</SubscriptionClass>
</SubscriptionClasses>


<NotificationClasses>
<NotificationClass>
<NotificationClassName>
StockPriceNotifications
</NotificationClassName>
<Schema>
<Fields>
<Field>
<FieldName>
StockCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
</Field>
<Field>
<FieldName>
ExchangeCode
</FieldName>
<FieldType>
varchar(15)
</FieldType>
</Field>
<Field>
<FieldName>
Price
</FieldName>
<FieldType>
decimal(18,5)
</FieldType>
</Field>
</Fields>
</Schema>
<ContentFormatter>
<ClassName> XsltFormatter </ClassName>
<Arguments>
<Argument>
<Name> XsltBaseDirectoryPath </Name>
<Value> C:Stylesheets </Value>
</Argument>
<Argument>
<Name> XsltFileName </Name>
<Value> Tansformation.xslt </Value>
</Argument>
</Arguments>
</ContentFormatter>

<Protocols>
<Protocol>
<ProtocolName>
File
</ProtocolName>
</Protocol>
</Protocols>
<ExpirationAge>
PT2H
</ExpirationAge>
</NotificationClass>
</NotificationClasses>

<Generator>
<SystemName>WFW050</SystemName>
</Generator>

<Distributors>
<Distributor>
<SystemName>WFW050</SystemName>
</Distributor>
</Distributors>
</Application>


Can you ever comeup with such problem. Please do let me know

Thanks

R.A.F

View Replies !   View Related
XML Format File For Bulk Insert Of Text File With Fixed Length Columns
Hey All,

Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.

I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:




Example of Data

0124015Apple Crate          32.12

0124016Bananna Box          12.56

0124017Mango Carton         15.98

0124018Seedless Watermelon  42.98
My Table would then have:
ProductID as int
Name as text
Cost as money

How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton         " --> "Mango Carton")?

I don't know if it makes any difference, but I've been calling SQL from my code by doing this:




Code in C# Form

            SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
            SqlCommand cmd = new SqlCommand();
            
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
            cmd.Connection = SqlConnection;

            SqlConnection.Open();
            cmd.ExecuteNonQuery();
            SqlConnection.Close();
            RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.



Thanks for your time,


Hayden.

View Replies !   View Related
Read Text File From Flat File Connection Manager SSIS
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
 
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{
 
//Some code to read file and write it into new file

return DTSExecResult.Success;

}
 

public const string Property_Task = "CustomErrorControl";

public const string Property_SourceConnection = "SourceConnection";

 

public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)

{

if (node.Name != Property_Task)

{

throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));

}

else

{

try

{



_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;



}

catch (Exception ex)

{

infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);

}

}

}

public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)

{

try

{

// // Create Task Element

XmlElement taskElement = doc.CreateElement("", Property_Task, "");

doc.AppendChild(taskElement);

// // Save source FileConnection

XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);

sourcefileAttribute.Value = _sourceConnectionId;

taskElement.Attributes.Append(sourcefileAttribute);

}

catch (Exception ex)

{

infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);

}

}
 
In UI Class there is OK Click event.

private void btnOK_Click(object sender, EventArgs e)

{

try

{

 

_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);

btnOK.DialogResult = DialogResult.OK;

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

#endregion

}

View Replies !   View Related
Export Stored Procedure To Flat File And Add Aggregate To End Of The Text File?
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 Replies !   View Related
How Do I Write The Content Of A Query Or Table To A Text File/binary File In A Extended Stored Proce
Hi everyone,

How do I write the content of a query or table to a text file/binary in an extended stored procedure?

Thanks in advance....
cobenho

View Replies !   View Related
BCP Import Text File With Blank Line At End Of File
I am trying to bcp a text file that is written out of a Javaapplication. The text file has important order information that I needto bcp into a SQL Server 2000 database. The problem is that whenrecords are written out by the Java application the last item in eachrecord has a new line character attached to it so that a new line isstarted for the next record. BCP gives me an error and doesn't importanything if there is this blank line at the end of my text file. If Idelete the last blank line then my text file imports without anyissues. Anyone have any ideas how I can fix this issue?Thanks,Eric

View Replies !   View Related
Get Row Timestamp With No Timestamp Column
For starters, please feel free to move this if it is in the wrong forum.

 

The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?

View Replies !   View Related
Error &&"Msg 6522, Level 16, State 1&&" Receives When Call The Assembly From Store Procedure To Create A Text File And To Write Text
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below

I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class

2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:

CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]

4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'

5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)

View Replies !   View Related
Put A Text File To Numeric And Date File
How can I put a char value to numeric field and date field ?
thank's

View Replies !   View Related
How Can Unzip File Text File Using SSIS
 

Hi,

I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved