Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHORHello,
Try this (obvious) query:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT 'Customer CustomerID="'+CustomerID
+'" ContactName="'+ContactName+'"' AS COLONE
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
Is this what you need ?
Razvan|||Hi Razvan,
Thanxs But this will not work. what I actually want is to get all inner
attribute of a xml. here you are concating ContactName...but I dont want to
have a hardcoding like this. client can pass Name , Cname...any thing. I jus
t
want a list of all all attribute.
I have tried this also
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT CustomerID ,ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)
)
for xml auto
But gave me error
Unnamed column or table names cannot be used as XML identifiers. Name
unnamed columns using AS in the SELECT statement.
Regards,
Kishor
"Razvan Socol" wrote:
> Hello,
> Try this (obvious) query:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
>
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT 'Customer CustomerID="'+CustomerID
> +'" ContactName="'+ContactName+'"' AS COLONE
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> Is this what you need ?
> Razvan
>|||> here you are concating ContactName...
> but I dont want to have a hardcoding like this.
You already did hardcoding: in the parameters of the OPENXML function,
in the WITH clause.
> I have tried this also [...] for xml auto [...] But gave me error
[...]
Try this:
[...]
SELECT * INTO #tmp
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
SELECT * FROM #tmp FOR XML AUTO
DROP TABLE #tmp
Razvan|||Yes,
Just to explain you all I have done .. I just want inner attributes...
if you know .. let me know.
TIA
Kishor
"Razvan Socol" wrote:
> You already did hardcoding: in the parameters of the OPENXML function,
> in the WITH clause.
>
> [...]
> Try this:
> [...]
> SELECT * INTO #tmp
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> SELECT * FROM #tmp FOR XML AUTO
> DROP TABLE #tmp
> Razvan
>
Showing posts with label solve. Show all posts
Showing posts with label solve. Show all posts
Friday, March 30, 2012
HELP ON XML
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHORIf what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:
> If what you want is the <Customer> element with all attributes, then you c
an
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the nod
e
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at lea
st
> I want some round about way, which will solve my problem. I am here pastin
g
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHORIf what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:
> If what you want is the <Customer> element with all attributes, then you c
an
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the nod
e
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at lea
st
> I want some round about way, which will solve my problem. I am here pastin
g
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>
HELP ON XML
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHOR
If what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR
|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:
> If what you want is the <Customer> element with all attributes, then you can
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the node
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at least
> I want some round about way, which will solve my problem. I am here pasting
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHOR
If what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR
|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:
> If what you want is the <Customer> element with all attributes, then you can
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the node
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at least
> I want some round about way, which will solve my problem. I am here pasting
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>
Help on using LIKE in sproc
hi all,
After reading some of the threads here, I experimented by changing ArticleContent and @.srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @.srch (being new at this, I could be seriously wrong though), like this:
My original string looks like this
Or you need to append the wildcards directly in the SQL statement
It seems the general consensus is that the first option is the best way to go. Note that I added SET NOCOUNT ON to your sproc. This is especially necessary for the first sproc since without it SQL will send something like '1 item selected' as the first resultset back to your ASP.NET page.
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @.srch)
GO
After reading some of the threads here, I experimented by changing ArticleContent and @.srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @.srch (being new at this, I could be seriously wrong though), like this:
prmSearch.ParameterName = "@.srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")
My original string looks like this
strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"
What am I doing wrong?? Thanks in advance for any help.Hey, I'm new to sprocs but I have never seen the first line like that. i usually write them and seen them liek this;
CREATE PROCEDURE dbo.sp_ArticleSearch
@.srch text
as...
If that isn't the problem then could it be the LIKE part? Should it not have '%' too? or some other char for t-sql?
I may not know the correct answer, but try it and let me know, cuz i too am in the learning process and will use statements like that in the furture.|||You absolutely need to add the % wildcards to get this work for you the way it used to work.
You either need to add the '%' to the @.srch parameter:
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS
SET NOCOUNT ON
SELECT @.srch = '%' + @.srch + '%'
SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @.srch)
GO
Or you need to append the wildcards directly in the SQL statement
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS
SET NOCOUNT ON
SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE '%' + @.srch + '%')
GO
It seems the general consensus is that the first option is the best way to go. Note that I added SET NOCOUNT ON to your sproc. This is especially necessary for the first sproc since without it SQL will send something like '1 item selected' as the first resultset back to your ASP.NET page.
Terri|||Thanks guys, will give this a try later.|||Terri, still no luck,
both of the sample procedures gave error:
Error 403 Invalid Operator For Data Type. Operator Equals Add. Type equals Text.
I've absolutely no idea what this means.|||OK, I got this to work if the parameter type is set to 'varchar' instead of 'text' in the stored procedure itself so it'll look like this:
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch varchar(8000))
AS
SET NOCOUNT ON
SELECT *
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE '%' + @.srch + '%')
GO
Thank you very much guys.|||I found that error to be is data-type mismatch. Is the field 'ArticleAbstract' data-type text? as u declared @.srch to be?|||heh, u beat me to itsql
Friday, March 23, 2012
Help on creating SSIS
Hello ,
I am getting the given error message while creting SSIS. Please help me to solve the error....
Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..
regards,
Malhar , THanks
Where do you see the error message?|||Hi,
Error is coming in Error List...
When i open new project->Integration Service Project.. I see the Package.dtsx with the following error
Plz help me out if u can..
Thanx n Regards,
Malhar
|||Hi,
Error is coming in Error List...
When i open new project->Integration Service Project.. I see the Package.dtsx with the following error
Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..
Plz help me out if u can..
Thanx n Regards,
Malhar
|||If all you're doing is creating a brand new project and this error appears before you've done anything then it sounds like you've got a corrupt installation. Unfortunatly I'm not able to offer any meaningful help other than trying to reinstall the SQL 2005 Client Tools|||Hi,
Thanx for ur reply.
Yup i m creating brand new project and as soon as i create new project this error comes.
Well, whatever solution u have mentioned, is the only solution? and after reinstalling sql server 2005, my problem wud b solved?
Thanx n Regards,
Malhar
|||hi,
Thanx for ur reply.
Yup i m creating brand new project and as soon as i create new project, that error comes.
Well, Whatever solution u have mentioned, is the only solution? My problen will get solved if i wud reinstall MS Sql Server 2005?
or is there any other solution available? Whts the meaning of that error : Failed on the unmanaged server..
Thanx and Regards,
Malhar
|||I'm not sure what's causing your issue, but it's possible that your SSIS installation has become corrupted. The only solution I can think of is to do a fresh install.|||malhar wrote:
Hi,
Error is coming in Error List...
When i open new project->Integration Service Project.. I see the Package.dtsx with the following error
Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..
Plz help me out if u can..
Thanx n Regards,
Malhar
This looks like it may be a problem related to InstallShield. I just received the same error opening an existing SSIS project. Here is a link to what may be a solution
http://support.installshield.com/kb/view.asp?articleid=q106194
Help on creating SSIS
Hello ,
I am getting the given error message while creting SSIS. Please help me to solve the error....
Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..
regards,
Malhar , THanks
Where do you see the error message?|||Hi,
Error is coming in Error List...
When i open new project->Integration Service Project.. I see the Package.dtsx with the following error
Plz help me out if u can..
Thanx n Regards,
Malhar
|||Hi,
Error is coming in Error List...
When i open new project->Integration Service Project.. I see the Package.dtsx with the following error
Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..
Plz help me out if u can..
Thanx n Regards,
Malhar
|||If all you're doing is creating a brand new project and this error appears before you've done anything then it sounds like you've got a corrupt installation. Unfortunatly I'm not able to offer any meaningful help other than trying to reinstall the SQL 2005 Client Tools|||Hi,
Thanx for ur reply.
Yup i m creating brand new project and as soon as i create new project this error comes.
Well, whatever solution u have mentioned, is the only solution? and after reinstalling sql server 2005, my problem wud b solved?
Thanx n Regards,
Malhar
|||hi,
Thanx for ur reply.
Yup i m creating brand new project and as soon as i create new project, that error comes.
Well, Whatever solution u have mentioned, is the only solution? My problen will get solved if i wud reinstall MS Sql Server 2005?
or is there any other solution available? Whts the meaning of that error : Failed on the unmanaged server..
Thanx and Regards,
Malhar
|||I'm not sure what's causing your issue, but it's possible that your SSIS installation has become corrupted. The only solution I can think of is to do a fresh install.|||malhar wrote:
Hi,
Error is coming in Error List...
When i open new project->Integration Service Project.. I see the Package.dtsx with the following error
Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..
Plz help me out if u can..
Thanx n Regards,
Malhar
This looks like it may be a problem related to InstallShield. I just received the same error opening an existing SSIS project. Here is a link to what may be a solution
http://support.installshield.com/kb/view.asp?articleid=q106194
Monday, March 12, 2012
Help needed to setup replication from SCRATCH
Hi,
I have tried and tried to solve my problems without any success.
Here I go...
I have 2 SBS 2000 Servers (Server A and Server B) in different offices 20
miles from each other.
SQL SP4 on each with ISA Ports open to 14446 (dont like 1433).
ODBC connects from my workstation to each server on port 14446 so
communication to database is ok.
I have only one database (800mb) in size that I need MERGE replication.
I have setup the client utility (in think) in server A (subscriber) with an
alias name of server B. Server B (distributor) simply wont talk to server
A? even though I know the connection is valid.
What I need is from scratch a walkthough on how to setup replication on both
servers. I have searched the net for weeks now, posted questions and read MS
articles but I'm not getting anywhere...
Please help.
TIM
Hopefully this article will help clarify things a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi,
Have read that article and undertand most of it but there are 2 things
missing.
1. Client Network setup (alias setup on the subscriber). Do you have to do
this on the publisher as well?.
2. FTP...Seen a lot of this. Do I need to setup FTP on both servers as
well!...
Sorry but its not sinking in yet...
"Paul Ibison" wrote:
> Hopefully this article will help clarify things a bit:
> http://www.replicationanswers.com/InternetArticle.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I think these questions really relate to push/pull. The article refers to a
pull subscription. For push, the merge agent needs to see the subscriber so
set up the alias on the publisher. I'm reasoning this out as I don't have a
test environment here at present. For the FTP, the ports need to be opened
in your firewalls, but the FTP snapshot files reside on the publisher ie the
FTP ServerName referred to is the publisher's server name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK thanks.
Whats the correct way to complete the client network utility (IP/Computer
Name/Server Name etc).
Regards
"Paul Ibison" wrote:
> I think these questions really relate to push/pull. The article refers to a
> pull subscription. For push, the merge agent needs to see the subscriber so
> set up the alias on the publisher. I'm reasoning this out as I don't have a
> test environment here at present. For the FTP, the ports need to be opened
> in your firewalls, but the FTP snapshot files reside on the publisher ie the
> FTP ServerName referred to is the publisher's server name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||There's an example on my article - the only thing to be careful of is to set
it up for TCP/IP and use the actual server name as the alias name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply but I've tried everything now and i'm still not any
connection from the subscriber to the publisher!!!!!.
I'm gonna spend a few more days on this and then give up...........
Thanks
TIM
"Paul Ibison" wrote:
> There's an example on my article - the only thing to be careful of is to set
> it up for TCP/IP and use the actual server name as the alias name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||It sounds like firewall issues ie ports not opened, or opened in one
direction only, or your IP address not being in the allowed list etc.
Can you get a connection using SSMS/EM using the alias?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply.
On the ISA firewall i have set port 14446 as my default sql server port and
disabled 1433. I then published the server using this rule to forward to the
external interface (2 nics). I then set a new protocol def (14446) and bound
the rule to this.
Now I can access sql database using ODBC from my XP using this
port/password/username so I can only assume that the correct ports and comms
are getting through.
Now my SBS server computer name is CLIFT.Local and my alias for SQL is
CLIFTSERVER.
I have changed the service startup accounts to a user i created in AD giving
FULL admin rights. All services including the agent is running fine.
I have created a distributor/publisher on CLIFT server.
I have created the alias to CLIFTSERVER on my BROADSERVER (other SBS Server)
and in the client network utility it asks for computer name and server name
but I'v every possible combination and it still wont connect.
My routers are set to not respond to PING but when I disable this function
PING works so there is a connection there.
Is there anything else I need to do before I setup the subscriber?.
Please help...
"Paul Ibison" wrote:
> It sounds like firewall issues ie ports not opened, or opened in one
> direction only, or your IP address not being in the allowed list etc.
> Can you get a connection using SSMS/EM using the alias?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||OK - for the replication setup, the alias needs to be CLIFT. The server name
needs to be the IP address and you also specify the port, the network lib is
tcp/ip. Once set up like that, just try to register in SSMS using CLIFT as
the server name to test.
For the initialization you'll need to set up FTP or do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have tried and tried to solve my problems without any success.
Here I go...
I have 2 SBS 2000 Servers (Server A and Server B) in different offices 20
miles from each other.
SQL SP4 on each with ISA Ports open to 14446 (dont like 1433).
ODBC connects from my workstation to each server on port 14446 so
communication to database is ok.
I have only one database (800mb) in size that I need MERGE replication.
I have setup the client utility (in think) in server A (subscriber) with an
alias name of server B. Server B (distributor) simply wont talk to server
A? even though I know the connection is valid.
What I need is from scratch a walkthough on how to setup replication on both
servers. I have searched the net for weeks now, posted questions and read MS
articles but I'm not getting anywhere...
Please help.
TIM
Hopefully this article will help clarify things a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi,
Have read that article and undertand most of it but there are 2 things
missing.
1. Client Network setup (alias setup on the subscriber). Do you have to do
this on the publisher as well?.
2. FTP...Seen a lot of this. Do I need to setup FTP on both servers as
well!...
Sorry but its not sinking in yet...
"Paul Ibison" wrote:
> Hopefully this article will help clarify things a bit:
> http://www.replicationanswers.com/InternetArticle.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I think these questions really relate to push/pull. The article refers to a
pull subscription. For push, the merge agent needs to see the subscriber so
set up the alias on the publisher. I'm reasoning this out as I don't have a
test environment here at present. For the FTP, the ports need to be opened
in your firewalls, but the FTP snapshot files reside on the publisher ie the
FTP ServerName referred to is the publisher's server name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK thanks.
Whats the correct way to complete the client network utility (IP/Computer
Name/Server Name etc).
Regards
"Paul Ibison" wrote:
> I think these questions really relate to push/pull. The article refers to a
> pull subscription. For push, the merge agent needs to see the subscriber so
> set up the alias on the publisher. I'm reasoning this out as I don't have a
> test environment here at present. For the FTP, the ports need to be opened
> in your firewalls, but the FTP snapshot files reside on the publisher ie the
> FTP ServerName referred to is the publisher's server name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||There's an example on my article - the only thing to be careful of is to set
it up for TCP/IP and use the actual server name as the alias name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply but I've tried everything now and i'm still not any
connection from the subscriber to the publisher!!!!!.
I'm gonna spend a few more days on this and then give up...........
Thanks
TIM
"Paul Ibison" wrote:
> There's an example on my article - the only thing to be careful of is to set
> it up for TCP/IP and use the actual server name as the alias name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||It sounds like firewall issues ie ports not opened, or opened in one
direction only, or your IP address not being in the allowed list etc.
Can you get a connection using SSMS/EM using the alias?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply.
On the ISA firewall i have set port 14446 as my default sql server port and
disabled 1433. I then published the server using this rule to forward to the
external interface (2 nics). I then set a new protocol def (14446) and bound
the rule to this.
Now I can access sql database using ODBC from my XP using this
port/password/username so I can only assume that the correct ports and comms
are getting through.
Now my SBS server computer name is CLIFT.Local and my alias for SQL is
CLIFTSERVER.
I have changed the service startup accounts to a user i created in AD giving
FULL admin rights. All services including the agent is running fine.
I have created a distributor/publisher on CLIFT server.
I have created the alias to CLIFTSERVER on my BROADSERVER (other SBS Server)
and in the client network utility it asks for computer name and server name
but I'v every possible combination and it still wont connect.
My routers are set to not respond to PING but when I disable this function
PING works so there is a connection there.
Is there anything else I need to do before I setup the subscriber?.
Please help...
"Paul Ibison" wrote:
> It sounds like firewall issues ie ports not opened, or opened in one
> direction only, or your IP address not being in the allowed list etc.
> Can you get a connection using SSMS/EM using the alias?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||OK - for the replication setup, the alias needs to be CLIFT. The server name
needs to be the IP address and you also specify the port, the network lib is
tcp/ip. Once set up like that, just try to register in SSMS using CLIFT as
the server name to test.
For the initialization you'll need to set up FTP or do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Monday, February 27, 2012
Help Me with This Query!
Hi All,
I have this query and I want to show single entry on the every records, I already use Group By, but it doesn't solve the problem.
Please help to find the problem.
=============================================================================================
SELECT EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal,SUM(TotalOrder)as TotalOrderFROM
(SELECT
e.OfficeCode,
(SELECTDISTINCT OfficeFROM OfficeCode ocWHERE oc.Code= e.OfficeCode)as Office,
e.OfficeCode+'-'+ e.EmployeeCodeas EmployeeCode,
(SELECT FirstName+' '+ LastNameFROM Person pWHERE p.PersonID= e.PersonID)as EmployeeName,
InvoiceTotal=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENSUM(o.OrderFee)ELSE 0END,
TotalOrder=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENCount(o.OrderID)ELSE 0END
FROM Employee e
LEFTJOIN oms.[OrderAssignment] oaON e.EmployeeID= oa.EmployeeID
LEFTJOIN oms.[Order] oON o.OrderID= oa.OrderID
LEFTJOIN OfficeCode ocON oc.Code= e.OfficeCode
WHERE e.OfficeCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.OfficeCode,','))AND
e.IsManager='True'AND
e.DivisionCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.DivisionCode,','))
GROUPBY e.OfficeCode, e.EmployeeCode, e.PersonID, o.InvoiceDT, o.OrderFee, o.OrderID)as tmp
GROUPBY EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal, TotalOrder
=================================================================================================
Thanks in advance.
Anyone please...|||I got it. The problem is I put Group By TotalOrder. If I take out the group by totalOrder, it will works just fine. Thanks.Friday, February 24, 2012
help me to solve this
hello,
i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data
and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server
like wise i have many xl files ( which have many sheets ) .
for eg:
excel file 1:
-> sheet 1
-> sheet 2
-> sheet 3
excel file 2:
-> sheet 1
-> sheet 2
-> sheet 3
excel file 3:
-> sheet 1
-> sheet 2
-> sheet 3
now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package
so plz help me by giving the solution asap.
thanks
B L Rao
hello ,
while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.
i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.
thanks and regards
B L Rao.
|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.Help me solve this.
When I collect data from a form, I recieved this message:
"Cannot create a row of size 8822 which is greater than the allowable maximum of 8060. The statement has been terminated."
Can any body tell me how to solve this, my data field is varchar size 8000 bytes, and I posted only ~ 6000 byte.Show the entire table definition, and perhaps we can help.|||There are several data types that store a fixed amount of data. For example, a char (10) field will store 10 bytes even if you never assign a value to it. So, if you have 4000 bytes worth of fixed-length fields, and you put 6000 bytes into a varchar(8000) field, you would end up with a record that's 10000 bytes and the update wil fail.
Subscribe to:
Posts (Atom)