Passing Table Name To The Openxml Method

Jul 8, 2007

I want to pass the table name as a variable

Declare @tbl varchar(30)
set @tbl = 'DB.dbo.Set'
declare @sql nvarchar(200)
set @sql =
'select * from OPENXML(@xmldoc,'DB.dbo.Site',2) with' + @tbl + ''
exec(@sql)

Please help me with the correct syntax.
Mayank

View 1 Replies


ADVERTISEMENT

Passing An IN (a, B, C) List To A Sproc As A String -- Best Method?

Jul 20, 2005

I want to do something like this in a stored proc:------Create Procedure dbo.GetPatients@PatientIdList varchar(200) -- comma separated list of PatientIDsAsSelect *From PatientsWhere PatientId In (@PatientIdList)------I know the above won't work, but of course what I want is if@PatientIdList = '1,2,3' then I want Patient records with PatientIds1, 2, and 3 returned.It looks like the only way to do this is to build the SQL statement asa string within the stored procedure ... which pretty much defeats theusefulness of using precompiled sprocs as I understand it (better offbuilding a dynamic query against a View in that case).Thoughts?Joel Thornton ~ <groups@joelpt.eml.cc>

View 1 Replies View Related

Large Table-Table Partition, View Or Other Method?

Aug 27, 2007

Hi everyone,

I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?

Can you please give some suggestions? It will be very helpful if you can post some references or examples.

Thank you!

View 12 Replies View Related

Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method

May 10, 2007

Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString)    {      //call stored procedure  } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.  

View 3 Replies View Related

[Table Partitioning] What Is The Best Method?

Oct 11, 2006

Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Le

View 2 Replies View Related

Table FillSchema Method

Oct 21, 2006

Hi,

Sorry if these is not the right forum for this:

I€™m using the data adapter method FillSchema works fine in MS Access but SQL Server Express Edition it doesn€™t get the primary key of the table. For example if I try to use the Find method of the dataset I get this error €śTable doesn't have a primary key€? but in MS Access these do not happen.

This is the code that I€™m using:
Dim sql As String = "SELECT admin.rents.id, admin.rents.transaction_id, admin.rents.item_id, admin.rents.description, admin.rents.due_date, admin.rents.return_date, "
sql &= "admin.rents.amount_to_pay, admin.rents.paid_amount, admin.rents.tax, admin.rents.type, admin.rents.deposit_id "
sql &= "FROM admin.rents INNER JOIN "
sql &= "admin.transactions ON admin.rents.transaction_id = admin.transactions.id "
sql &= "WHERE admin.transactions.customer_id=?; "

RentsDA = New OdbcDataAdapter(sql, cn)
With RentsDA.SelectCommand
.Parameters.Add(New OdbcParameter("@customer_id", OdbcType.Int)).Value = TextBox1.Text.Trim
End With
RentsDA.FillSchema(ds, SchemaType.Source, "Rents")
RentsDA.Fill(ds, "Rents")

It's because of the joins?
I need to configure something in the sql server?
What is the problem?

View 1 Replies View Related

Quickest Method Of Updating A Table

Feb 9, 2007

I have a DataSet which I am holding in .NET and I would like to know the quickest way to get the DataSet in to a Table on SQL Server?
Any sample code would be great.

View 6 Replies View Related

How To Use Inner Join In Update Method For 1 Table

Aug 23, 2007

i need to use inner join while updating..
im using only one table..


rajesh

View 2 Replies View Related

Update Method Is Not Finding A Nongeneric Method!!! Please Help

Jan 29, 2008

Hi,
 I just have a Dataset with my tables and thats it
 I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
 Please help if anyone has a solution
 
Thanks

View 7 Replies View Related

Easier Method To Remove And Add A Table To Database?

Feb 22, 2000

I have a table that is corrupted and want to remove and add a backup version of it. How can i remove this table and add it again preserving all the foregin key restraints, permissions, dependencies, etc? Simply exporting and importing does not work. I could painfully remove the table and then painfully reconnect it again, recreating all the foreign key restraints, etc, by hand; but there has to be an easier way! What is the How-to?

Thank you!

Llyal

View 1 Replies View Related

Seek Method, Table-direct, And Sql Server2005

Jul 23, 2005

From what I've read in the docs, ado.net currently supports opening sqlserver ce tables in table-direct mode and performing Seek operations on them(using SqlCeDataReader), but not on the full-blown sql server. Is this(will this be) still true with ado.net 2.0 & sql server 2005?

View 11 Replies View Related

Is There A Method To Convert Select * From Table To Select Field1,field2,...fieldn From Table ?

Nov 29, 2007

Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
 
Thanks

View 1 Replies View Related

Passing Table Names To Table Valued Functions

May 25, 2008

Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?

For example, a simple stored procedure is this:

CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN

Execute('Select top 10 * from ' + @tableName + ';')

END

I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?

View 11 Replies View Related

C# Application - Method To Import Delimeted CSV To Sql Server Table

Jan 26, 2007

Hi,I'm writing a telecom billing system where it is necessary to import csv delimited files into a sql server table.
1) The csv files to import differ in column arrangement i.e SQL server table arrangement = A, B, C, D whilst CDR = - , A, -, C, D2) The csv files are delimited via different characters i.e some are del.. by commas some by semi colons
 I can import a CSV file from my application using a BULK INSERT query but only using CSV that resembles the structure of the table exactly
