Hi..I am trying to read text file using OpenRowSet function for this my DBA was created linked Server with Name "DBLServerText", my text file path c:/txtcount.txt. Please guide me syntax any one have idea.
I don't have access on XP_CMDSHELL & BULK Insert commands.
DTS package is also not allowing.
as per my knowledge, I have only one option that is OpenRowset.
Please help me the syntax using with Linked Server. with Linked it is showing the error. " You don't have OLEDB access use with Linked server"
Well, i need a piece of code that return me the schema rowset of a desicion tree and let me iterate me on it. I need some columns like node_type, node_unique_name.... etc.. And another question is that the node_distribution has multiple rows of information. How can i access them. I think there must be a adomddatareader object to iterate on them..
Please let me know if I am on the right track here.
I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.
I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.
Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection > Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).
I'd like the "OUTPUT DELETED.* INTO target table" resulting from a DELETE to occur in the same order as the clustering key of the target table. Is this possible?
I am using a proc that runs fine in SSMS Query editor and also produces rows when I click the Preview button in the OLEDB Source Adapter. It has two input parameters. The proc contains some CTE's and UNION ALL's. It does not contain any dynamic sql. However, when I run the package I get this error:
[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
How can that be when the Preview shows rows being returned?
I get the following error. Err.Description - "Rowset does not support fetching backward." Err. Number - -2147217884
Can somebody tell me the reason behind this error.
My Reasearch: I have got to know that I get this error in the line of code recordset.MoveLast in My code.
My Code environment: I have built an SQL Query string which calls the Strored procedure with one parameter (parameter tyoe is string type). I have attached the SP code below.
and SP contains the call for querying other database in the same server using OPENQUERY command.
and the VB code contains code as Recordset.MoveLast. (Error is generated here and Recordset.RecordCount is also -1)
AS BEGIN DECLARE @sSQL VARCHAR (1000) SET @sSQL = '''SELECT * FROM VW_JOKE WHERE JOKE_ID = ''' + '''' + @sRMS_Status + '''' + ''' ORDER BY JOKE_NO'' EXEC ( 'SELECT * FROM OPENQUERY(lnk_joke__cat,' + @sSQL + ')' ) END
My Questions: 1). Is there any problem in SP as I am using OpenQuery? I queries the SP and concluded that it generates result properly.
2). Is there any problem with the Recordset.MoveLast line? I am using adOpenKeyset, adLockReadOnly as parameters to my Recordset.Open command along with other parameters.
Let me know the corrective steps to be taken to get rid of this error.
Thanks in Advance for your valuable time Ranjan Jain
I see others having this problem when trying to run a query across a link from 2005 to a 2000 server. But we are already running SP4 on our 2000 server. What gives?
OLE DB provider "SQLNCLI" for linked server "s-1" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "s-1" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 5
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "s-1". The provider supports the interface, but returns a failure code when it is used.
My goal is to have a file (it comes from another system) that I want to import via an OPENROWSET style query.
The query would look like this:
select [NoName] from openrowset('MSDASQL' ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DefaultDir=c:filedir' ,'select * from "file.lst"')
If I make the file a .csv it works fine. However, if it has a not CSV or TXT extension it throws the following error and cannot seem to find a solution to it.
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
In addition, (although I can probably find this elsewhere), I need to have the first line 'BLANK' so that it does not miss data (there is no header row). Is there a way to use OPENROWSET without BULK to basically include all rows as data?
I have a strange problem with an OleDB call to a stored procedure thatreturns a rowset.Only the first time I execute the query, after I restart SqlServer, myprogram crashes becausethe rowset is empty. All next calls (after restarting my program, ofcourse) run successfully.The context is:1) if I do not bind the output rowset, my program doesn't crash2) If I run the call without the binding and then I run the call withthe binding, it doesn't crash3) It is not the first query I do in my session4) the call crashes only if in the SP there are some inserts: it is awell known problem with OleDB, but in manyother cases I have fixed it setting NOCOUNT to ON
I am getting the following error when I execute my sql task:
An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "
I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes. I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:
In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.
So I am not sure what I am missing here. Anyone have any suggestions?
I'm trying to store the output of my stored procedure into a temp table, which takes Table Type as Input.
CREATE TYPE [dbo].[Employee] AS TABLE( [Field] [varchar](50) NULL, [Criteria] [varchar](50) NULL, [Value] [varchar](50) NULL ) declare @Employee dbo.Employee insert into @Employee values(N'Salary',N'is greater than',N'3520')
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'',''Server=localhost;Trusted_Connection=yes;'',''EXEC dbo.uspGetEmployee ' + @Employee + ')'
I need to create a stored procedure which uses output of the above stored procedure. Hence, I don't want to put declare and insert into OPENROWSET as I get those values as parameter to the new procedure.
I have a dataflow task. On which I have OLEDB as my source. I connect to my database and execute a stored proc. the stored proc results in a result set with only one row and two columns. First Column is an integer and the second row is a varchar(max) with xml script in it. Not that it should matter because it is in varchar(max).
Anyway, it give me an error
[OLE DB Source [321]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
What am I doing wrong?
Can I not have a stroed proc that returns a result set as my data source?
i have recently ported an old asp.net web application using crystal reports 9 from windows server 2003 to windows server 2008 . the crystal reports smoothly at first but one of the reports stopped working when the admin changed his password now the report is showing error. Failed to open a rowset
i got a error [OLE DB Destination [16]] Error: Failed to open a fastload rowset for "[dbo].[tempMaster]". Check that the object exists in the database.
i am creating and doping this table in beginning after insert/update i will drop this table but this is error.i am using sql server 2008R2
My problem is this: There can be a variable number of line elements (between 2 and 5), and in addition each element tag is identical to the others, i.e. Line.
I am trying to populate a SQL Server 2000 table (which contains the columns AddressLine1, AddressLine2, etc.) using OpenXML
Hi,My application writes data into sql server.Currently it converts data into XML (an in memory XML string) and writeusing OPENXML.I want to know if i write it to a csv file and use BCP, then will it befaster then OPENXML. (i feel, writing to a csv will create IO operation thatwill slow down the process).Thanks
I have a SP set up to take an input param and then fire the followingsp_xml_preparedocumentOPENXML (With a select statement)sp_xml_removedocumentThis works fine with the XML syntax that microsoft provides in itsdocumentation ...<ROOT><Customer><CustomerID>VINET</CustomerID><ContactName>Paul Henriot</ContactName><Order OrderID="10248" CustomerID="VINET" EmployeeID="5"OrderDate="1996-07-04T00:00:00"><OrderDetail ProductID="11" Quantity="12"/><OrderDetail ProductID="42" Quantity="10"/></Order></Customer><Customer><CustomerID>LILAS</CustomerID><ContactName>Carlos Gonzlez</ContactName><Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"OrderDate="1996-08-16T00:00:00"><OrderDetail ProductID="72" Quantity="3"/></Order></Customer></ROOT>The problem I am having is parsing XML data given to me in this format<?xml version="1.0" standalone="yes"?><Active_x0020_Directory_x0020_Users><Groups_Duz_x0060_mp><whenCreated>2/13/2004 7:13:21 PM</whenCreated><whenChanged>2/13/2004 7:13:21 PM</whenChanged><sAMAccountname>!WGSyEnBuCoBuDoCG</sAMAccountname><cn>!WGSyEnBuCoBuDoCG</cn><groupType>-2147483646</groupType></Groups_Duz_x0060_mp><Groups_Duz_x0060_mp><whenCreated>2/13/2004 7:12:04 PM</whenCreated><whenChanged>2/13/2004 7:12:04 PM</whenChanged><sAMAccountname>#11SeanTest</sAMAccountname><cn>#11SeanTest</cn><groupType>8</groupType></Groups_Duz_x0060_mp></Active_x0020_Directory_x0020_Users>As you can see the elements are defined differently, I have an XSLfile as follows<?xml version="1.0" standalone="yes"?><xs:schema id="Active_x0020_Directory_x0020_Users" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="Active_x0020_Directory_x0020_Users"msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Groups_Duz_x0060_mp"><xs:complexType><xs:sequence><xs:element name="whenCreated" type="xs:string"minOccurs="0" /><xs:element name="whenChanged" type="xs:string"minOccurs="0" /><xs:element name="sAMAccountname" type="xs:string"minOccurs="0" /><xs:element name="cn" type="xs:string" minOccurs="0" /><xs:element name="groupType" type="xs:string"minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema>So now I am wondering how I tell SQL Server to use the new file formatfor XML?Thanks in advance for your help.
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned DECLARE @memberList VARCHAR(4000),@hDoc INT SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT) EXEC sp_xml_removedocument @hDoc
Hello everyone. I am new to.Net and here is what I have to do. I needto update a SQL table with data coming from a XML file. I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure. The XML contains about 12 000 records, kind of phonebook info (name, email, phone). What would be the best approach to do this? What objects should I use? Thanks a million, Ben
Im trying to update a row that all collumns are required (can not be null). Let say if i want to remove "No2" from the insert command but by default i want to make sure that in my collumn "No2" in the table of my database i have zero into it.
================= with tbl_xmltest ================= My problem is on this line above. When i remove in my XML column No2 i got an error.. "'OpenXML'. This column cannot be NUL". Is there a way to use my sql-command
================================ declare @data nvarchar(4000) declare @handle int
--select top 1 * from tbl_xmltest for xml auto set @data = '<tbl_x005F_xmltest No1="1111" No2="234" No3="3333"/>'
exec sp_xml_preparedocument @handle output, @data
begin tran select * from tbl_xmltest
insert into tbl_xmltest select * from openxml(@handle, '//tbl_x005F_xmltest') with tbl_xmltest
select * from tbl_xmltest rollback EXEC sp_xml_removedocument @handle
Hello:I am leaning to use OPENXML in my queries, but I keep encountering problems:This is the code:========================================================DECLARE @xml_text VARCHAR(200)DECLARE @i INT SET @xml_text = '<ROOT><CODE>HAMHE</CODE><CODE>MENHE</CODE><CODE>REGCA</CODE><CODE>SLAGI</CODE></ROOT>' EXEC sp_xml_preparedocument @i OUTPUT, @xml_text SELECT * FROM OPENXML(@i, '/ROOT/CODE', 2) WITH CODE EXEC sp_xml_removedocument @i====================================================all I want is to return a return a result like this:HAMHEMENHEREGCASLAGIbut it keeps giving me error: "Server: Msg 208, Level 16, State 98, Line 129Invalid object name 'CODE'."Could anybody help me pls? thank you
Hello, I am using OpenXML to do inserts/updates on Sql Server. I have a problem with data type, for example, check this example: DECLARE @record VARCHAR(1000) SELECT @record = '<tests> <test> <NumberofChildren></NumberofChildren> <Name></Name> <Active></Active> </test></tests>' DECLARE @IndexInXML INT -- Create an internal representation of the XML document EXEC sp_xml_preparedocument @IndexInXML OUTPUT, @record INSERT INTO [test] SELECT [NumberofChildren], [Name], ISNULL([Active],null) FROM OPENXML (@IndexInXML, '/tests/test',2) WITH ( [NumberofChildren] INT, [Name] VARCHAR(50), [Active] BIT ) -- Remove in-memory table from memory EXEC sp_xml_removedocument @IndexInXML I have a table called test, it has the followig fields:1- ID2- NumberofChildren3- Name4- ActiveAll fields but ID are asisgned "Allow Null"I want to add data to this table, but if the value coming from xml is empty, I want to have null, in NumberofChildren, Name, Active.When I try my script, if the data is null, NumberofChildren with 0 value, Name is nothing, Active is 0,How can I make the INT and BIT datatypes have NULL when the input is empty in the xml? Can you helpthank you
When I use 'OPENXML' in SQL there is an error message appear, that is: 'XML parsing error: below tag has not been closed: NewDataSet.', is it related to my using LOOP method? how can I solve this problem? Here my SQL query for your reference: ''select @myn=UserName FROM OPENXML (@idoc, '/NewDataSet/Table',2) WITH (UserName varchar(20))"
I have an text column in a table that was intended to store XML data about queries that users submitted in our database. The problem appears that the application inserting records into the table doesn't do a great job of formatting the XML and I am having a great deal of trouble getting workable results.
The XML data in the table looks like: <request> <name>LastName</name> <oper>=</oper> <value>Smith</value> <name>FirstName</name> <oper>=</oper> <value>John</value> <name>MiddleName</name> <oper>=</oper> <value>Q.</value> </request>
Note the groups of three elements (name, oper, value). These always appear sequentially in the data and there are always a fixed number of elements. If an element has no data, then it contains the value 'null' (ie, <value>null</value>).
I want to get a result like:
Col1 Col2 Col3 ======== ====== ======== LastName = Smith FirstName = John MiddleName = Q
When I execute OPENXML, I specify option 2 (element centric) and the following WITH clause:
WITH (name varchar(10), oper varchar(10), value varchar(50) )
I get a single row returned. I have also tried:
WITH (name varchar(10) '@name', oper varchar(10) '@oper', value varchar(50) '@value')
I get a single row returned (with null values).
I've tried lots of other permutations, but nothing so far has worked. The only thing that has been modestly successful is to create and Edge Table (exclude the WITH clause), but that's really hard for me to work with. Another option is to insert a <line num="#">...<line> around each group of three elements (name, oper, value). That worked well, but it's a pain to implement (it means going back and updating all rows where the XML data exists and inserting the proper data.
I am by no means an XML guru, so if anyone has a suggestion, I'd love to hear it.
I don't know if anyone can help me but I have two issues with SQL Server 2000 SP4 (version 8.00.2039 - Desktop Engine) running on W2K and W2K3. I'm also running SQLXML 3.0 (msxml2.dll version is 8.30.9530.0).
Is it me or is sp_xml_preparedocument a crippled fat dog that is blind?...not that I have anything against crippled fat dogs that are blind :)
In all the stored procs I have developed, I pass a text var as an input parameter and return an IStream to ADO (using an sqlxml provider) in COM+. All has been very well and fine...until the passed text parameter resembles a data object of any decent size.
The first error I was noticing was a "XML Parsing Error: not enough storage is available to complete this operation". Well, I thought I would debug logically in a step fashion and just prepare the doc first and then do a return and then do a return on the next segment of code to find out where the issue is. I was amazed to find that sp_xml_preparedocument is taking 7 seconds to load a simple 1MByte text input var and around three minutes to load a 7 MByte file.
I believe these long load times are causing issues with transaction timouts etc so I thought I would try to solve the speed issue with sp_xml_preparedocument and then see if the "XML Parsing Error" continues.
So, my first question is:
Should sp_xml_preparedocument take 7 seconds to load a 1MByte text variable and nearly three odd minutes to load a 7 MByte file? Surely there is something wrong somewhere?
I'm also running these tests on two machines - one is 2 GHz and the other is 2.4 GHz P4's.
I have an variable size XML file currently +/- 5 MB(8000+ records) but may be bigger. I need to INSERT/UPDATE each row. Which is the better way of doing this? Using selectNodes on the XML and INSERT/UPDATE each row via a SP or using sp_xml_preparedocument and OPENXML therefore only calling the SP once?
I've got thousands of XML docs and have to import those to the DB. AndI am having problem with getting some values because of tricky XMLformat.I didn't create this XML format and I don't like this format, OOP snobdid. And I(DBA) have to look after their mess.I've got XML doc like this :<customer><customer_id>12345</customer_id><first_name>Jason</first_name><last_name>Varitek</last_name><location><city>Boston</city><state>MA</state></location></customer>First, I did following :DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<customer><customer_id>12345</customer_id><first_name>Jason</first_name><last_name>Varitek</last_name><location><city>Boston</city><state>MA</state></location></customer>'EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM OPENXML (@idoc, '/customer',2)WITH (customer_id int,first_name varchar(50),last_name varchar(50),location varchar(50))Then returned this which is not bad.customer_id | first_name |last_name |location12345 | Jason | Varitek |Boston MABut I need to return city and state in separated columns(I wish OOPsnobs made these as attributes in location element, instead ofelements). And I tried following and returned everything NULL.DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<customer><customer_id>12345</customer_id><first_name>Jason</first_name><last_name>Varitek</last_name><location><city>Boston</city><state>MA</state></location></customer>'EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM OPENXML (@idoc, '/customer/customer_id',2)WITH (customer_id int '../@customer_id',first_name varchar(50) '../@first_name',last_name varchar(50) '../@last_name',city varchar(50) '@city',state varchar(50) '@state')Does anybody have any idea to get value of city & state elements inseparated columns?ThanksJimmy
