Hi
I am trying to read by means of sp_xml_preparedocument a document XML stored
in a variable ntext, but this gives me the following error:
XML parsing error: Switch from current encoding to specified encoding not
supported.
Example XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
It is possible to do compatible unicode with xml with encoding:
sp_xml_preparedocument + ntext + encoding
Thank
Cristiánntext requires the encoding to be UCS-2 or UTF-16. You need to do the
conversion on the mid-tier before sending it to sp_xml_preparedocument.
Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
server code page that implies ISO-8859-1 encoding.
Best regards
Michael
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML
> stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristin
>|||Thanks Michael,
Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
accept UTF-16 but yes UTF-8, ?You Know Why?
XML --> UTF-16 '
Cristián
"Michael Rys [MSFT]" wrote:
> ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
> conversion on the mid-tier before sending it to sp_xml_preparedocument.
> Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
> server code page that implies ISO-8859-1 encoding.
> Best regards
> Michael
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
>
>|||For example:
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
Error...
but
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
OK
?
> Thanks Michael,
> Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does no
t
> accept UTF-16 but yes UTF-8, ?You Know Why?
> XML --> UTF-16 '
> Cristián
> "Michael Rys [MSFT]" wrote:
>|||Try:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
> For example:
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> Error...
> but
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> OK
> ?
>
>|||Hi Roger.
that work, but not thist:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
why? N-> unicode and UTF-8 idem or not?
"Roger Wolter[MSFT]" wrote:
> Try:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
>|||other example that work:
declare @.XmlInfo xml,
@.Xml nvarchar(max)
set @.Xml= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
set @.XmlInfo = @.Xml
select @.XmlInfo
--nvarchar --> XML
"sqlextreme" wrote:
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML stor
ed
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristián
>|||This works because character data is expected to be double-byte
declare @.XmlInfo xml
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This works because character data is expected to be single-byte.
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
In other words, if the encoding is UTF-8, the string holding it has to be
varchar ('<xml...>'); and if the encoding is UTF-16, then the string holding
it has to be nvarchar (N'<xml...>')
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
>|||The XML parser doesn't like being lied to. If you say it's utf-8 data you
need to pass it 8 bit data. If you say it's utf-16 you need to give it 16
bit data. In your example you prefix the string with an N which means the
string is Unicode so the parser parses Unicode data. When it runs into your
declaration that says it's utf-8 it is already parsing utf-16 so it errors
out because its is doing the wrong thing.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
>
Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts
Friday, March 23, 2012
Problem with sp_xml_preparedocument and ntext
Hi
I am trying to read by means of sp_xml_preparedocument a document XML stored
in a variable ntext, but this gives me the following error:
XML parsing error: Switch from current encoding to specified encoding not
supported.
Example XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
It is possible to do compatible unicode with xml with encoding:
sp_xml_preparedocument + ntext + encoding
Thank
Cristián
ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
conversion on the mid-tier before sending it to sp_xml_preparedocument.
Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
server code page that implies ISO-8859-1 encoding.
Best regards
Michael
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML
> stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristin
>
|||Thanks Michael,
Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
accept UTF-16 but yes UTF-8, ?You Know Why?
XML --> UTF-16 ?
Cristián
"Michael Rys [MSFT]" wrote:
> ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
> conversion on the mid-tier before sending it to sp_xml_preparedocument.
> Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
> server code page that implies ISO-8859-1 encoding.
> Best regards
> Michael
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
>
>
|||For example:
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
Error...
but
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
OK
?
[vbcol=seagreen]
> Thanks Michael,
> Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
> accept UTF-16 but yes UTF-8, ?You Know Why?
> XML --> UTF-16 ?
> Cristián
> "Michael Rys [MSFT]" wrote:
|||Try:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...[vbcol=seagreen]
> For example:
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> Error...
> but
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> OK
> ?
>
>
|||Hi Roger.
that work, but not thist:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
why? N-> unicode and UTF-8 idem or not?
"Roger Wolter[MSFT]" wrote:
> Try:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
>
|||other example that work:
declare @.XmlInfo xml,
@.Xml nvarchar(max)
set @.Xml= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
set @.XmlInfo = @.Xml
select @.XmlInfo
--nvarchar --> XML
"sqlextreme" wrote:
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristián
>
|||This works because character data is expected to be double-byte
declare @.XmlInfo xml
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This works because character data is expected to be single-byte.
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
In other words, if the encoding is UTF-8, the string holding it has to be
varchar ('<xml...>'); and if the encoding is UTF-16, then the string holding
it has to be nvarchar (N'<xml...>')
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...[vbcol=seagreen]
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
|||The XML parser doesn't like being lied to. If you say it's utf-8 data you
need to pass it 8 bit data. If you say it's utf-16 you need to give it 16
bit data. In your example you prefix the string with an N which means the
string is Unicode so the parser parses Unicode data. When it runs into your
declaration that says it's utf-8 it is already parsing utf-16 so it errors
out because its is doing the wrong thing.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...[vbcol=seagreen]
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
I am trying to read by means of sp_xml_preparedocument a document XML stored
in a variable ntext, but this gives me the following error:
XML parsing error: Switch from current encoding to specified encoding not
supported.
Example XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
It is possible to do compatible unicode with xml with encoding:
sp_xml_preparedocument + ntext + encoding
Thank
Cristián
ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
conversion on the mid-tier before sending it to sp_xml_preparedocument.
Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
server code page that implies ISO-8859-1 encoding.
Best regards
Michael
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML
> stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristin
>
|||Thanks Michael,
Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
accept UTF-16 but yes UTF-8, ?You Know Why?
XML --> UTF-16 ?
Cristián
"Michael Rys [MSFT]" wrote:
> ntext requires the encoding to be UCS-2 or UTF-16. You need to do the
> conversion on the mid-tier before sending it to sp_xml_preparedocument.
> Alternatively, ISO-8859-1 is a 1-byte encoding. Use text instead and a
> server code page that implies ISO-8859-1 encoding.
> Best regards
> Michael
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:646CCD6A-2B00-437A-B01A-EC245AE42A47@.microsoft.com...
>
>
|||For example:
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
Error...
but
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
OK
?
[vbcol=seagreen]
> Thanks Michael,
> Ok, test with UTF-16 and good, but testing XML in SQL Server 2005, does not
> accept UTF-16 but yes UTF-8, ?You Know Why?
> XML --> UTF-16 ?
> Cristián
> "Michael Rys [MSFT]" wrote:
|||Try:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...[vbcol=seagreen]
> For example:
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> Error...
> but
> declare @.XmlInfo xml
> set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> OK
> ?
>
>
|||Hi Roger.
that work, but not thist:
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
why? N-> unicode and UTF-8 idem or not?
"Roger Wolter[MSFT]" wrote:
> Try:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
> news:7D87F14A-47F8-4D95-BF91-8D52B121CD75@.microsoft.com...
>
|||other example that work:
declare @.XmlInfo xml,
@.Xml nvarchar(max)
set @.Xml= '<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
set @.XmlInfo = @.Xml
select @.XmlInfo
--nvarchar --> XML
"sqlextreme" wrote:
> Hi
> I am trying to read by means of sp_xml_preparedocument a document XML stored
> in a variable ntext, but this gives me the following error:
> XML parsing error: Switch from current encoding to specified encoding not
> supported.
> Example XML:
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <DA><USU tbxp1_varchar1="Sandra Damarid" /></DA>
> It is possible to do compatible unicode with xml with encoding:
> sp_xml_preparedocument + ntext + encoding
> Thank
> Cristián
>
|||This works because character data is expected to be double-byte
declare @.XmlInfo xml
set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-16"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
This works because character data is expected to be single-byte.
declare @.XmlInfo xml
set @.XmlInfo= '<?xml version="1.0" encoding="UTF-8"?>
<COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
In other words, if the encoding is UTF-8, the string holding it has to be
varchar ('<xml...>'); and if the encoding is UTF-16, then the string holding
it has to be nvarchar (N'<xml...>')
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...[vbcol=seagreen]
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
|||The XML parser doesn't like being lied to. If you say it's utf-8 data you
need to pass it 8 bit data. If you say it's utf-16 you need to give it 16
bit data. In your example you prefix the string with an N which means the
string is Unicode so the parser parses Unicode data. When it runs into your
declaration that says it's utf-8 it is already parsing utf-16 so it errors
out because its is doing the wrong thing.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlextreme" <sqlextreme@.discussions.microsoft.com> wrote in message
news:9A0778C0-43D1-4C8D-B7EB-51C99F2F1437@.microsoft.com...[vbcol=seagreen]
> Hi Roger.
> that work, but not thist:
> set @.XmlInfo= N'<?xml version="1.0" encoding="UTF-8"?>
> <COB><DET Estado="Sandra Damarid" Origen="Vasquez" /></COB>'
> why? N-> unicode and UTF-8 idem or not?
> "Roger Wolter[MSFT]" wrote:
Wednesday, March 21, 2012
Problem with Slowly Changing Dimension-transformation
Hi,
I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.
This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.
I only want to update the batchid for row that have changed in the current batch.
Is it possible to do this in any way without adding the Derived Column after the SCD transformation?
Thank for any help!!
Patrick
I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.
From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.
let me know if I do not answer your question.
wenyang
|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.Problem with Slowly Changing Dimension-transformation
Hi,
I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.
This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.
I only want to update the batchid for row that have changed in the current batch.
Is it possible to do this in any way without adding the Derived Column after the SCD transformation?
Thank for any help!!
Patrick
I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.
From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.
let me know if I do not answer your question.
wenyang
|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.sqlProblem with setting variable values in a loop
In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack.
This is the current structure:
SET @.MktNbr = 10
WHILE @.MktNbr < 90
BEGIN
DECLARE @.sqlstmt varchar(1000)
SET @.Market = '0' + CONVERT(char(2),@.MktNbr)
SET @.sqlstmt = ' SELECT (columns)
INTO dbo.table' + @.Market + '
FROM #table
WHERE marketcode = ''' + @.Market + '''
IF @.MktNbr = 50
BEGIN
SET @.MktNbr = 51
END
ELSE
IF @.MktNbr = 51
BEGIN
SET @.MktNbr = 52
END
ELSE
IF @.MktNbr = 52
BEGIN
SET @.MktNbr = 55
END
ELSE
IF @.MktNbr = 55
BEGIN
SET @.MktNbr = 60
END
ELSE
BEGIN
SET @.MktNbr = @.MktNbr + 10
END
EXEC (@.sqlstmt)
END
I'm probably having a blonde moment, but I'm trying to replace the if statements with this:
SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END
Clearly it's wrong because the proc bombs every time with a duplicate table error.
It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?That works fine for me:
DECLARE @.MktNbr int
SET @.MktNbr = 30
SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END
PRINT @.MktNbr|||First...dynamic sql...ugh
Second, why are you setting @.market BEFORE you set @.mrktnmbr?
third, non logged creation of a table will fail the second time you need to do the insert
Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
Clearly
You can only execute it once per table creation.
Also, again, the assignmnet is out of whack
You will always be trying to create the same table, over and over, because the tablename is not being included in your "logic"|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
My guess is that it fails on dbo.table081, right?
When @.MktNbr reaches 81, your case statement assigns it the new value of 81. The loop will try to make table081 again and fails.
You should set it to 90, so the loop will end.|||The answer is:
@.MktNbr never exceeds 81.|||the biggest wtf here is why are there so many market tables? why not just one?|||But as Brett (and now Rudy... Man I'm slow resonding to this thread) as highlighted above - the code is not good!
Even if you have a fix this is not the way for you to be doing this - explain what you're trying to achieve and hopefully we can prod you towards a better solution :)|||First...dynamic sql...ugh
Second, why are you setting @.market BEFORE you set @.mrktnmbr?
third, non logged creation of a table will fail the second time you need to do the insert
Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?
Fair points...allow me to address them in turn.
First: yes, dynamic SQL can be yucky but this is not something I developed, I am only making a modification to it. ;)
Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:
Third: I've had some ideas of things I'm going to try there so I'll get back to you on that. :)|||Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:
There's a reason for that|||Let me ask, do the tables get dropped before you hit this code?
How much data are we talking about?
Why not just hard code the 10 statements and not use dynamic sql?
Or, why not use 1 table and add a column for market code?
Really, all of this makes very little sense
So where did the person go? Burger King?|||There's a reason for that
Yep, there is. Thing is, the bossman doesn't want me to re-write the proc since it works...it's just slow. Right now the priority is just to make that amendment.
If you think that's good, there's some other ones that'd probably turn your hair white.|||Let me ask, do the tables get dropped before you hit this code?
How much data are we talking about?
Why not just hard code the 10 statements and not use dynamic sql?
Or, why not use 1 table and add a column for market code?
Really, all of this makes very little sense
So where did the person go? Burger King?
Don't fret, the person who suggested that I needed to set the variable to 90 was right; it works now. :cool:
Some of the procs do have hard-coded statements. Some of the developers here prefer the dynamic sql because they feel it's easier to maintain. I'm new here so I'm not in a position to tell them their code sucks, particularly since I'm the least experienced of the group. And yes, the tables get dropped; that's the first thing that happens in the proc. Right now we're not doing any design changes.|||that'd probably turn your hair white.
too late, the margarita's took care of that
And btw, what's "Too slow"
Instead of moving the data, why not just create views that are the name of the tables you are creating?
Oh, and if the smucks think your a jr. dba/developer, just keep coming here.
We'll smoke'em|||too late, the margarita's took care of that
And btw, what's "Too slow"
Instead of moving the data, why not just create views that are the name of the tables you are creating?
Oh, and if the smucks think your a jr. dba/developer, just keep coming here.
We'll smoke'em
This particular proc takes over an hour to execute.
Right now I'm testing one that has been executing for over four hours. It's obscene. :Ssql
This is the current structure:
SET @.MktNbr = 10
WHILE @.MktNbr < 90
BEGIN
DECLARE @.sqlstmt varchar(1000)
SET @.Market = '0' + CONVERT(char(2),@.MktNbr)
SET @.sqlstmt = ' SELECT (columns)
INTO dbo.table' + @.Market + '
FROM #table
WHERE marketcode = ''' + @.Market + '''
IF @.MktNbr = 50
BEGIN
SET @.MktNbr = 51
END
ELSE
IF @.MktNbr = 51
BEGIN
SET @.MktNbr = 52
END
ELSE
IF @.MktNbr = 52
BEGIN
SET @.MktNbr = 55
END
ELSE
IF @.MktNbr = 55
BEGIN
SET @.MktNbr = 60
END
ELSE
BEGIN
SET @.MktNbr = @.MktNbr + 10
END
EXEC (@.sqlstmt)
END
I'm probably having a blonde moment, but I'm trying to replace the if statements with this:
SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END
Clearly it's wrong because the proc bombs every time with a duplicate table error.
It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?That works fine for me:
DECLARE @.MktNbr int
SET @.MktNbr = 30
SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END
PRINT @.MktNbr|||First...dynamic sql...ugh
Second, why are you setting @.market BEFORE you set @.mrktnmbr?
third, non logged creation of a table will fail the second time you need to do the insert
Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
Clearly
You can only execute it once per table creation.
Also, again, the assignmnet is out of whack
You will always be trying to create the same table, over and over, because the tablename is not being included in your "logic"|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
My guess is that it fails on dbo.table081, right?
When @.MktNbr reaches 81, your case statement assigns it the new value of 81. The loop will try to make table081 again and fails.
You should set it to 90, so the loop will end.|||The answer is:
@.MktNbr never exceeds 81.|||the biggest wtf here is why are there so many market tables? why not just one?|||But as Brett (and now Rudy... Man I'm slow resonding to this thread) as highlighted above - the code is not good!
Even if you have a fix this is not the way for you to be doing this - explain what you're trying to achieve and hopefully we can prod you towards a better solution :)|||First...dynamic sql...ugh
Second, why are you setting @.market BEFORE you set @.mrktnmbr?
third, non logged creation of a table will fail the second time you need to do the insert
Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?
Fair points...allow me to address them in turn.
First: yes, dynamic SQL can be yucky but this is not something I developed, I am only making a modification to it. ;)
Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:
Third: I've had some ideas of things I'm going to try there so I'll get back to you on that. :)|||Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:
There's a reason for that|||Let me ask, do the tables get dropped before you hit this code?
How much data are we talking about?
Why not just hard code the 10 statements and not use dynamic sql?
Or, why not use 1 table and add a column for market code?
Really, all of this makes very little sense
So where did the person go? Burger King?|||There's a reason for that
Yep, there is. Thing is, the bossman doesn't want me to re-write the proc since it works...it's just slow. Right now the priority is just to make that amendment.
If you think that's good, there's some other ones that'd probably turn your hair white.|||Let me ask, do the tables get dropped before you hit this code?
How much data are we talking about?
Why not just hard code the 10 statements and not use dynamic sql?
Or, why not use 1 table and add a column for market code?
Really, all of this makes very little sense
So where did the person go? Burger King?
Don't fret, the person who suggested that I needed to set the variable to 90 was right; it works now. :cool:
Some of the procs do have hard-coded statements. Some of the developers here prefer the dynamic sql because they feel it's easier to maintain. I'm new here so I'm not in a position to tell them their code sucks, particularly since I'm the least experienced of the group. And yes, the tables get dropped; that's the first thing that happens in the proc. Right now we're not doing any design changes.|||that'd probably turn your hair white.
too late, the margarita's took care of that
And btw, what's "Too slow"
Instead of moving the data, why not just create views that are the name of the tables you are creating?
Oh, and if the smucks think your a jr. dba/developer, just keep coming here.
We'll smoke'em|||too late, the margarita's took care of that
And btw, what's "Too slow"
Instead of moving the data, why not just create views that are the name of the tables you are creating?
Oh, and if the smucks think your a jr. dba/developer, just keep coming here.
We'll smoke'em
This particular proc takes over an hour to execute.
Right now I'm testing one that has been executing for over four hours. It's obscene. :Ssql
Problem with Setting a variable in SQL String
Hi,
I am having problems setting the value of a variable in a SQL String
that I have to create dynamically in my procedure. The code that I
currently have is as follows:
set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
exec(@.sqlStatement)
The error message that I get is as follows:
Must declare the variable '@.compare_string'.
Here @.compare_string has already been declared in the procedure and I
don't have a problem using the variable anywhere else but this SQL
Statement (when called using the EXEC function).
I am not sure why SQL Server can't see the variable declared when used
in a string in conjunction with EXEC. Is this a syntax issue? Any help
on this issue would be greatly appreciated!
Thanks in advance.You need a parms string and an exec string, like this:
SET @.Parms = `@.compare_string`
set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).
> I am not sure why SQL Server can't see the variable declared when used
> in a string in conjunction with EXEC. Is this a syntax issue? Any help
> on this issue would be greatly appreciated!
> Thanks in advance.|||Thanks for your reply. The sp_executesql procedure still doesn't give
the desired results. I am posting the updated piece of code and sample
output from the Query Analyzer.
------
set @.parameter_String=N'@.compare_string nvarchar(4000)'
set @.sqlStatement='Set @.compare_string=(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
Print @.sqlStatement
EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
Print @.compare_String
------
When I print the value of @.compare_String in the end its a NULL.
However, if I run the same query without the set @.compare_string
clause, it does work perfectly and returns the values of two columns
concatenated together. Any clues as to where I might be going wrong?
Thanks,
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> You need a parms string and an exec string, like this:
> SET @.Parms = `@.compare_string`
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > exec(@.sqlStatement)
> > The error message that I get is as follows:
> > Must declare the variable '@.compare_string'.
> > Here @.compare_string has already been declared in the procedure and I
> > don't have a problem using the variable anywhere else but this SQL
> > Statement (when called using the EXEC function).
> > I am not sure why SQL Server can't see the variable declared when used
> > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > on this issue would be greatly appreciated!
> > Thanks in advance.|||[posted and mailed, please reply in news]
Aamer Nazir (aamernazir_01@.hotmail.com) writes:
> I am having problems setting the value of a variable in a SQL String
> that I have to create dynamically in my procedure. The code that I
> currently have is as follows:
>
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).
The EXEC() statement is another scope which is not part of your procedure.
Thus, @.compare_string is not defined in that example.
For better examples than the one posted, see
http://support.microsoft.com/?id=262499 and
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, that won't work. Sorry, I just focused on the parameter part.
You can just do this:
select @.compare_string = mytable.myfield FROM mytable where Identity_Column
= myvalue
or, in your specific case:
'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
'_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'
At least this is the syntax you should use in this case. Otherwise, you are
effectively trying to bind @.compare_string to a recordset result, which
doesn't work.
Make sure you add in the error checking afterwards!! :)
"Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> Thanks for your reply. The sp_executesql procedure still doesn't give
> the desired results. I am posting the updated piece of code and sample
> output from the Query Analyzer.
>
> ------
> set @.parameter_String=N'@.compare_string nvarchar(4000)'
> set @.sqlStatement='Set @.compare_string=(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> Print @.sqlStatement
> EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> Print @.compare_String
> ------
> When I print the value of @.compare_String in the end its a NULL.
> However, if I run the same query without the set @.compare_string
> clause, it does work perfectly and returns the values of two columns
> concatenated together. Any clues as to where I might be going wrong?
> Thanks,
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > You need a parms string and an exec string, like this:
> > SET @.Parms = `@.compare_string`
> > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > exec(@.sqlStatement)
> > > > The error message that I get is as follows:
> > > > Must declare the variable '@.compare_string'.
> > > > Here @.compare_string has already been declared in the procedure and I
> > > don't have a problem using the variable anywhere else but this SQL
> > > Statement (when called using the EXEC function).
> > > > I am not sure why SQL Server can't see the variable declared when used
> > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > on this issue would be greatly appreciated!
> > > > Thanks in advance.|||Thanks for pointing me to the right direction. The code works
perfectly fine now. The problem was with the syntax that Erland
Sommarskog mentioned in his posting. You have to specify the parameter
type (input or output) in the parameter specification string (the
second argument to sp_executesql).
Best Regards,
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bsboku$o7p$1$8300dec7@.news.demon.co.uk>...
> Yes, that won't work. Sorry, I just focused on the parameter part.
> You can just do this:
> select @.compare_string = mytable.myfield FROM mytable where Identity_Column
> = myvalue
> or, in your specific case:
> 'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
> '_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'
> At least this is the syntax you should use in this case. Otherwise, you are
> effectively trying to bind @.compare_string to a recordset result, which
> doesn't work.
> Make sure you add in the error checking afterwards!! :)
> "Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
> news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> > Thanks for your reply. The sp_executesql procedure still doesn't give
> > the desired results. I am posting the updated piece of code and sample
> > output from the Query Analyzer.
> > ------
> > set @.parameter_String=N'@.compare_string nvarchar(4000)'
> > set @.sqlStatement='Set @.compare_string=(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > Print @.sqlStatement
> > EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> > Print @.compare_String
> > ------
> > When I print the value of @.compare_String in the end its a NULL.
> > However, if I run the same query without the set @.compare_string
> > clause, it does work perfectly and returns the values of two columns
> > concatenated together. Any clues as to where I might be going wrong?
> > Thanks,
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > > You need a parms string and an exec string, like this:
> > > > SET @.Parms = `@.compare_string`
> > > > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > > > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > > > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > > > > exec(@.sqlStatement)
> > > > > > The error message that I get is as follows:
> > > > > > Must declare the variable '@.compare_string'.
> > > > > > Here @.compare_string has already been declared in the procedure and I
> > > > don't have a problem using the variable anywhere else but this SQL
> > > > Statement (when called using the EXEC function).
> > > > > > I am not sure why SQL Server can't see the variable declared when used
> > > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > > on this issue would be greatly appreciated!
> > > > > > Thanks in advance.
Monday, March 12, 2012
Problem with saving Chinese.
Hi.. I'm trying to save text into SQL 2000 database.
When user enter text in text box , the text save into variable , and show it in confirm page , after save the text to database, all the text turned into "??"
I try to view the data in SQL enterprise Manager / Web Matrix / ASP web page gridview , all of them showing the text fields in "??"
Then I try to add record which come with SQL 2000 enterprise Manager.After save the record , the chinese also turned into "??"
Is there something I need to set for database or server?
Is your datatype VARCHAR, TEXT or CHAR as the datatype on the table columns?
If VARCHAR change to NVARCHAR. If TEXT change to NTEXT. If CHAR change to NCHAR.
If you are using stored procedures (and you should be), you will to amend the parameter declaration on them.
You might want to change the collating sequence on your database to one more appropriate to Chinese (if collating sequence is meaningful for that language), but otherwise you should not need to change anything at the database level.
If this reply provides the anser to your question, please mark as such.
|||all the text are stored in varchar... I'll try to change it to nvarchar|||yea... it works when I change all the text field type to nnvarchar..
Wednesday, March 7, 2012
Problem With Quotes in @[System::ErrorDescription] Variable
I am using an Execute T-SQL Task as a part of an OnError event Handler in my SSIS Package. When occurs an error, using the Expressions-feature, my Execute T-SQL task builds an Insert Statement to insert the @.System::ErrorDescription into a table.
"
INSERT INTO [ErrorDB].[dbo].[ISErrors]
([EventType]
,[PackageName]
,[TaskName]
,[DateDone]
,[Status]
,[Host]
,[ErrorCode]
,[ErrorDescription]
,[Comments])
VALUES
( 'OnError'
, '"+ @.[System::PackageName] + "'
, '"+ @.[System::SourceName] + "'
,getdate()
,'Failed'
,'" + @.[System::MachineName] + "'
, null
, '" + @.[System::ErrorDescription] + "'
,null
)
"
When I run the task ( not the package, only the task) everything is ok ( since the ErrorDescription variable is empty)
But when an error occurs in my package, then the T-SQL task fails giving the following error
[Execute SQL Task] Error: Executing the query " INSERT INTO [LogDB].[dbo].[ISFullMaintenanceErrors] ([EventType] ,[PackageName] ,[TaskName] ,[DateDone] ,[Status] ,[Host] ,[ErrorCode] ,[ErrorDescription] ,[Comments]) VALUES ( 'OnError' , 'Package' , 'TrialTempEx' ,getdate() ,'Failed' ,'SCYLLA' , null , @.[System::ErrorDescription] ,null ) " failed with the following error: "Must declare the scalar variable "@.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I realized that the problem is that the @.[System::ErrorDescription] contains quotes ( " ' ) and this is the reason that the insert statement fails. I tried the replace function but there was no solution
Any help would be appreciated
For this very reason I advocate using the parameter support in the Exec SQL Task, over expressions for this type of statement. This is basically a SQL injection attack, albeit benign, but by using a parameterised statement, you can protect yourself from this. The other issue you may hit is with long descriptions, you could exceed the 4000 character limit for an expression result.|||Darren and I don't exactly see eye-to-eye on this one but I'll concede he makes a good, if slightly dramatic, point about SQL Injection
If you do want to carry on using expressions then you can just wrap the variable in a REPLACE() function.
-Jamie
Monday, February 20, 2012
Problem with package.Execute passing variables
I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions.
When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error.
Any ideas?
- C# -
Reference to Microsoft.SQLServer.ManagedDTS
using Microsoft.SqlServer.Dts.Runtime;
Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\\SSISPackages\\Package.dtsx", null);
DTSPack.Variables.Add("Variable1", true, "", var1.ToString());
DTSPack.Variables.Add("Variable2", true, "", var2);
DTSPack.Variables.Add("Variable3", true, "", 100);
DTSPack.Variables.Add("Variable4", true, "", var4.ToString());
DTSExecResult pkgResult = DTSPack.Execute(null, DTSPack.Variables, null, null, null);
A copy of the error returned.
Source: Bulk Insert Task
Description: The result of the expression "@.[User::TableName]" on property "DestinationTableName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
|||What is DestinationTableName?|||DestinationTableName is a Property in the Bulk Insert Task Expression List.
I am trying to pass a variable from C# to SSIS that will be used in an expression (in this case, to tell the Bulk Insert where to write the data to).
my C# code where I set the variable looks like...
vars["TableName"].Value = "TestDB."+Session["GenSQLName"].ToString();
I altered my code above to match this logic and I am getting proper returns on my variables which tells me SSIS is receiving the proper data but is not handleing it correctly.
Variables vars = DTSPack.Variables;
vars["Var1"].Value = var1.ToString();
vars["TableName"].Value = "TestDB."+Session["GenSQLName"].ToString();
DTSExecResult pkgResult = DTSPack.Execute();
BMcDowell wrote:
A copy of the error returned.
Source: Bulk Insert Task
Description: The result of the expression "@.[User::TableName]" on property "DestinationTableName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I have not used bulk insert task; but the error suggests that 'DestinationTableName' property cannot be override via expression...It may be that the problem?
|||I'm in the same position as Rafael here, but I do see that you can assign expressions to properties in the Bulk Insert Task... At least the GUI lets you use expressions for properties of the Bulk Insert task.Are you sure the variables are scoped correctly? Have you captured the TableName variable in SSIS to ensure that you are getting the correct results?
I wouldn't think it would matter, but normally when I select a table from a drop down box, I get just the table name. When I selected a table in the Bulk Insert task, I got the fully qualified name for the table... Would that matter? So, "database.dbo.table_name" is what showed up.
Phil|||
The path is fully represented. If i paste in the variables exactly as they are being passed (verified via the data flow) the package runs fine and updates the database. Are there any settings in SSIS that would prevent me from dynamically passing variables into expressions? I went through all the properties for the variables and the bulk insert task and have yet to find a logical setting.
I
|||The path is fully represented. If i paste in the variables exactly as they are being passed (verified via the data flow) the package runs fine and updates the database. Are there any settings in SSIS that would prevent me from dynamically passing variables into expressions? I went through all the properties for the variables and the bulk insert task and have yet to find a logical setting.
I
There isn't an option to my knowledge, and this could be a bug. I'll have to test on my own when I get a chance later today.|||
We had the same problem when executing from a ASP.net app. The issue appears to be that SSIS only evaluates expressions when the package is initially loaded and the package never really unloads from the IIS server until it bounces. The only way that I was able to get around it was to change the web app to kick off the package execution on a new thread. That causes the package to be reloaded each time so the expressions are rebuilt correctly.
Hope that helps.
Harry
|||Thank you Harry, Interesting approach but makes a lot of sense.I am not very familiar with threading.I did some quick research on the web and have a few ideas.I will post back the results once I figure out how to accomplish this task.
|||DTSPack.Dispose(); did the trick.
Here is an example of the code for future reference. Thank you to everybody who helped me brainstorm on this. I feel like such a rookie at times.
Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\\SSISPackages\\TestBulkInsert.dtsx", null);
DTSPack.ImportConfigurationFile("d:\\SSISPackages\\TestBulkInsert.dtsConfig");
Variables vars = DTSPack.Variables;
vars["SSISVar1"].Value = CSharpVar1.ToString();
vars["SSISVar2"].Value = CSharpVar2;
vars["SSISVar3"].Value = 100;
vars["SSISTableName"].Value = "SQLDataBase."+Session["GenSQLName"].ToString();
DTSExecResult pkgResult = DTSPack.Execute();
DTSPack.Dispose();
I also tried Dispose and saw the same results. Works the first time only. I could not get the package to actually unload until the thread completed. The app domain idea should accomplish the same thing, but I believe it would be more work especially if you are running under the IIS context. Running under a new thread is pretty simple and there are a lot of examples on the web, but if you can't find one let me know and I would be happy to post one.
Harry
|||Try setting the RaiseChangedEvent property of each variable to TRUE. This cause and event to be fired that will force the recalculation of all expressions that depend on this variable whenever the variable value changes
Subscribe to:
Posts (Atom)