How can I pass a CSV to the table and state where I want each CSV column to fill a column in the SQL SERVER table
Below is how I am managing to pass a CDR to the SQL table that I have pre manufactured to resemble the structure of the SQL table Is there a way I can state which values from the CSV I want to pass to corresponding columns in the SQL table:
 
string conString = @"Provider=SQLOLEDB;Server=(local);Database=Billing;Uid=sa;Pwd=sa;";
for (int i = listBox1.Items.Count - 1; i >= 0; i--)
{
string strSQL;
OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
objConnection = new OleDbConnection(conString);
 
string cdr = listBox1.GetItemText(listBox1.Items[i]);
 
strSQL = "BULK INSERT Campaign FROM '"+ cdr +"' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '')";
OleDbDataReader objDataReader;
 
try
{
objConnection.Open();
objCmd = new OleDbCommand(strSQL, objConnection);
objCmd.ExecuteNonQuery();
objCmd = null;
objDataReader = null;
objConnection.Close();
listBox1.Items.Remove(listBox1.Items[i]);
}
catch (Exception ex)
{
objConnection.Close();
}
objCmd = null;
objConnection = null;
}
 
Regards
 Simon

View 4 Replies View Related

Method To Insert All Record From Access Table To SQL Server One

Jul 20, 2005

Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com

View 3 Replies View Related

ListChildren Method Does Not Return All Columns From Catalog Table

Dec 5, 2007

I am trying to use the ListChildren method to query the SSRS Catalog. I am using this as a dataset in a report. I'd like to see more columns, in particular the Description column, included in the dataset. Is there some way to tell ListChildren what columns should be included or is there some other method to obtain this information.
ListChildren returns ID, Name, Path, Type, CreationDate, ModifiedDate, CreatedBy, ModifiedBy, xmlns.

I'd prefer not to query the Catalog table directly.

Thanks
Tim

View 1 Replies View Related

SQL 2012 :: Method To Setup Table Replication Between Server Express?

Aug 12, 2014

Is there any method to setup table replication between you sql server express?

View 3 Replies View Related

Preferred Method To Incorporate Common Table Expression (CTE) Functionality

Feb 26, 2007

What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset?  The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and  then limiting the persisted results based on the CTE function. 

It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
 
 

 

View 1 Replies View Related

Transact SQL :: Method To Generate Insert Statements For Data In A Table

May 30, 2013

I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)

View 7 Replies View Related

SQL Server 2012 :: Set Based Method To Insert Missing Records Into Table - Right Join Not Work

Apr 24, 2014

I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)

Table A could look like the following where the multiple employees could have multiple types and multiple stages.

EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2

Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.

Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4

So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.

I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.

I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.

My count would look something like this:

EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2

So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.

The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.

View 9 Replies View Related

Passing A Table Name

Sep 3, 2004

I would like to pass the name of a table in my update query. It goes something like :

set @Table = 'TableA'

UPDATE
@Table
SET
Company_id = @Company_id


However I get a syntax error when I try to pass the table name like this.

View 1 Replies View Related

OpenXML

Nov 19, 2007

Hi

I've run into a problem using openxml and would greatly appreciate any help or direction!

The Xml document I am working with contains the following excerpt:


<Address>
<UKAddress>
<no>24</no>
<Line>A</Line>
<Line>B</Line>
<PostCode>GIR 0AA</PostCode>
</UKAddress>
</Address>

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

Here's the code for my sproc....

.
...
.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, 'root/data/', 2)
WITH (Number int '//no',
// address line detail here
Postcode varchar(10) '//PostCode')
.
...
.

I'm ok with OpenXML/sp_xml_preparedocument syntax and that, but I can't think how to handle unknown multiple address lines with the same tag name!

ANY HELP GREATLY RECEIVED!!!! :)

View 2 Replies View Related

OPENXML Vs BCP

Oct 3, 2005

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

View 1 Replies View Related

Using OPENXML

Jul 20, 2005

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.

View 2 Replies View Related

Using Sp_ExecuteSQL And Passing Table Name

Jan 11, 2001

I am creating a dynamic sql statement in a stored procedure.
The stored procedure has table name passed in.
I cannot pass in the table name as a parameter.

How do I pass the table name as a paramter is a dynamic sql statement

View 1 Replies View Related

Passing A Table Name Through A Parameter

Jul 2, 2004

Hi there,

I have this problem..

I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel....

Can someone help me??

Thanks!!

View 1 Replies View Related

Passing A Table Name Through A Parameter

Jul 3, 2004

Hi there..

I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name.
MS SQL is not accepting it because it is asking me to declare it... can someone help me??

Thankyou

"CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0)
AS
BEGIN
DECLARE @TB varchar(30)
SET @TB = @tablename
DECLARE @SR decimal(9,0)

SET @SR= (
SELECT MAX(SysRef)+1
FROM @tablename )

RETURN(@SR)

END

"

View 1 Replies View Related

Passing Value From Sp To #temp Table

Dec 13, 2006

Hi,

I've got following code to sync two identical tables between servers.

I first create a #temp table and truncate it so that I'm left with just structure. Next thing I want to do is insert the result of a subquery into the #temp table for later use but for some reason the #temp table remains empty...

As you can see below I've tried 2 approaches to get the result in the #temp table but without success...

Can someone help me coz its getting rather annoying...

thx in advance



Code:

/***** set instance to query from *****/

declare @frominstance varchar(30)
set @frominstance = 'SERVER1INSTANCE1'

/***************************************/

declare @SQL nvarchar(4000)

-- create empty table to insert in next step
select * into #temp from tableTest where id = '1'
truncate table #temp

-- execute query to insert values from SERVER1 into table #temp
set @SQL=N'select * into #temp from ['+@frominstance+'].dbTest.dbo.tableTest tbl where tbl.id not in (select id from tableTest)'
exec sp_executesql @SQL

-- ALTERNATIVE FOR PREVIOUS execute query to insert values from SERVER1 into table #temp
set @SQL=N'select * from ['+@frominstance+'].dbTest.dbo.tableTest tbl where tbl.id not in (select id from tableTest)'
insert into #temp
exec sp_executesql @SQL

select * from #temp -- test to see what in the #temp table

drop table #temp

View 1 Replies View Related

Why OPENXML Returns Only First Row?

Aug 11, 2006

 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

View 1 Replies View Related

SQL OPENXML: Best Approach

Feb 27, 2008

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
 

View 2 Replies View Related

XML - Openxml With SQL-Server

Feb 13, 2004

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

View 1 Replies View Related

OPENXML Problem

Sep 22, 2005

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

View 1 Replies View Related

OpenXML DataType

Sep 30, 2005

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

View 3 Replies View Related







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