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.
> >
> >
>
>

DatabaseName data column is blank in Profiler

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
IgorHi
It is not always captured!
http://tinyurl.com/3m6xm
http://tinyurl.com/4t9wb
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:epuZDeyCFHA.1188@.tk2msftngp13.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>

DatabaseName data column is blank in Profiler

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
Igor
Hi
It is not always captured!
http://tinyurl.com/3m6xm
http://tinyurl.com/4t9wb
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:epuZDeyCFHA.1188@.tk2msftngp13.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>

DatabaseName data column is blank in Profiler

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
IgorHi
It is not always captured!
http://tinyurl.com/3m6xm
http://tinyurl.com/4t9wb
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:epuZDeyCFHA.1188@.tk2msftngp13.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>

DatabaseName data column in Profiler is blank

Hello!
I am trying to figure out when DatabaseName Data column in SQL Trace is
always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
Any help is greatly appreciated,
Igor
It's only populated for certain security related events. It won't be
populated for events like Batch Completed or SP Completed. When I load a
trace into a table I tend to have an auxillary table with the databaseid +
name and then create a view to look at the trace so I get the database name.
I do a similar thing with the Event column (you can find the names for the
eventid's in BOL)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OlgV2dyCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hello!
> I am trying to figure out when DatabaseName Data column in SQL Trace is
> always blank. I am executing SQL Batch. I can see DatabaseID but not Name.
> Any help is greatly appreciated,
> Igor
>
|||Thanks a lot,
Igor
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eplEBP3CFHA.228@.tk2msftngp13.phx.gbl...
> It's only populated for certain security related events. It won't be
> populated for events like Batch Completed or SP Completed. When I load a
> trace into a table I tend to have an auxillary table with the databaseid +
> name and then create a view to look at the trace so I get the database
> name. I do a similar thing with the Event column (you can find the names
> for the eventid's in BOL)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OlgV2dyCFHA.2288@.TK2MSFTNGP14.phx.gbl...
>

Database Tuning Advisor

Hi All,

Can anyone tell me if it makes any difference in Database Tuning advisor when is used against stored procedures workload and simple batch statements workload.

I used it against stored procedures first and got no recommendations for it, and when I used the statements of same stored procedures outside the procedure and used the worload against the DTA, I got some recommendations.

Also, in the reports section of DTA, the Database Access Report shows no database details, whereas in the other case, I got all the details for database, tables and columns accessed.

When you use DTA with a given workload of SQL queries and updates, DTA recommends an appropriate physical design, and generates a script to implement the recommended physical design.

There is no direct connection between VSTS for DB Pro and DTA. After you use DTA you can change your DB project and DB objects as the DTA recommended.

You can read great article about DTA and get more information from: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQL2005DTA.doc

Maor

|||

DTA does not get inside my database for the stored procedures where there's a 'if' condition inside the SP.

For example,

I created a stored procedure as,

createproc testSP2

@.Group_ID_id int

as

begin

select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from

PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)

end

GO

exec testSP2

GO

The Database, Table, column Access reports shows the details of database,tables and columns accessed in the DTA and a set of recommendations are recieved.

But when the same procedure is modifies as,

create proc testSP2

@.Group_id int

as

begin

if(1=1)

begin

select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from

PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)

end

end

GO

exec testSP2

GO

The Database, Table, column Access reports don't give any details and no recommendations are made.