Showing posts with label value. Show all posts
Showing posts with label value. Show all posts

Monday, March 19, 2012

[SQLDatasource]Dynamic selection of column

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.
I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.

If you see I have put in the querystring as columnname @.sescolumn which I have initialised as asp:sessionparameter.
But it gives no results. When I put @.sescolumn between [] like normal columnnames are it doesn't work also.

Can someon put my on the right path?

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring%>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @.sescolumn like @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where ((@.sescolumn='AutoID' AND [AutoID] LIKE @.seskeyword) OR (@.sescolumn='Merk' AND [Merk] LIKE @.seskeyword) OR (@.sescolumn='Kleur' AND [Kleur] LIKE @.seskeyword) OR (@.sescolumn='Type' AND [Type] LIKE @.seskeyword) OR (@.sescolumn='Autotype' AND [Autotype] LIKE @.seskeyword) OR (@.sescolumn='prijs' AND [prijs] LIKE @.seskeyword) OR (@.sescolumn='Zitplaatsen' AND [Zitplaatsen] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding1' AND [Afbeelding1] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding2' AND [Afbeelding2] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding3' AND [Afbeelding3] LIKE @.seskeyword) OR (@.sescolumn='Afbeelding4' AND [Afbeelding4] LIKE @.seskeyword)) and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

Or...

<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where CASE @.sesscolumn WHEN 'AutoID' THEN [AutoID] WHEN 'Merk' THEN [Merk] WHEN 'Kleur' THEN [Kleur] WHEN 'Type' THEN [Type] WHEN 'Autotype' THEN [Autotype] WHEN 'prijs' THEN [prijs] WHEN 'Zitplaatsen' THEN [Zitplaatsen] WHEN 'Afbeelding1' THEN [Afbeelding1] WHEN 'Afbeelding2' THEN [Afbeelding2] WHEN 'Afbeelding3' THEN [Afbeelding3] WHEN 'Afbeelding4' THEN [Afbeelding4] END LIKE @.seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @.sesdatefrom and [Eindeverhuur] <= @.sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>

|||

Damn didn't knew that this was possible. Thanks a lot mate.

Now I have just to convert some columns in where clause to type of string because my session is a string.
Going to find this out

Thanks again

Sunday, March 11, 2012

[SQL 2005] How do I prevent or hide this warning message?

"Warning: Null value is eliminated by an aggregate or other SET operation."
i keep having this message while having some joint statement.
is there a way to prevent this or even hide this message?Check ANSI_WARNINGS in BoL|||sorry, may I know what is BoL? thanks :)|||bol = books online

it's the documentation for sql server.

[rsRuntimeErrorInExpression] The Value expression for the textbox

Hey All,
I am hoping that I will get a speedy response to this problem. I am working
in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
multivalue. They work fine, with the following exception:
I'm trying to set an expression in a text box on the report, to display back
to the users which parameters they selected prior to viewing the report.
This works fine for non-multivalue parameters. But, with the multivalue
parameters, I keep getting an error about the '&' not being valid. The
report builds fine, it only errors when I try to preview the report.
Here's the expression in my textbox:
="Report Parameters Entered:" & vbCrLf & "Retailer: " &
Parameters!Retailer.Value & vbCrLf & "Month/Year: " & Parameters!Month.Value
& "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
Parameters!Managed.Value
If I change the parameters in this to .IsMultiValue (for the ones that are
multivalue), the report runs fine. But, the actual values selected are not
returned. What shows up in the report is 'True' for each of the multivalue
parameters (because they are multivalue and I'm using the IsMultiValue?).
Anyway, if anyone knows of a work around for this, or if I am just way off
base...PLEASE let me know as soon as possible.
Thanks so much for your response!
SandiMaybe this tip helps?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481987&SiteID=1
Kaisa M. Lindahl Lervik
"SandiB" <SandiB@.discussions.microsoft.com> wrote in message
news:C87FE985-C985-4C3F-B047-53338EFF7A41@.microsoft.com...
> Hey All,
> I am hoping that I will get a speedy response to this problem. I am
> working
> in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
> multivalue. They work fine, with the following exception:
> I'm trying to set an expression in a text box on the report, to display
> back
> to the users which parameters they selected prior to viewing the report.
> This works fine for non-multivalue parameters. But, with the multivalue
> parameters, I keep getting an error about the '&' not being valid. The
> report builds fine, it only errors when I try to preview the report.
> Here's the expression in my textbox:
> ="Report Parameters Entered:" & vbCrLf & "Retailer: " &
> Parameters!Retailer.Value & vbCrLf & "Month/Year: " &
> Parameters!Month.Value
> & "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
> Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
> Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
> Parameters!Managed.Value
> If I change the parameters in this to .IsMultiValue (for the ones that are
> multivalue), the report runs fine. But, the actual values selected are
> not
> returned. What shows up in the report is 'True' for each of the
> multivalue
> parameters (because they are multivalue and I'm using the IsMultiValue?).
> Anyway, if anyone knows of a work around for this, or if I am just way off
> base...PLEASE let me know as soon as possible.
> Thanks so much for your response!
> Sandi|||Thanks so much! This worked perfectly!
"Kaisa M. Lindahl Lervik" wrote:
> Maybe this tip helps?
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481987&SiteID=1
> Kaisa M. Lindahl Lervik
> "SandiB" <SandiB@.discussions.microsoft.com> wrote in message
> news:C87FE985-C985-4C3F-B047-53338EFF7A41@.microsoft.com...
> > Hey All,
> >
> > I am hoping that I will get a speedy response to this problem. I am
> > working
> > in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
> > multivalue. They work fine, with the following exception:
> >
> > I'm trying to set an expression in a text box on the report, to display
> > back
> > to the users which parameters they selected prior to viewing the report.
> > This works fine for non-multivalue parameters. But, with the multivalue
> > parameters, I keep getting an error about the '&' not being valid. The
> > report builds fine, it only errors when I try to preview the report.
> >
> > Here's the expression in my textbox:
> >
> > ="Report Parameters Entered:" & vbCrLf & "Retailer: " &
> > Parameters!Retailer.Value & vbCrLf & "Month/Year: " &
> > Parameters!Month.Value
> > & "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
> > Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
> > Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
> > Parameters!Managed.Value
> >
> > If I change the parameters in this to .IsMultiValue (for the ones that are
> > multivalue), the report runs fine. But, the actual values selected are
> > not
> > returned. What shows up in the report is 'True' for each of the
> > multivalue
> > parameters (because they are multivalue and I'm using the IsMultiValue?).
> >
> > Anyway, if anyone knows of a work around for this, or if I am just way off
> > base...PLEASE let me know as soon as possible.
> >
> > Thanks so much for your response!
> >
> > Sandi
>
>|||'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\ISOJC111\ISOJC\bin\Debug\ISOJC.vshost.exe', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\10.2.3600.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\CrystalDecisions.Enterprise.Framework\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Enterprise.Framework.dll', No symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\CrystalDecisions.Enterprise.InfoStore\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Enterprise.InfoStore.dll', No symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.ReportSource\10.2.3600.0__692fbea5521e1304\CrystalDecisions.ReportSource.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Shared\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Shared.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Windows.Forms\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.Common\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.WinForms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic.PowerPacks\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.PowerPacks.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Deployment\2.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 0x12ac has exited with code 0 (0x0).
The thread 0x1650 has exited with code 0 (0x0).
The thread 0xdc has exited with code 0 (0x0).
'ISOJC.vshost.exe' (Managed): Loaded 'C:\ISOJC111\ISOJC\bin\Debug\ISOJC.exe', Symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0__b77a5c561934e089\System.Runtime.Remoting.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.ProcessingObjectModel\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.ProcessingObjectModel.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'expression_host_10e7fe2ca2834f48ab7700ebecf77268', No symbols loaded.
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0x1730 has exited with code 0 (0x0).
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0x380 has exited with code 0 (0x0).
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0xd24 has exited with code 0 (0x0).
The thread 0x17ec has exited with code 0 (0x0).
The thread 0x133c has exited with code 0 (0x0).
The program '[5820] ISOJC.vshost.exe: Managed' has exited with code 0 (0x0).
From http://www.google.co.in/search?q=rsRuntimeErrorInExpression&hl=en&start=10&sa=N
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type t

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type that is not valid.

[rsInvalidDatabaseImage] The Value expression for the image ‘image1’ did not evaluate to an image.

Hi How should i solve this problem

Regards

KAren

Try this out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=967272&SiteID=1

|||

thanks Greg,

got it to work.. Set the image as an external....

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregat

I put this into a textbox, why isn't this possible?

=SUM(ReportItems!GrossGoal1.Value)

I need to do this!!!!

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsMissingAggregateScope] The Value expression for the textbox ‘textbox6’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.

Build complete -- 2 errors, 0 warnings

I also need to be able to accomplish the same thing. Is there any way to get this done? I have been searching for an answer for a while now, and I can't seem to find anything.

Thanks

|||

Anyone find a solution to this problem?

thanks..

dd

|||There isn't one, Microsoft said they are working on a fix for it in future editions of SSRS, rather an enhancement. We'll see if they ever get to it.|||

Is there another way to get real time row totals in excel without ReportItems? So, if the user changed a value in an excel cell it updates to the total in the spreadsheet (not in the database of course). I got it working for column totals but it's not working for rows. Partly because of the aggregation issue; but, also due to the fact this would have to be more dynamic than the columns one. Anyways, here is an example of what I am talking about:

t < got this working

1 1 1 | 3

1 2 3 | 5

2 3 4 | 8

^^isnt working

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggr

I put this into a textbox, why isn't this possible?

=SUM(ReportItems!GrossGoal1.Value)

I need to do this!!!!

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsMissingAggregateScope] The Value expression for the textbox ‘textbox6’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.

Build complete -- 2 errors, 0 warnings

I also need to be able to accomplish the same thing. Is there any way to get this done? I have been searching for an answer for a while now, and I can't seem to find anything.

Thanks

|||

Anyone find a solution to this problem?

thanks..

dd

|||There isn't one, Microsoft said they are working on a fix for it in future editions of SSRS, rather an enhancement. We'll see if they ever get to it.|||

Is there another way to get real time row totals in excel without ReportItems? So, if the user changed a value in an excel cell it updates to the total in the spreadsheet (not in the database of course). I got it working for column totals but it's not working for rows. Partly because of the aggregation issue; but, also due to the fact this would have to be more dynamic than the columns one. Anyways, here is an example of what I am talking about:

t < got this working

1 1 1 | 3

1 2 3 | 5

2 3 4 | 8

^^isnt working

Thursday, March 8, 2012

[newbie] SQL-query with SUM() doesnt work. :(

Hi NG,

I have two tables:

TABLE1 (id1 INTEGER NOT NULL, id2 INTEGER NOT NULL, value INTEGER, PRIMARY KEY (id1, id2))

TABLE2 (id1 INTEGER NOT NULL, text CHAR(4), PRIMARY KEY (id1))

And this is the query, that I'm trying to run on this tables:

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

Well - it doesn't work at all! :(
That is the reason?!

Appreciate everyone's help!!!

S.B.well when doing aggregates you must have everything in the select statement that is not being summed in the group by clause.

Example:

select t1.id1, t2.text, sum(t1.value)
from table1 t1, table2 t2
where t1.id1 = t2.id1
group by t1.id1, t2.text
order by t1.id1 asc|||Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||Thanks, mkkmg and alligatorsql.com !!! :)

"group by TABLE1.id1, TABLE2.text" works perfectly!

S.B.

Originally posted by alligatorsql.com
Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

Tuesday, March 6, 2012

[Microsoft][SQLServer 2000 Driver for JDBC]Row update failed

I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row update
failed" while updating an int value on result set. Below is the code snippet
that i use in java. The error is occurring occasionally. Not able to
replicate.
strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
PreparedStatement objPS = null;
...get the connection object, prepare object...using msjdbc driver with SQL
server 2000
ResultSet objRS = objPS.executeQuery();
objRS.updateInt("intCol", 2);
objRS.updateRow();
any help is appreciated.
Thanks in advance.
| Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
| X-WBNR-Posting-Host: 152.160.15.58
| From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
| Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| Date: Fri, 25 Feb 2005 07:33:03 -0800
| Lines: 15
| Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
update
| failed" while updating an int value on result set. Below is the code
snippet
| that i use in java. The error is occurring occasionally. Not able to
| replicate.
|
| strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
| PreparedStatement objPS = null;
| ...get the connection object, prepare object...using msjdbc driver with
SQL
| server 2000
| ResultSet objRS = objPS.executeQuery();
| objRS.updateInt("intCol", 2);
| objRS.updateRow();
|
| any help is appreciated.
| Thanks in advance.
|
What is the frequency of this problem? Did the problem occur while other
activity the same database was occurring? Do you have a primary key
defined on the table? Which options did you use when preparing the
statement? Are you using the most recent build of the JDBC driver?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hi Carb, thanks for your reply, here is my answers for your questions. Hope
that will help us to resolve the issue.
1. What is the frequency of this problem? - not able to determine,
occurring occasinoaly.
2. Did the problem occur while other activity the same database was
occurring? - I couldn't make sure it 100% though, i am possitive that there
could be some other activity on the database.
3. Do you have a primary key defined on the table? - Yes e.g. table script
tbl1(col1 varchar(100) NOT NULL, col2 int NULL)
here: col1 is primary key - but i am trying to update col2
4. Which options did you use when preparing the statement? - here is e.g.
objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY)
5. Are you using the most recent build of the JDBC driver? MS-JDBC SP2
thanks
-Saran
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
> | thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
> | X-WBNR-Posting-Host: 152.160.15.58
> | From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
> | Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
> | Date: Fri, 25 Feb 2005 07:33:03 -0800
> | Lines: 15
> | Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
> update
> | failed" while updating an int value on result set. Below is the code
> snippet
> | that i use in java. The error is occurring occasionally. Not able to
> | replicate.
> |
> | strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
> | PreparedStatement objPS = null;
> | ...get the connection object, prepare object...using msjdbc driver with
> SQL
> | server 2000
> | ResultSet objRS = objPS.executeQuery();
> | objRS.updateInt("intCol", 2);
> | objRS.updateRow();
> |
> | any help is appreciated.
> | Thanks in advance.
> |
> What is the frequency of this problem? Did the problem occur while other
> activity the same database was occurring? Do you have a primary key
> defined on the table? Which options did you use when preparing the
> statement? Are you using the most recent build of the JDBC driver?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||saran wrote:
> 4. Which options did you use when preparing the statement? - here is
e.g.
> objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY)
And if the ResultSet was created as read only how do you expect
updateRow() to work? Speaking of which, if the error message would have
been "General error" or simply "Error", it would have been just as easy
to track down the cause.
Alin,
The jTDS Project.
|||| Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| thread-index: AcUpfTtxzBvD5vNlRPaQXIKezzsWHg==
| X-WBNR-Posting-Host: 152.160.15.58
| From: "=?Utf-8?B?c2FyYW4=?=" <saran@.discussions.microsoft.com>
| References: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
<zQR4wCMKFHA.1136@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| Date: Tue, 15 Mar 2005 08:37:09 -0800
| Lines: 86
| Message-ID: <4F9F2476-D4EE-4EA1-8288-B9C2EB8D5A30@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6756
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi Carb, thanks for your reply, here is my answers for your questions.
Hope
| that will help us to resolve the issue.
|
| 1. What is the frequency of this problem? - not able to determine,
| occurring occasinoaly.
|
| 2. Did the problem occur while other activity the same database was
| occurring? - I couldn't make sure it 100% though, i am possitive that
there
| could be some other activity on the database.
|
| 3. Do you have a primary key defined on the table? - Yes e.g. table script
| tbl1(col1 varchar(100) NOT NULL, col2 int NULL)
| here: col1 is primary key - but i am trying to update col2
|
| 4. Which options did you use when preparing the statement? - here is e.g.
| objCon.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE,
| ResultSet.CONCUR_READ_ONLY)
|
| 5. Are you using the most recent build of the JDBC driver? MS-JDBC SP2
|
| thanks
| -Saran
|
| ""Carb Simien [MSFT]"" wrote:
|
| >
| > --
| > | Thread-Topic: [Microsoft][SQLServer 2000 Driver for JDBC]Row update
failed
| > | thread-index: AcUbT0uzY+NiEZNfSCeDqAYi6cibWQ==
| > | X-WBNR-Posting-Host: 152.160.15.58
| > | From: "=?Utf-8?B?U2FyYW4=?=" <Saran@.discussions.microsoft.com>
| > | Subject: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed
| > | Date: Fri, 25 Feb 2005 07:33:03 -0800
| > | Lines: 15
| > | Message-ID: <05BEC007-4670-4D11-9C3F-E46F19A60BDC@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT NGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6702
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | I am getting an error "[Microsoft][SQLServer 2000 Driver for JDBC]Row
| > update
| > | failed" while updating an int value on result set. Below is the code
| > snippet
| > | that i use in java. The error is occurring occasionally. Not able to
| > | replicate.
| > |
| > | strSQL = "SELECT intCol FROM Table1 WHERE intCol = 1";
| > | PreparedStatement objPS = null;
| > | ...get the connection object, prepare object...using msjdbc driver
with
| > SQL
| > | server 2000
| > | ResultSet objRS = objPS.executeQuery();
| > | objRS.updateInt("intCol", 2);
| > | objRS.updateRow();
| > |
| > | any help is appreciated.
| > | Thanks in advance.
| > |
| >
| > What is the frequency of this problem? Did the problem occur while
other
| > activity the same database was occurring? Do you have a primary key
| > defined on the table? Which options did you use when preparing the
| > statement? Are you using the most recent build of the JDBC driver?
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
| >
| >
|
Yes, you will need to use Resultset.CONCUR_UPDATABLE to allow the updates..
Also, the most recent build of the Microsoft JDBC driver is JDBC SP3
(build 2.2.0040):
SQL Server 2000 Driver for JDBC Service Pack 3
http://www.microsoft.com/downloads/d...B11-0502-461A-
B138-2AA54BFDC03A&displaylang=en
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Sunday, February 19, 2012

