|
Thanks....
My question is I need to make a stored procedure for testing use. The pseudo-code is:
BEGIN
Open input file for reading
Create new output file REJECTE.txt in the delete folder
While not EOF in input file {
For every line of the input file {
Run update dbo.ITEMS table set
ITEMS_SLS_FLG = B where ITEM_ID = A (A & B are from input file)
IF update fails because ITEM_ID is not found on dbo.ITEMS table
Write to output file "ITEM ID ITEM_ID is not found in the table"
Else IF any other error return failure
Close input file
Close output file
}
}
Close input file
Close output file
Return success
END
My question is how to open, read and write to a txt file in a SQL stored procedure? The input file has the below format:
A B
125345 1
235649 1
765430 0
...... .
if you have the experience or knowledge handling this kind of problem, please do me a favor, many thanks....
|
|
|
|
|
Hi,
Don't recall any direct file operation procedures, but could you use another approach:
- write informatical messages to a table using normal SQL
- create a stored procedure which writes to a file yourself using managed code (C#) and install that procedure to SQL Server
- use trace (not actually designed for this but...). For more info see sp_trace_create
Hope this helps you,
Mika
|
|
|
|
|
Look into SSIS packages. It might look hard to do at first, but making a package is not that hard.
|
|
|
|
|
Below is a snipet of code that I'm using to develop some XML processing.
SET @XMLSTRING = '<DATAMATRIX> <FSDATAMATRIX> <DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE> <FIELD1>UNITED STATES</FIELD1><FIELD2>BEVERAGES, ALCOHOLIC</FIELD2><FIELD3>123</FIELD3><FIELD4>FEMA</FIELD4><FIELD5>NO</FIELD5></FSDATAMATRIX></DATAMATRIX>'
PRINT @XMLSTRING
EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLSTRING
SELECT *
FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1)
EXEC sp_xml_removedocument @idoc
The problem I'm having is that I can't seem to get the values of FIELD1, FIELD2, FIELD3 by using the OPENXML procedure.
Can someone shed some light on this ? Thanks.
|
|
|
|
|
Hi,
couldn't quite reproduce the problem.
SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX/FIELD1',1)
id parentid nodetype localname prefix namespaceuri datatype prev text
4 2 1 FIELD1 NULL NULL NULL 3 NULL
9 4 3 #text NULL NULL NULL NULL UNITED STATES
Since text is independent node inside Field1 this doesn't return a single value but two rows, one for node and one for content
Mika
|
|
|
|
|
Oops. I should have stated that my code does work, but not the manner I was hoping. Yes, I got the same results you are seeing but I wanted to be able to see 1 row returned. In an ideal world, it would have showed me "FIELD1" and the value of "UNITED STATES". I'm a novice at XML processing, so please bear with me.
Is what I'm asking for possible ? If not I will have to work around it by using some logic that will link the two records together by the node type and parent id. Thanks again for the quick response.
|
|
|
|
|
No problem 
One way is to use SQL to manipulate results. For example:
SELECT set1.localname, set2.text
FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) set1
INNER JOIN
OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) set2
ON set1.id = set2.parentid
WHERE set1.LocalName = 'FIELD1'
Remember that you can define the columns you need from the xml by using WITH clause.
Of course if you can modify the XML, you could set the texts as attributes instead of elements, which could simplify your code.
Hope this helps,
Mika
|
|
|
|
|
SELECT *
FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1)
WITH ( DM_OBJECT_TYPE VARCHAR(100) 'DM_OBJECT_TYPE'
, FIELD1 VARCHAR(100) 'FIELD1'
, FIELD2 VARCHAR(100) 'FIELD2'
, FIELD3 VARCHAR(100) 'FIELD3'
, FIELD4 VARCHAR(100) 'FIELD4'
, FIELD5 VARCHAR(100) 'FIELD5'
)
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
Thank you to those who have responded, I can get the data XML results. The next problem is that I need to be able to Insert, Update and Delete XML data stored in one of my tables. The table consists of a primary key and a "TEXT_DATA" column that contains the XML data. The wrinkle here is that the TEXT_DATA column is defined as ntext.
I'm struggling with XQUERY commands to manipulate the data. Your help is greatly appreciated.
|
|
|
|
|
Hi,
Try simply casting the text to xml datatype and use the result for xml dml operations. Simple case to test that casting actually works:
SELECT CAST( CAST( '<root><node1 value="A" /></root>' as ntext) as xml) XmlTypedColumn
Mika
p.s. You could start a new thread for separate questions
|
|
|
|
|
hi all
i get above error when using this code snippet
Dim svr As Server = New Server()
Console.Writeline(svr.Name & " " & svr.Information.VersionString)
i already import the the SMO as following
Imports Microsoft.SqlServer.Management.Smo
please suggest how can i solve this problem
|
|
|
|
|
Could be the server isn't running, your connection string is not setup right, or firewall.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
hi all,
i am new to SQL Server but little strong in vb.net
i want to create Backup file of any database of SQL server and again restore them to server . So how can i do this task Programmatically. Also what is the option when Authentication mode is Windows or Server or Mix-Mode
|
|
|
|
|
You are not mentioned here SQL Server version.
If you are using SQL2005 then do some research on SQL Server Management Objects (SMO). It provides the all kind of Database management facility including backup and restore.
Parwej Ahamad
R & D: REST services with WCF
|
|
|
|
|
Windows Authentication uses active directory credentials
Mix mode will also allow standard sql server authentication.
There are pros and cons to both of these, and entire articles/books written on them. We use windows authentication for the developers and admins who need to interact with the database directly and SQL credentials for the applications to interact.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all,
i am new bie in SQL server
i want to store Database with my application on client machine . However i know i add one for setup for Database but how can get the server name of system (mean SQL server name on client machine). Reason behind this question is that i use server name in connection string as following
Dim ConnectionString As String = "Data Source=s-1\SQLEXPRESS;Initial Catalog=abc;Integrated Security=True"
i use above string .In which server name is "s-1\SQLExpress" . so how can replace this server name to client machine server name?
|
|
|
|
|
I believe this will be the name of your computer unless you can install an instance of SQL Express.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
sir,First of all , i am new bie
actually i dont know what is the configuration of client machine . so i suppose user simply add the database in their Server. and run my application . so how could i get the Server Part in following connection string
Dim ConnectionString As String = "Data Source=s-1\SQLEXPRESS;Initial Catalog=abc;Integrated Security=True"
Here server is S-1
and instance is SQLExpress. Correct me if i wrong
|
|
|
|
|
Nope you do not need correcting. I would make it a prerequisite of your install package - ask the use for the server name to use.
I'm not sure the Express version announces itself to the network like SQL server does. Even if it does, how are you going to pick between the # number of SQL instances on the network. Last I looked we have over 300 SQL server instances on ours.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
sir, i does not properly get you
Are you want to say that I add Database as Prerequiste of Installation Package and After installation of application, when user run the application first time, then it ask for Server Name and store server name in some place like text file or registry ? Please correct me
|
|
|
|
|
I think what he want to say is, your database creation progrom should ask the database information, as sql expressoin cant be reachable accross network, so you also need to change the connection string. after getting the database info you update your configuration file.
|
|
|
|
|
Mycroft Holmes wrote: over 300 SQL server instances on ours
Oh boy. 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Yeah I work for a bank, they have 4 data centers, 2 production and 2 DR. They run 2 discreet networks UAT and production. Deployment is a PITA
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: have 4 data centers, 2 production and 2 DR
Sounded like with that many instances, you are at a good sized shop.
Mycroft Holmes wrote: Deployment is a PITA
I imagine so. ![Beer | [beer]](https://www.codeproject.com/script/Forums/Images/beer.gif) helps after a tough day in any shop 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Paul Conrad wrote: you are at a good sized shop.
Yeah but I work in one department and IT hate me, I have a mandate to "get the job done" and worry about enterprise requirements later. Therefore I'm not subject to the endless meetings IT seem to require before they allow you to start coding, it also means there is no such thing as a requirements document either so it has it's drawbacks.
Thanks for the beer, I may wait as it is 9:30 on a Sunday and thats a bit early even for me!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
