Datatype Conversion during insert

I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
Line 303: Incorrect syntax near '='.
The code is:

INSERT INTO table-name( c1,c2,c3,c4)

VALUES
(@.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3)

Where am I going wrong? Where do I do the conversion? The comma after END AS @.variable, Is that syntax right?

Please advise.

ThanksUse SELECT instead of VALUES.

INSERT INTO table-name( c1,c2,c3,c4)

SELECT @.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3|||snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.|||Originally posted by ms_sql_dba
snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

to ms_sql_dba:

You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.

create table test13(id int,code varchar(10))
go
insert test13 values(1,case when 1=1 then 1 else 0 end)
insert test13 values(1,'4'+'5')

Datasource credentails not saved if inside a batch

I am publishing an RDL using webservice. After publishing the report, i
update the embeded datasource for the report using "SetReportDataSources"
method.
If i use a "BatchHeader", with "ExecuteBatch" method the report is not
working. It says cannot login. I have to go the report datasource and retype
the password for the report to work.
But if I donot use "BatchHeader", the same code works.
This issue happens only on one of the reprot servers in our network. The
same code works on other report servers in the network. Is there a settings
in the report server or the server itself that is causing this.
Any help appreciated.I think in RS 2005 it was changed to SetItemDataSources(), anyway here's the
code we use for updating the datasources for the reports during deployment.
We create the datasource once, then for each report we deploy the report
then call this to update the datasource reference. We only use a single
datasource for all reports, so not too complex of an example. Depending on
the environment and version we change the datasource name.
Sub DSChange(ByVal ReportName As String)
Dim reference As new DataSourceReference()
Dim ds As New DataSource()
Dim dataSources() As DataSource
Try
reference.Reference = "/" & ReportDir & "/" & RSDSName
DataSources = rs.GetItemDataSources("/" & ReportDir & "/" &
ReportName)
ds = DataSources(0)
ds.Item = CType(reference, DataSourceDefinitionOrReference)
rs.SetItemDataSources("/" & ReportDir & "/" & ReportName,
DataSources)
Console.WriteLine("datasource reference set")
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Steve MunLeeuw
"vRam" <vRam@.discussions.microsoft.com> wrote in message
news:D47581C0-F81B-4C34-A695-A1F95693A668@.microsoft.com...
>I am publishing an RDL using webservice. After publishing the report, i
> update the embeded datasource for the report using "SetReportDataSources"
> method.
> If i use a "BatchHeader", with "ExecuteBatch" method the report is not
> working. It says cannot login. I have to go the report datasource and
> retype
> the password for the report to work.
> But if I donot use "BatchHeader", the same code works.
> This issue happens only on one of the reprot servers in our network. The
> same code works on other report servers in the network. Is there a
> settings
> in the report server or the server itself that is causing this.
> Any help appreciated.
>|||thanks for the reply.
But our implementation is on RS2000.
Again our code is working on all the RS servers except one of them. that too
if we set the datasource within a transaction using BatchHeaders.
"Steve MunLeeuw" wrote:
> I think in RS 2005 it was changed to SetItemDataSources(), anyway here's the
> code we use for updating the datasources for the reports during deployment.
> We create the datasource once, then for each report we deploy the report
> then call this to update the datasource reference. We only use a single
> datasource for all reports, so not too complex of an example. Depending on
> the environment and version we change the datasource name.
>
> Sub DSChange(ByVal ReportName As String)
> Dim reference As new DataSourceReference()
> Dim ds As New DataSource()
> Dim dataSources() As DataSource
> Try
> reference.Reference = "/" & ReportDir & "/" & RSDSName
> DataSources = rs.GetItemDataSources("/" & ReportDir & "/" &
> ReportName)
> ds = DataSources(0)
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> rs.SetItemDataSources("/" & ReportDir & "/" & ReportName,
> DataSources)
> Console.WriteLine("datasource reference set")
> Catch e As Exception
> Console.WriteLine(e.Message)
> End Try
> End Sub
> Steve MunLeeuw
> "vRam" <vRam@.discussions.microsoft.com> wrote in message
> news:D47581C0-F81B-4C34-A695-A1F95693A668@.microsoft.com...
> >I am publishing an RDL using webservice. After publishing the report, i
> > update the embeded datasource for the report using "SetReportDataSources"
> > method.
> >
> > If i use a "BatchHeader", with "ExecuteBatch" method the report is not
> > working. It says cannot login. I have to go the report datasource and
> > retype
> > the password for the report to work.
> >
> > But if I donot use "BatchHeader", the same code works.
> >
> > This issue happens only on one of the reprot servers in our network. The
> > same code works on other report servers in the network. Is there a
> > settings
> > in the report server or the server itself that is causing this.
> >
> > Any help appreciated.
> >
> >
>
>