[Execute SQL Task] Error: An error occurred while assigning a value to variable...

hi chaps

i m getting the following ERROR:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "JDETimezone": "Unable to find column Timezone in the result set.".

i know what the problem is i.e. no row is returned then what is the problem

here you are.... i want to it work... strange... ok i explain...

actully i have some processign to do with variable JDETimezone even no row is returned.... can u tell me the alternative to do the follwing task...

I want to retrieve a record from some table and do some processing and if no row is present or returned then i want to do seperate processing.... can ne one help me out ?

regards,

Anas

That error usually occurs when you either don't have a column in your select statement named "Timezone", or you didn't name the resultset Timezone. You may want to check your settings.

If you want to do separate processing when no rows are returned then in the Error Flow redirect the rows instead of Failing or Ignoring them.

|||

thanx for your reply

but i sure I have both, column name Timezone in Select statement as well as in Resultset...

problem is that it doesnt return any row and causes error...Sean you are right I can use Error flow but the problem that i dont want any of my task to fail becuase i m doing error handling by Task failure event which then email the failure information to admin group

does ne one have solution of my problem?

regards,

Anas

|||

One way to implement different processing logic based on the result set row count is by having a Execute SQL task that counts the total number of rows returned by a query and assign the count to a variable which can later be used to determine the control flow.

