I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)
I created a column in my details table with with ID of the primary key in the master table.
The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.
Not sure why the primary key ID isn't inserted as a foreign key into my details table?
Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?
ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.
Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?
Thanks for your help! I appreciate it.
-Dman100-
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Thursday, March 29, 2012
Foreach variable cannot be applied
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2, str2, nValue1, nValue2
FROM MyTable
In the SQL Task I've set the following
ResultSet = Full Result Set
Connection Type = OLE DB
Result Name = 0
I've created the following Variables with a Package scope
s1 as String
s2 as String
s3 as String
n1 as Double
n2 as Double
When I run the package I get the following errors
Foreach variable mapping number 4 to variable "User : : n1" cannot be applied
Foreach variable mapping number 5 to variable "User : : n2" cannot be applied
The data type of nValue1 and nValue2 in MyTable are both numeric(19,4)
In setting the variable types in the package it seems that the closest to numeric(19,4) would be double. Is this why it is failing? Is there any way around it? I just need to pass the variables which are numeric and have decimal places.
Any insight would be greatly appreciated.
Regards,
Bill
See if this blog post helps you:
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html
|||Thanks for the pointer, I found that if I cast the numeric fields in my query to float that the mapping seemed to pass the variables ok.
Regards,
Bill
Monday, March 26, 2012
Forcing matrix static elements to display despite empty record set
I created a nice matrix report and it works great when the parameter I pass
returns some records, however when no records are returned the matrix simply
does not render at all leaving a big empty hole where that part of the report
should appear.
The matrix has static row heading that I want to always appear even if no
data appears in the columns, even better if there was a way to set a default
value to the columns in case no data is returned say 0s. This is an example
of my matrix.
Static Heading
Column Group
Cars
people
animals
So, Cars, People, and Animals are static row headings and the values fill in
next to them for the column groupings. Now, if no records are returned I
would still like to have the static column heading and static row heading to
appear even if there is no data to display. How is that done?
ThanksTry setting something in the NoRows property - like display a message "no
data for this timeframe" or something and I think that will make your
headings show in addition to the message. I have used this for tables ...
havent tried it with a matrix but I would think it should work.
"Ramez" wrote:
> I created a nice matrix report and it works great when the parameter I pass
> returns some records, however when no records are returned the matrix simply
> does not render at all leaving a big empty hole where that part of the report
> should appear.
> The matrix has static row heading that I want to always appear even if no
> data appears in the columns, even better if there was a way to set a default
> value to the columns in case no data is returned say 0s. This is an example
> of my matrix.
> Static Heading
> Column Group
> Cars
> people
> animals
>
> So, Cars, People, and Animals are static row headings and the values fill in
> next to them for the column groupings. Now, if no records are returned I
> would still like to have the static column heading and static row heading to
> appear even if there is no data to display. How is that done?
> Thanks
>
>
>
returns some records, however when no records are returned the matrix simply
does not render at all leaving a big empty hole where that part of the report
should appear.
The matrix has static row heading that I want to always appear even if no
data appears in the columns, even better if there was a way to set a default
value to the columns in case no data is returned say 0s. This is an example
of my matrix.
Static Heading
Column Group
Cars
people
animals
So, Cars, People, and Animals are static row headings and the values fill in
next to them for the column groupings. Now, if no records are returned I
would still like to have the static column heading and static row heading to
appear even if there is no data to display. How is that done?
ThanksTry setting something in the NoRows property - like display a message "no
data for this timeframe" or something and I think that will make your
headings show in addition to the message. I have used this for tables ...
havent tried it with a matrix but I would think it should work.
"Ramez" wrote:
> I created a nice matrix report and it works great when the parameter I pass
> returns some records, however when no records are returned the matrix simply
> does not render at all leaving a big empty hole where that part of the report
> should appear.
> The matrix has static row heading that I want to always appear even if no
> data appears in the columns, even better if there was a way to set a default
> value to the columns in case no data is returned say 0s. This is an example
> of my matrix.
> Static Heading
> Column Group
> Cars
> people
> animals
>
> So, Cars, People, and Animals are static row headings and the values fill in
> next to them for the column groupings. Now, if no records are returned I
> would still like to have the static column heading and static row heading to
> appear even if there is no data to display. How is that done?
> Thanks
>
>
>
Friday, March 23, 2012
ForceEncryption Parameter
Hi All,
I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.
I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.
Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.
But, I do get the following error when I set Encrypt = Yes
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
and, If I set Encrypt=No, I do not get any error again.
My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?
Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.
Could you please help me understand the results of various options like this?
Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
The Encrypt = No setting again does not give any error.
Thanks and Regards,
-Ajay Kumar Suri
Hi Ajay,
The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
Il-Sung.
|||Hi,
Thanks very much for your reply.
Could you please verify if my understanding now is correct for SQL Server 2005.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
I hope I have understood it correctly.
Please advise.
Best Regards,
-Ajay Kumar Suri
|||Hi Ajay,
Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
> Correct.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
> Correct, although the login packet will always be encrypted.
Hope this clears things up.
Il-Sung.
|||Hi Sung,
Thanks very much for your comments.
It does clear a lot of things for me.
Best Regards,
-Ajay Kumar Suri
ForceEncryption Parameter
Hi All,
I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.
I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.
Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.
But, I do get the following error when I set Encrypt = Yes
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
and, If I set Encrypt=No, I do not get any error again.
My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?
Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.
Could you please help me understand the results of various options like this?
Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
The Encrypt = No setting again does not give any error.
Thanks and Regards,
-Ajay Kumar Suri
Hi Ajay,
The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
Il-Sung.
|||Hi,
Thanks very much for your reply.
Could you please verify if my understanding now is correct for SQL Server 2005.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
I hope I have understood it correctly.
Please advise.
Best Regards,
-Ajay Kumar Suri
|||Hi Ajay,
Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
> Correct.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
> Correct, although the login packet will always be encrypted.
Hope this clears things up.
Il-Sung.
|||Hi Sung,
Thanks very much for your comments.
It does clear a lot of things for me.
Best Regards,
-Ajay Kumar Suri
ForceEncryption Parameter
Hi All,
I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.
I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.
Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.
But, I do get the following error when I set Encrypt = Yes
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
and, If I set Encrypt=No, I do not get any error again.
My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?
Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.
Could you please help me understand the results of various options like this?
Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
The Encrypt = No setting again does not give any error.
Thanks and Regards,
-Ajay Kumar Suri
Hi Ajay,
The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
Il-Sung.
|||Hi,
Thanks very much for your reply.
Could you please verify if my understanding now is correct for SQL Server 2005.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
I hope I have understood it correctly.
Please advise.
Best Regards,
-Ajay Kumar Suri
|||Hi Ajay,
Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
> Correct.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
> Correct, although the login packet will always be encrypted.
Hope this clears things up.
Il-Sung.
|||Hi Sung,
Thanks very much for your comments.
It does clear a lot of things for me.
Best Regards,
-Ajay Kumar Suri
ForceEncryption Parameter
Hi All,
I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.
I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.
Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.
But, I do get the following error when I set Encrypt = Yes
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
and, If I set Encrypt=No, I do not get any error again.
My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?
Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.
Could you please help me understand the results of various options like this?
Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
The Encrypt = No setting again does not give any error.
Thanks and Regards,
-Ajay Kumar Suri
Hi Ajay,
The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
Il-Sung.
|||Hi,
Thanks very much for your reply.
Could you please verify if my understanding now is correct for SQL Server 2005.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
I hope I have understood it correctly.
Please advise.
Best Regards,
-Ajay Kumar Suri
|||Hi Ajay,
Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
> Correct.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
> Correct, although the login packet will always be encrypted.
Hope this clears things up.
Il-Sung.
|||Hi Sung,
Thanks very much for your comments.
It does clear a lot of things for me.
Best Regards,
-Ajay Kumar Suri
Force View || Create View Accessing view that has not been created yet?
Hello All.
Does Sql 2005 support forced-later-compliation? Which is to say, can I create a view that access another view which has not been created yet? e.g. "Create Force View Foo" in Oracle.
Thanks,
Steve
You can only do that for stored procedures. This is known and filed in the BOL under "Deferred Name Resolution".HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
sql
Wednesday, March 21, 2012
Force prompt when using default parameters
I wrote an sp and assigned static values to fields for easy testing, after
that I created the parameters and assigned default values to them, now
everytime I click preview I don't get a prompt for new values, the report
automatically runs with the defaults.
I want to be able to use the default values most of the time but also want
the option to change them if needed. The only option is to stop the report
and enter different values. This is strange because I have other reports that
have all default values but give me a prompt in preview.Someone answered this a few ones back.
Add a boolean "Run Report" parameter to the report with no default.
-T
"Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
>I wrote an sp and assigned static values to fields for easy testing, after
> that I created the parameters and assigned default values to them, now
> everytime I click preview I don't get a prompt for new values, the report
> automatically runs with the defaults.
> I want to be able to use the default values most of the time but also want
> the option to change them if needed. The only option is to stop the report
> and enter different values. This is strange because I have other reports
> that
> have all default values but give me a prompt in preview.|||Cool! Thx Tim
"Tim Dot NoSpam" wrote:
> Someone answered this a few ones back.
> Add a boolean "Run Report" parameter to the report with no default.
> -T
> "Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
> news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
> >I wrote an sp and assigned static values to fields for easy testing, after
> > that I created the parameters and assigned default values to them, now
> > everytime I click preview I don't get a prompt for new values, the report
> > automatically runs with the defaults.
> > I want to be able to use the default values most of the time but also want
> > the option to change them if needed. The only option is to stop the report
> > and enter different values. This is strange because I have other reports
> > that
> > have all default values but give me a prompt in preview.
>
>
that I created the parameters and assigned default values to them, now
everytime I click preview I don't get a prompt for new values, the report
automatically runs with the defaults.
I want to be able to use the default values most of the time but also want
the option to change them if needed. The only option is to stop the report
and enter different values. This is strange because I have other reports that
have all default values but give me a prompt in preview.Someone answered this a few ones back.
Add a boolean "Run Report" parameter to the report with no default.
-T
"Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
>I wrote an sp and assigned static values to fields for easy testing, after
> that I created the parameters and assigned default values to them, now
> everytime I click preview I don't get a prompt for new values, the report
> automatically runs with the defaults.
> I want to be able to use the default values most of the time but also want
> the option to change them if needed. The only option is to stop the report
> and enter different values. This is strange because I have other reports
> that
> have all default values but give me a prompt in preview.|||Cool! Thx Tim
"Tim Dot NoSpam" wrote:
> Someone answered this a few ones back.
> Add a boolean "Run Report" parameter to the report with no default.
> -T
> "Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
> news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
> >I wrote an sp and assigned static values to fields for easy testing, after
> > that I created the parameters and assigned default values to them, now
> > everytime I click preview I don't get a prompt for new values, the report
> > automatically runs with the defaults.
> > I want to be able to use the default values most of the time but also want
> > the option to change them if needed. The only option is to stop the report
> > and enter different values. This is strange because I have other reports
> > that
> > have all default values but give me a prompt in preview.
>
>
Monday, March 19, 2012
Force columns to appear in matrix.
I have created the report below in SSRS to show the total by month for the calendar year.
As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.
As I go to project this report for January 2007 I am having trouble.
I want the report to appear as below with the 12 requisite columns.
But as only data for the first column is returned from my query I only get one column to appear.
How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?
Hi,
One option I can think of is adding some dummy records to the query, using the UNION statement. One for each month of the year, with a value 0.
Regards, Jeroen
|||At the risk of sounding like a total noob. Can you modify the query below or give an example?
SELECT
bo.AS400Billingid,
OrgName,
ih.date,
ih.rev
FROM billingorg bo
INNER JOIN
(SELECT
AS400BillingID,
substring(InvoiceDate,4,2) date,
sum(SalesAmount) rev
FROM
InvoiceHeader
WHERE
left(InvoiceDate,3)='106'
GROUP BY
AS400BillingID,substring(InvoiceDate,4,2)) ih
ON bo.AS400BillingID=ih.AS400BillingID
|||I found a solution.
SELECT
id,
OrgName,
AddedDate,
SalesTerritory,
IndustryCode,
AccountManager,
[01] AS 'JAN',
[02] AS 'FEB',
[03] AS 'MAR',
[04] AS 'APR',
[05] AS 'MAY',
[06] AS 'JUN',
[07] AS 'JUL',
[08] AS 'AUG',
[09] AS 'SEP',
[10] AS 'OCT',
[11] AS 'NOV',
[12] AS 'DEC'
FROM
(SELECT
bo.AS400Billingid id,
bo.OrgName,
bo.AddedDate,
bo.SalesTerritory,
bo.IndustryCode,
bo.AccountManager,
substring(ih.InvoiceDate,4,2) date,
ih.SalesAmount rev
FROM
billingorg bo INNER JOIN InvoiceHeader ih
ON bo.AS400BillingID=ih.AS400BillingID
WHERE
left(InvoiceDate,3)='106') as data
PIVOT
(
sum(rev)
FOR date IN([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
) XTab
Monday, March 12, 2012
For Xml Path, functions and UDF's
I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this
.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UD
F
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work'
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')Ok, just found the issue with the UDF in BOL so I understand why I can't pas
s
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:
> I have created a select statement to return using for xml path which work
s
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing th
is.
> If i include the complete sub query directly in the sql it works without a
ny
> issues, but if I take the sub select and put into into a table returning U
DF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a
UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work'
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
> Ok, just found the issue with the UDF in BOL so I understand why I can't p
ass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see
if
> thay works.
> "Nathan" wrote:
>
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this
.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UD
F
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work'
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')Ok, just found the issue with the UDF in BOL so I understand why I can't pas
s
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:
> I have created a select statement to return using for xml path which work
s
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing th
is.
> If i include the complete sub query directly in the sql it works without a
ny
> issues, but if I take the sub select and put into into a table returning U
DF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a
UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work'
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
> Ok, just found the issue with the UDF in BOL so I understand why I can't p
ass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see
if
> thay works.
> "Nathan" wrote:
>
For Xml Path, functions and UDF's
I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UDF
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work?
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
Ok, just found the issue with the UDF in BOL so I understand why I can't pass
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:
> I have created a select statement to return using for xml path which works
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing this.
> If i include the complete sub query directly in the sql it works without any
> issues, but if I take the sub select and put into into a table returning UDF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work?
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
[vbcol=seagreen]
> Ok, just found the issue with the UDF in BOL so I understand why I can't pass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see if
> thay works.
> "Nathan" wrote:
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UDF
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work?
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
Ok, just found the issue with the UDF in BOL so I understand why I can't pass
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:
> I have created a select statement to return using for xml path which works
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing this.
> If i include the complete sub query directly in the sql it works without any
> issues, but if I take the sub select and put into into a table returning UDF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work?
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
[vbcol=seagreen]
> Ok, just found the issue with the UDF in BOL so I understand why I can't pass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see if
> thay works.
> "Nathan" wrote:
For XML Path problem?
I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporar
y
> table is created with the recordset I wish to call the results as per belo
w
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||And it certainly doesn't support FOR PATH.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:uJYgjngmHHA.960@.TK2MSFTNGP03.phx.gbl...
> Daniel Badger wrote:
> I don't see anything wrong with that snippet, unless you are using SQL
> Server 2000 which does not support the root clause I think as it is a new
> feature only supported in SQL server 2005.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporar
y
> table is created with the recordset I wish to call the results as per belo
w
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||And it certainly doesn't support FOR PATH.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:uJYgjngmHHA.960@.TK2MSFTNGP03.phx.gbl...
> Daniel Badger wrote:
> I don't see anything wrong with that snippet, unless you are using SQL
> Server 2000 which does not support the root clause I think as it is a new
> feature only supported in SQL server 2005.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.
For XML Path problem?
I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.
Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.
Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.
Friday, March 9, 2012
FOR XML EXPLICIT doesnt seem to work for me--HELP NEEDED ASAP!!
Hi,
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.
<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...
</Customer>
</Customers>
Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!
My apologies for the delay in answering....
If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...
Here is the EXPLICIT solution:
select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"
union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers
union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers
union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers
-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit
and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):
select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')
Best regards
Michael
Wednesday, March 7, 2012
For XML Explicit
k. I'm going crazy here. I have some for xml explicit sql working (created a
test against the adventureworks database) but how can I get the results of
this sql into an xml variable. I have tried inserting it into a table
variable or setting an xml variable equal to it...I can't get it to
working...any help given is appreciated.
Select 1 as Tag
,null as Parent
,null as [Audit!1]
,'Test' as [Audit!1!type]
,null as [TestText!2]
,null as [Contacts!3]
,null as [ContectId!4]
Union All
Select 2
,1
,null
,null
,'This is test text'
,null
,null
Union All
Select 3
,1
,null
,null
,null
,null
,null
Union All
Select 4
,3
,null
,null
,null
,null
,ContactId
from Person.Contact
where contactId between 1 and 10
For XML ExplicitHello JI,
J> k. I'm going crazy here. I have some for xml explicit sql working
J> (created a test against the adventureworks database) but how can I
J> get the results of this sql into an xml variable. I have tried
J> inserting it into a table variable or setting an xml variable equal
J> to it...I can't get it to working...any help given is appreciated.
SQL 2000 or 2005? In 2005 this should work:
declare @.x xml;set @.x = (select * from (select 1 as Tag,null as Parent,null
as [Audit!1],'Test' as [Audit!1!type],null as [TestText!2],null as [Contacts
!3],null
as [ContectId!4] Union All Select 2,1,null,null,'This is test text',null,nul
l
Union All Select 3,1,null,null,null,null,null Union All Select 4,3,null,null
,null,null,ContactId
from Person.Contact where contactId between 1 and 10) as c for xml explicit,
type);select
@.x;
In 2000, XML aggreation of the UT occurs post query, so you couldn't do that
there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is 2k5.
Thank you Kent!
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741ea2a8c8432165f54180@.news.microsoft.com...
> Hello JI,
> J> k. I'm going crazy here. I have some for xml explicit sql working
> J> (created a test against the adventureworks database) but how can I
> J> get the results of this sql into an xml variable. I have tried
> J> inserting it into a table variable or setting an xml variable equal
> J> to it...I can't get it to working...any help given is appreciated.
> SQL 2000 or 2005? In 2005 this should work:
> declare @.x xml;set @.x = (select * from (select 1 as Tag,null as
> Parent,null as [Audit!1],'Test' as [Audit!1!type],null as
> [TestText!2],null as [Contacts!3],null as [ContectId!4] Union All Select
> 2,1,null,null,'This is test text',null,null Union All Select
> 3,1,null,null,null,null,null Union All Select
> 4,3,null,null,null,null,ContactId from Person.Contact where contactId
> between 1 and 10) as c for xml explicit,type);select @.x;
> In 2000, XML aggreation of the UT occurs post query, so you couldn't do
> that there.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
test against the adventureworks database) but how can I get the results of
this sql into an xml variable. I have tried inserting it into a table
variable or setting an xml variable equal to it...I can't get it to
working...any help given is appreciated.
Select 1 as Tag
,null as Parent
,null as [Audit!1]
,'Test' as [Audit!1!type]
,null as [TestText!2]
,null as [Contacts!3]
,null as [ContectId!4]
Union All
Select 2
,1
,null
,null
,'This is test text'
,null
,null
Union All
Select 3
,1
,null
,null
,null
,null
,null
Union All
Select 4
,3
,null
,null
,null
,null
,ContactId
from Person.Contact
where contactId between 1 and 10
For XML ExplicitHello JI,
J> k. I'm going crazy here. I have some for xml explicit sql working
J> (created a test against the adventureworks database) but how can I
J> get the results of this sql into an xml variable. I have tried
J> inserting it into a table variable or setting an xml variable equal
J> to it...I can't get it to working...any help given is appreciated.
SQL 2000 or 2005? In 2005 this should work:
declare @.x xml;set @.x = (select * from (select 1 as Tag,null as Parent,null
as [Audit!1],'Test' as [Audit!1!type],null as [TestText!2],null as [Contacts
!3],null
as [ContectId!4] Union All Select 2,1,null,null,'This is test text',null,nul
l
Union All Select 3,1,null,null,null,null,null Union All Select 4,3,null,null
,null,null,ContactId
from Person.Contact where contactId between 1 and 10) as c for xml explicit,
type);select
@.x;
In 2000, XML aggreation of the UT occurs post query, so you couldn't do that
there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is 2k5.
Thank you Kent!
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741ea2a8c8432165f54180@.news.microsoft.com...
> Hello JI,
> J> k. I'm going crazy here. I have some for xml explicit sql working
> J> (created a test against the adventureworks database) but how can I
> J> get the results of this sql into an xml variable. I have tried
> J> inserting it into a table variable or setting an xml variable equal
> J> to it...I can't get it to working...any help given is appreciated.
> SQL 2000 or 2005? In 2005 this should work:
> declare @.x xml;set @.x = (select * from (select 1 as Tag,null as
> Parent,null as [Audit!1],'Test' as [Audit!1!type],null as
> [TestText!2],null as [Contacts!3],null as [ContectId!4] Union All Select
> 2,1,null,null,'This is test text',null,null Union All Select
> 3,1,null,null,null,null,null Union All Select
> 4,3,null,null,null,null,ContactId from Person.Contact where contactId
> between 1 and 10) as c for xml explicit,type);select @.x;
> In 2000, XML aggreation of the UT occurs post query, so you couldn't do
> that there.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
Friday, February 24, 2012
For non-serialized does key lock lock more than one row?
For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David
|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David
|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>
For non-serialized does key lock lock more than one row?
For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index[vbcol=seagreen]
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index[vbcol=seagreen]
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>
For non-serialized does key lock lock more than one row?
For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> > For Sql 2000, for isolation levels not serialized, can a key lock
> > (especially created by an inserted row), involving a nonprimary key
index
> > end up locking more than one row?
> >
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> > For Sql 2000, for isolation levels not serialized, can a key lock
> > (especially created by an inserted row), involving a nonprimary key
index
> > end up locking more than one row?
> >
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>
Sunday, February 19, 2012
For each loop
Hi,
I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?
I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?
Please help me with the above problem as I am working on a demo.
Thank you!!
Here's how you do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx and there is a downloadable demo on there as well
The only difference is that in the example given here the recordset object variable is populated with an Execute SQL Task rather than the Recordset destination but thereafter the same applies.
You cannot use the DataReader destination by the way!
-Jamie
Subscribe to:
Posts (Atom)