Datasource

How can I synchronize a table inside a list if both have different
datasources?
I need to synchronize the table by the list details grouping expression. If
I use the table filter, the table fieldID is always aggregated when inside
a list with a different datasource and it results in error.
and
if I add a field to a table from another datasource it is always in the form
Sum(Fields!Field.Value, "datasource")
which is the total sum of the values of the datasource even though the
datasource is grouped on the same field the table is grouped
how can I break this aggregate by the same field the table is grouped so
that I did not get the always the same total sum of all the values of that
aditional datasource in all rows of the table?
Thank you in advance
Martin Suchy
CBSolutionsWithin a data region you cannot use more than one dataset. You have to write
a single query that supplies data to the list and to the table. In most
cases, this involves a JOIN SQL query.
HTH
Charles Kangai, MCT, MCDBA
"Martin" wrote:
> How can I synchronize a table inside a list if both have different
> datasources?
> I need to synchronize the table by the list details grouping expression. If
> I use the table filter, the table fieldID is always aggregated when inside
> a list with a different datasource and it results in error.
> and
> if I add a field to a table from another datasource it is always in the form
> Sum(Fields!Field.Value, "datasource")
> which is the total sum of the values of the datasource even though the
> datasource is grouped on the same field the table is grouped
> how can I break this aggregate by the same field the table is grouped so
> that I did not get the always the same total sum of all the values of that
> aditional datasource in all rows of the table?
> Thank you in advance
> Martin Suchy
> CBSolutions

Datasets in Data regions

I have encountered a problem in one of my reports.
I use multiple datasets, and inside a List I have two matrices, one for my
'income' dataset and one from my 'cost' dataset. The List uses a field from
the 'income' dataset as it's 'Details Grouping'.
My problem is that even though i explicitly set the data source for the
second matrix to my 'cost' dataset, it still access the 'income' dataset. I
looked in the RDL-file and the <DataSetName> fields is correctly set as
'cost'.
Furthermore; when using the expression editor in a field in the cost-matrix,
I can only choose fields from the 'income' dataset...
Any clues as to what causes this?Your matrices are in the List data region. All items in the same data region
must use the same dataset. That is why you are getting this problem. You have
to change how you are doing this. Perhaps you need to use subreports inside
the list data region? Create a separate report for each of the matrix regions
that needs to have a different dataset. Each report must use a parameter if
you want to filter the data returned in the matrix. Add a subreport control
to the list data region and link to the report you just created, passing the
correct parameter(s) to return the data you need.
HTH
Charles Kangai, MCT, MCDBA
"Kristian Vinther" wrote:
> I have encountered a problem in one of my reports.
> I use multiple datasets, and inside a List I have two matrices, one for my
> 'income' dataset and one from my 'cost' dataset. The List uses a field from
> the 'income' dataset as it's 'Details Grouping'.
> My problem is that even though i explicitly set the data source for the
> second matrix to my 'cost' dataset, it still access the 'income' dataset. I
> looked in the RDL-file and the <DataSetName> fields is correctly set as
> 'cost'.
> Furthermore; when using the expression editor in a field in the cost-matrix,
> I can only choose fields from the 'income' dataset...
> Any clues as to what causes this?|||I combined my datasets into one, and instead used a Filter on my
matrix-rowgroups. That did the trick.
Thanks for your reply.
"Charles Kangai" wrote:
> Your matrices are in the List data region. All items in the same data region
> must use the same dataset. That is why you are getting this problem. You have
> to change how you are doing this. Perhaps you need to use subreports inside
> the list data region? Create a separate report for each of the matrix regions
> that needs to have a different dataset. Each report must use a parameter if
> you want to filter the data returned in the matrix. Add a subreport control
> to the list data region and link to the report you just created, passing the
> correct parameter(s) to return the data you need.
> HTH
> Charles Kangai, MCT, MCDBA
> "Kristian Vinther" wrote:
> > I have encountered a problem in one of my reports.
> >
> > I use multiple datasets, and inside a List I have two matrices, one for my
> > 'income' dataset and one from my 'cost' dataset. The List uses a field from
> > the 'income' dataset as it's 'Details Grouping'.
> >
> > My problem is that even though i explicitly set the data source for the
> > second matrix to my 'cost' dataset, it still access the 'income' dataset. I
> > looked in the RDL-file and the <DataSetName> fields is correctly set as
> > 'cost'.
> > Furthermore; when using the expression editor in a field in the cost-matrix,
> > I can only choose fields from the 'income' dataset...
> >
> > Any clues as to what causes this?