See http://msdn2.microsoft.com/en-US/library/ms140153.aspx

[Execute SQL Task] Error: An error occurred while assigning a value to variable

hello
I have a problem with Sql task
when sql task tried to assing a value to my variable I have this error ""La valeur n'est pas comprise dans la plage attendue."
I'm using ODBC connexion for a csv file

someone can help me ?

thanks
It might help if you translated the error message to english.|||

we can translate as " the value is not included in the correct space " or something like that

|||

Take a look at this - it explains how to set up the Execute SQL task for retrieving values.

http://www.sqlis.com/58.aspx

Thursday, February 16, 2012

[DB2] Update Field in Table A to a Field in Table B

I am trying to change the values in Field2 of table A to the values in Field2 of table B wherever the tables have the same value for Field1.

This works in Microsoft Access, but I can't figure out how to make it work in DB2.

UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];

Any ideas?I know this works in Oracle, but not tested in DB2...

UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,

Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:

update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )

...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.|||thanks, joe, i understand that

i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity

i'd use a WHERE clause to ensure that only those rows which had a match are actually updated|||Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.

[ask] sqlserver getdate() function doesnt return seconds value

i have a quite strange condition...

when i add some value in database with getdate() function it only returns date and minute not the seconds...

does somebody have an experience about this

What is the data type of the variable or column that you are using? If it is smaldatetime, the accuracy is only of minutes. If you want seconds, you'll need to use datetime.

If you're doing something else, post your code to see if we can help you figure it out.

Don

|||

getdate() actually returns current date & time. Just try to select getdate() and you will see. You must have assign the value of getdate() to smalldatetime.|||

I've changed to datetime and it works :)

weew... i never thought about it... just a little mistake there :)

thx anyway

Monday, February 13, 2012

@XMLVariable.query

How do I get the value of ID when an attribute is part of the XML? The
following script shows the problem. It should be ready to execute,
depending on wrapping. Test1 works; test2 and test3 have an attribute, and
do not work.
Thanks,
Richard
set nocount on
use tempdb
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet>
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet
xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
goDoes this help?
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'declare default element namespace
"http://tempuri.org/DataSet.xsd";
data( /DataSet/Request/ID )' ) as Test2 ;
with xmlnamespaces(default 'http://tempuri.org/DataSet.xsd' )
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2|||Your xml document is using a default namespace which must be defined somewhe
re.
Here is an example of one way to do it, note the use of the semicolon.
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>';
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/DataSet.xsd')
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
Dan

> How do I get the value of ID when an attribute is part of the XML?
> The following script shows the problem. It should be ready to
> execute, depending on wrapping. Test1 works; test2 and test3 have an
> attribute, and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3 go
>|||Perfect! Thanks for the quick replies.
"Richard" <napa299@.yahoo.com> wrote in message
news:%23c%23EM$miGHA.4284@.TK2MSFTNGP05.phx.gbl...
> How do I get the value of ID when an attribute is part of the XML? The
> following script shows the problem. It should be ready to execute,
> depending on wrapping. Test1 works; test2 and test3 have an attribute,
> and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
> go
>

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything come
s
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W1Month1Indicator
)[1]' , 'char(1)' ) as P6_W1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1
]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanksKarl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instanc
e
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything co
mes
> as NULL), however if i manually take out toe xmlns part of the xml, it wor
ks
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) a
s
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) A
S
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' )
as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.W1Month1Indicat
or)[1]' , 'char(1)' ) as P6_W1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]
'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)
[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes fro
m
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime'
)
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) a
s
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal
?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the detail
s
> thanks again