Database Tuning Advisor

I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
Bryan
Hi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:

> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan
|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:

Database Tuning Advisor

I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:

> I am trying to run through a trace log to improve one of our databases her
e,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully fr
om
> management studio or possibly try and unravel what the queries and run the
m
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
>|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something whic
h
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I se
e
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
>|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something wh
ich
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
>

Database Tuning Advisor

I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
> > I am trying to run through a trace log to improve one of our databases here,
> > however, the calls originate inside a read only database, goto another
> > database on the same server before they finally end up in the destination
> > database that i want to tune.
> >
> > How can i get the advisor to travel through these other two databases?
> >
> > Right now, it doesn't even recognize any of the statements because none of
> > them affect any databases it knows about.
> >
> > Bryan|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
> > Hi Bryan
> >
> > I assume that you have selected all these databases/tables on the general
> > tab? In which case I would try look at the query plans when run manully from
> > management studio or possibly try and unravel what the queries and run them
> > directly and then look at the query plans.
> >
> > John
> >
> > "Bryan Aldrich" wrote:
> >
> > > I am trying to run through a trace log to improve one of our databases here,
> > > however, the calls originate inside a read only database, goto another
> > > database on the same server before they finally end up in the destination
> > > database that i want to tune.
> > >
> > > How can i get the advisor to travel through these other two databases?
> > >
> > > Right now, it doesn't even recognize any of the statements because none of
> > > them affect any databases it knows about.
> > >
> > > Bryan|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
> > Yes, I even tried by selecting all of the databases involved in this trace
> > file. I even selected Master to see if it would execute the sp_ExecuteSQL
> > calls, but it didn't. It analyzes the originating database just fine. I see
> > the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> > tables".
> >
> > I'm hoping for some sort of automated solution because it can be difficult
> > to always recreate the exact parameters that make it to this database.
> >
> > Bryan
> >
> > "John Bell" wrote:
> >
> > > Hi Bryan
> > >
> > > I assume that you have selected all these databases/tables on the general
> > > tab? In which case I would try look at the query plans when run manully from
> > > management studio or possibly try and unravel what the queries and run them
> > > directly and then look at the query plans.
> > >
> > > John
> > >
> > > "Bryan Aldrich" wrote:
> > >
> > > > I am trying to run through a trace log to improve one of our databases here,
> > > > however, the calls originate inside a read only database, goto another
> > > > database on the same server before they finally end up in the destination
> > > > database that i want to tune.
> > > >
> > > > How can i get the advisor to travel through these other two databases?
> > > >
> > > > Right now, it doesn't even recognize any of the statements because none of
> > > > them affect any databases it knows about.
> > > >
> > > > Bryan