@xml.value against XML data with namespaces

i have some XML, that i am using .value to get the values from, when the
namespa e is included in the XML(as below ) it doesn't work (everything comes
as NULL), however if i manually take out toe xmlns part of the xml, it works
fine. i'm just wondering how i can change my .value code to work with the
namespace..
declare @.myxml XML
set @.myxml =
'<CodingNoticesP6P6B
xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
xmlns:gt="http://www.govtalk.gov.uk/CM/core"
xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
<EmployerRef>961/1791574</EmployerRef>
<Name>
<Title>AA</Title>
<Forename>AA</Forename>
<Surname>AA</Surname>
</Name>
<NINO>AA000000A</NINO>
<WorksNumber>WN001</WorksNumber>
<EffectiveDate>2005-09-01Z</EffectiveDate>
<CodingUpdate>
<TaxCode>10A</TaxCode>
<TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
<TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
</CodingUpdate>
</CodingNoticesP6P6B>'
--doesn't work when namespace is in, need to make the query work with the
namespace somehow
select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
P6_IssueDate,
@.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
P6_EmployerRef,
@.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
P6_Title,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
as P6_ForeName,
@.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
as P6_ForeName_2,
@.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
P6_Surname,
@.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
@.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
as P6_WorksNumber,
@.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
P6_EffectiveDate,
@.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
P6_FormType,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
'varchar(5)' ) as P6_TaxCode,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
, 'numeric(9,2)' ) as P6_TotalPreviousPay,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
'numeric(9,2)' ) as P6_TotalPreviousTax,
@.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
thanks
Karl,
The query processor only has several builtin namespace/prefix mappings
builtin. You need to supply these additional mappings since each xml instance
that the query runs against may have their own namespace/prefix mappings.
There are two way to create this mapping. One is to use the "declare" clause
in a value or query method. For example:
select @.myxml.value('declare namespace ns1 =
"http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2";
(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as P6_IssueDate
But, since you have many value methods, you can also use the WITH
XMLNAMESPACES clause before the SELECT statement. This defines
namespace/prefix mappings for all value and query methods within a single
select statement. See BOL for me details.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as ns1)
select @.myxml.value('(/ns1:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate
You will need to place the correct prefix in front of each element name
within your xpath expressions. In the example you gave, all the elements use
the default namespace.
Note: You should place a semicolon after the statement that precedes the
WITH clause. In your case, the set statement should have ; at the end.
Regards,
Galex Yen
"Karl Prosser" wrote:

> i have some XML, that i am using .value to get the values from, when the
> namespa e is included in the XML(as below ) it doesn't work (everything comes
> as NULL), however if i manually take out toe xmlns part of the xml, it works
> fine. i'm just wondering how i can change my .value code to work with the
> namespace..
> declare @.myxml XML
> set @.myxml =
> '<CodingNoticesP6P6B
> xmlns="http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2"
> xmlns:gt="http://www.govtalk.gov.uk/CM/core"
> xmlns:gms="http://www.govtalk.gov.uk/CM/gms-xs" IssueDate="2005-09-01"
> TaxYearEnd="2005" SequenceNumber="1" FormType="P6B">
> <EmployerRef>961/1791574</EmployerRef>
> <Name>
> <Title>AA</Title>
> <Forename>AA</Forename>
> <Surname>AA</Surname>
> </Name>
> <NINO>AA000000A</NINO>
> <WorksNumber>WN001</WorksNumber>
> <EffectiveDate>2005-09-01Z</EffectiveDate>
> <CodingUpdate>
> <TaxCode>10A</TaxCode>
> <TotalPreviousPay Currency="GBP">5000.00</TotalPreviousPay>
> <TotalPreviousTax Currency="GBP">1000.00</TotalPreviousTax>
> </CodingUpdate>
> </CodingNoticesP6P6B>'
> --doesn't work when namespace is in, need to make the query work with the
> namespace somehow
> select @.myxml.value('(/CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' ) as
> P6_IssueDate,
> @.myxml.value('(/CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(CodingNoticesP6P6B/EmployerRef)[1]' , 'varchar(14)' ) AS
> P6_EmployerRef,
> @.myxml.value('(CodingNoticesP6P6B/Name/Title)[1]' , 'varchar(4)' ) as
> P6_Title,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[1]' , 'varchar(35)' )
> as P6_ForeName,
> @.myxml.value('(CodingNoticesP6P6B/Name/Forename)[2]' , 'varchar(35)' )
> as P6_ForeName_2,
> @.myxml.value('(CodingNoticesP6P6B/Name/Surname)[1]' , 'varchar(35)' ) as
> P6_Surname,
> @.myxml.value('(CodingNoticesP6P6B/NINO)[1]' , 'char(9)' ) as P6_NINO,
> @.myxml.value('(CodingNoticesP6P6B/WorksNumber)[1]' , 'varchar(35)' )
> as P6_WorksNumber,
> @.myxml.value('(CodingNoticesP6P6B/EffectiveDate)[1]' , 'datetime' ) as
> P6_EffectiveDate,
> @.myxml.value('(CodingNoticesP6P6B/@.FormType)[1]' , 'varchar(3)' ) as
> P6_FormType,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode)[1]' ,
> 'varchar(5)' ) as P6_TaxCode,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TaxCode/@.Week1Month1Indicator)[1]' , 'char(1)' ) as P6_Week1Month1Indicator,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay)[1]'
> , 'numeric(9,2)' ) as P6_TotalPreviousPay,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousPay/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousPay_Currency,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax)[1]' ,
> 'numeric(9,2)' ) as P6_TotalPreviousTax,
> @.myxml.value('(CodingNoticesP6P6B/CodingUpdate/TotalPreviousTax/@.Currency)[1]' , 'char(3)' ) as P6_TotalPreviousTax_Currency
> thanks
|||thank you very much, i appreciate your help and feedback
here is an example of what i am doing now.
WITH XMLNAMESPACES
('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
as P6_IssueDate,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
P6_TaxYearEnd,
@.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
P6_SequenceNumber,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
'varchar(14)' ) AS P6_EmployerRef,
@.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
'varchar(4)' ) as P6_Title,
i noticed that in multilevel parts i have to put p6 at every level (i.e
codingbase, name and title, as shown above for it to work.. Is this Normal?
shouldn't it be able to know from the first one? Of course i can get it
working with the p6 and that makes me happy. jUst curious about the details
thanks again
|||Karl,
The reason the prefix must be specified over and over again is simply an
artifact of xml+namespaces. An xml document may have elements/attributes from
multiple namespaces and within those namespace there could be name
collisions. An element name is not really just the name part, it is also the
namespace it is defined in.
That being said, in your case, since only one namespace is being used, you
can define the default namespace (no prefix) throughout your query. This can
be done like this:
WITH XMLNAMESPACES (DEFAULT 'http://your.uri')
SELECT xmlcol.value('/foo[1]', 'int')
In this case '/foo' really means {http://your.uri}:foo. Note: this isn't
really xml syntax, just an illustration.
Regards,
Galex Yen
"Karl Prosser" wrote:

> thank you very much, i appreciate your help and feedback
> here is an example of what i am doing now.
> WITH XMLNAMESPACES
> ('http://www.govtalk.gov.uk/taxation/CodingNoticesP6P6B/2' as p6)
> select @.myxml.value('(/p6:CodingNoticesP6P6B/@.IssueDate)[1]' , 'datetime' )
> as P6_IssueDate,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.TaxYearEnd)[1]', 'char(4)' ) as
> P6_TaxYearEnd,
> @.myxml.value('(/p6:CodingNoticesP6P6B/@.SequenceNumber)[1]' , 'int' ) as
> P6_SequenceNumber,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:EmployerRef)[1]' ,
> 'varchar(14)' ) AS P6_EmployerRef,
> @.myxml.value('(/p6:CodingNoticesP6P6B/p6:Name/p6:Title)[1]' ,
> 'varchar(4)' ) as P6_Title,
> i noticed that in multilevel parts i have to put p6 at every level (i.e
> codingbase, name and title, as shown above for it to work.. Is this Normal?
> shouldn't it be able to know from the first one? Of course i can get it
> working with the p6 and that makes me happy. jUst curious about the details
> thanks again

Saturday, February 11, 2012

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
Tom
Hi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
TomHi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>|||Hi Hari,
I have the same scenario as below, and I have even try to do two times
server restart also, but @.@.servername still returns Null to me, which is
different result from SERVERNAME property of SERVERPROPERTY function. Any
idea what else we can try? Thanks in advance!
Regards,
JC
"Hari Prasad" wrote:
> Hi,
> Try this
>
> sp_dropserver 'XYZ'
> go
> sp_addserver 'XYZ', 'local'
> go
> After this stop and start the MS SQL Server service. Then login in Query
> analyzer and execute SELECT @.@.SERVERNAME
> Thanks
> Hari
> SQL Server MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> > We are running SQL 2000 in a non-clustered server. For some reason when
> > we
> > select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> > ...
> > the information in returned by @.@.SERVERNAME may be different than the
> > SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> > automatically reports changes in the network name of the computer. In
> > contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> > changes made to the local server name using the sp_addserver or
> > sp_dropserver stored procedure
> >
> > Assuming that my server name is XYZ - I would assume that by executing the
> > following command we would be OK:
> >
> > use master
> > go
> >
> > sp_addserver 'XYZ', 'local'
> > go
> >
> > But when executing this - we get the following message:
> > Server already exists.
> >
> > Can someone advise the correct way to do this?
> >
> > Thanks,
> > Tom
> >
> >
> >
> >
> >
> >
>
>

@@SERVERNAME returns NULL value... Can we adjust so that it returns the server name

We are running SQL 2000 in a non-clustered server. For some reason when we
select @.@.SERVERNAME we are getting a NULL value back. According to BOL ...
the information in returned by @.@.SERVERNAME may be different than the
SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
automatically reports changes in the network name of the computer. In
contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
changes made to the local server name using the sp_addserver or
sp_dropserver stored procedure
Assuming that my server name is XYZ - I would assume that by executing the
following command we would be OK:
use master
go
sp_addserver 'XYZ', 'local'
go
But when executing this - we get the following message:
Server already exists.
Can someone advise the correct way to do this?
Thanks,
TomHi,
Try this
sp_dropserver 'XYZ'
go
sp_addserver 'XYZ', 'local'
go
After this stop and start the MS SQL Server service. Then login in Query
analyzer and execute SELECT @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"TJT" <TJT@.nospam.com> wrote in message
news:O9QwWELiFHA.1244@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 in a non-clustered server. For some reason when
> we
> select @.@.SERVERNAME we are getting a NULL value back. According to BOL
> ...
> the information in returned by @.@.SERVERNAME may be different than the
> SERVERNAME property of SERVERPROPERTY function. The SERVERNAME property
> automatically reports changes in the network name of the computer. In
> contrast, @.@.SERVERNAME does not report such changes. @.@.SERVERNAME reports
> changes made to the local server name using the sp_addserver or
> sp_dropserver stored procedure
> Assuming that my server name is XYZ - I would assume that by executing the
> following command we would be OK:
> use master
> go
> sp_addserver 'XYZ', 'local'
> go
> But when executing this - we get the following message:
> Server already exists.
> Can someone advise the correct way to do this?
> Thanks,
> Tom
>
>
>

Thursday, February 9, 2012

@@servername returns NULL value

I have a SQL 2005 clustered server which is returning a Null value for @.@.servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @.@.servername which returns a null value. So I am struck in a loop.

Any advice is appreciated.

thanks

For a machine to be considered local, srvid = 0 in sysservers. Can you check on this?

Can a simple reboot of the nodes fix the problem? Otherwise how did you get into this state, considering you have replication set up properly already?

|||

Hi,

Have you rename the virtual server name or the participating node name?

Peng

@@servername returning NULL

I have a SQL 2005 clustered server which is returning a Null value for @.@.servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @.@.servername which returns a null value. So I am struck in a loop.

Any advice is appreciated.

thanks

@.@.Servername of null can happen if there is no entry in sys.servers for server_id 0. check sys.servers with this:

select * from sys.servers where server_id=0

If this returns 0 rows you can add the local server with

sp_addserver '<servername>', local

you will need to restart the server for this to take effect.