Dropping Data Connect String

I've created a data source connection using the sa login and password with
the save password option. Running the stored procedure returns a good result
set. Ok, I'm feeling pretty good at this point . But after clicking on the
Preview tab, I receive the following message: "A connection cannot be made
to the database. Set and test the connection string. Login failed for
DIM\tj."
Of course testing the connection works fine, but why is trying to
authenticate using my credentials when it should be use the connect string?
I'm running RS SP1 against a SQL 2000 db.
--
Any and all contributions are greatly appreciated ...
Regards TJIt couldn't be something inside the stored procedure, could it? Can you do a
straight select?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> I've created a data source connection using the sa login and password with
> the save password option. Running the stored procedure returns a good
> result
> set. Ok, I'm feeling pretty good at this point . But after clicking on the
> Preview tab, I receive the following message: "A connection cannot be made
> to the database. Set and test the connection string. Login failed for
> DIM\tj."
> Of course testing the connection works fine, but why is trying to
> authenticate using my credentials when it should be use the connect
> string?
> I'm running RS SP1 against a SQL 2000 db.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>|||If it is, I'm not seeing any errors when I execute the stored procedure on
the Data Tab. Is there an error log for the Data Tab?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> It couldn't be something inside the stored procedure, could it? Can you do
a
> straight select?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "TJ" <nospam@.nowhere.com> wrote in message
> news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > I've created a data source connection using the sa login and password
with
> > the save password option. Running the stored procedure returns a good
> > result
> > set. Ok, I'm feeling pretty good at this point . But after clicking on
the
> > Preview tab, I receive the following message: "A connection cannot be
made
> > to the database. Set and test the connection string. Login failed for
> > DIM\tj."
> >
> > Of course testing the connection works fine, but why is trying to
> > authenticate using my credentials when it should be use the connect
> > string?
> >
> > I'm running RS SP1 against a SQL 2000 db.
> > --
> > Any and all contributions are greatly appreciated ...
> > Regards TJ
> >
> >
>|||I am running into the exact same issue... Any resolution to this yet?
"TJ" wrote:
> If it is, I'm not seeing any errors when I execute the stored procedure on
> the Data Tab. Is there an error log for the Data Tab?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > It couldn't be something inside the stored procedure, could it? Can you do
> a
> > straight select?
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "TJ" <nospam@.nowhere.com> wrote in message
> > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > I've created a data source connection using the sa login and password
> with
> > > the save password option. Running the stored procedure returns a good
> > > result
> > > set. Ok, I'm feeling pretty good at this point . But after clicking on
> the
> > > Preview tab, I receive the following message: "A connection cannot be
> made
> > > to the database. Set and test the connection string. Login failed for
> > > DIM\tj."
> > >
> > > Of course testing the connection works fine, but why is trying to
> > > authenticate using my credentials when it should be use the connect
> > > string?
> > >
> > > I'm running RS SP1 against a SQL 2000 db.
> > > --
> > > Any and all contributions are greatly appreciated ...
> > > Regards TJ
> > >
> > >
> >
> >
>
>|||Here are a couple of things you might try:
a) I was calling a stored procedure from within a stored procedure using the
exec command; The report data connection account didn't have permissions for
the stored procedure inside the main stored procedure. I found the
permissions issue by running the main stored procedure in Query Analyzer,
but you need to open your Query Analyzer connection using the same data
connection information being used in your report.
b) You can hard code the User Id and password setting in the Connection
String on the Data Source Tab for the report.
Good Luck
TJ
"StanDaMon" <StanDaMon@.discussions.microsoft.com> wrote in message
news:102E7428-08C9-467E-8DC6-B69A4E8D094E@.microsoft.com...
> I am running into the exact same issue... Any resolution to this yet?
> "TJ" wrote:
> > If it is, I'm not seeing any errors when I execute the stored procedure
on
> > the Data Tab. Is there an error log for the Data Tab?
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > It couldn't be something inside the stored procedure, could it? Can
you do
> > a
> > > straight select?
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "TJ" <nospam@.nowhere.com> wrote in message
> > > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > > I've created a data source connection using the sa login and
password
> > with
> > > > the save password option. Running the stored procedure returns a
good
> > > > result
> > > > set. Ok, I'm feeling pretty good at this point . But after clicking
on
> > the
> > > > Preview tab, I receive the following message: "A connection cannot
be
> > made
> > > > to the database. Set and test the connection string. Login failed
for
> > > > DIM\tj."
> > > >
> > > > Of course testing the connection works fine, but why is trying to
> > > > authenticate using my credentials when it should be use the connect
> > > > string?
> > > >
> > > > I'm running RS SP1 against a SQL 2000 db.
> > > > --
> > > > Any and all contributions are greatly appreciated ...
> > > > Regards TJ
> > > >
> > > >
> > >
> > >
> >
> >
> >

DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:

> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:[vbcol=seagreen]
> You might compare the query plans to make sure they are the same. It migh
t
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.

DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:
> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:
> You might compare the query plans to make sure they are the same. It might
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
> >I have a query that I'm trying to benchmark. The first time I run it,
> > it takes 40 seconds to run. The next time I run it, it returns in 0
> > seconds.
> >
> > I execute the following:
> >
> > CHECKPOINT
> > DBCC DROPCLEANBUFFERS
> > DBCC FREEPROCCACHE
> >
> > Then I run the query again and it takes 40 seconds, as expected.
> > Re-running the query causes it to go back down to 0 seconds, as
> > expected. However, from this point forward, even if I run the commands
> > above, the query stays at 0 seconds! I can't seem to clear the cache
> > anymore.
> >
> > Is something being cached in spite of dropping the data and procedure
> > caches?
> >|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>> You might compare the query plans to make sure they are the same. It
>> might
>> be that a large scan takes 40 seconds without cached data but runs
>> sub-second once data are cached. A plan change (e.g. index seek) could
>> cause the query to run sub-second with or without cached data.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <element533@.gmail.com> wrote in message
>> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>> >I have a query that I'm trying to benchmark. The first time I run it,
>> > it takes 40 seconds to run. The next time I run it, it returns in 0
>> > seconds.
>> >
>> > I execute the following:
>> >
>> > CHECKPOINT
>> > DBCC DROPCLEANBUFFERS
>> > DBCC FREEPROCCACHE
>> >
>> > Then I run the query again and it takes 40 seconds, as expected.
>> > Re-running the query causes it to go back down to 0 seconds, as
>> > expected. However, from this point forward, even if I run the commands
>> > above, the query stays at 0 seconds! I can't seem to clear the cache
>> > anymore.
>> >
>> > Is something being cached in spite of dropping the data and procedure
>> > caches?
>> >
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.

drop out of stored procedure when found some data

Hi
I have a stored procedure which takes a while to run and returns some
data. I want to make
another version that just says is there some data to look at (boolean,
yes no). This should
run as quickly as possible so as soon as it finds theres any data to
look at it stops. Is
there any general way of doing this?
This has to work in oracle and sqlserver 2000.
Can this be done in SQL. I'm afraid I know very little about SQL so I
just don't know if its even possible?
thanks
fshard wrote:
> Hi
> I have a stored procedure which takes a while to run and returns some
> data. I want to make
> another version that just says is there some data to look at (boolean,
> yes no). This should
> run as quickly as possible so as soon as it finds theres any data to
> look at it stops. Is
> there any general way of doing this?
> This has to work in oracle and sqlserver 2000.
> Can this be done in SQL. I'm afraid I know very little about SQL so I
> just don't know if its even possible?
> thanks
> f
Use EXISTS:
IF EXISTS (SELECT ... /* something */)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> IF EXISTS (SELECT ... /* something */)
> --
> David Portas, SQL Server MVP
thanks for that, nice simple answer.

drop out of stored procedure when found some data

Hi
I have a stored procedure which takes a while to run and returns some
data. I want to make
another version that just says is there some data to look at (boolean,
yes no). This should
run as quickly as possible so as soon as it finds theres any data to
look at it stops. Is
there any general way of doing this?
This has to work in oracle and sqlserver 2000.
Can this be done in SQL. I'm afraid I know very little about SQL so I
just don't know if its even possible?
thanks
fshard wrote:
> Hi
> I have a stored procedure which takes a while to run and returns some
> data. I want to make
> another version that just says is there some data to look at (boolean,
> yes no). This should
> run as quickly as possible so as soon as it finds theres any data to
> look at it stops. Is
> there any general way of doing this?
> This has to work in oracle and sqlserver 2000.
> Can this be done in SQL. I'm afraid I know very little about SQL so I
> just don't know if its even possible?
> thanks
> f
Use EXISTS:
IF EXISTS (SELECT ... /* something */)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> IF EXISTS (SELECT ... /* something */)
> --
> David Portas, SQL Server MVP
thanks for that, nice simple answer.

DrillThrough action is broken and returns no data

I have a simple DrillThrough action which has been working for sometime. However, it has now stopped working and will not return any data... even if I invoke it from the highest level. I have re-deployed and processed the whole database from scratch. There is no conditional statement to this so I cannot see why it would not run.

What could possibly been happening? Has anyone had this problem before? Any suggestions about finding the source of this problem?

Ta

Dirc

Hello Dirc/Dorc,

Have you ever been able to solve this? I seem to be experiencing the same thing.

Regards, Jeroen

DrillThrough action is broken and returns no data

I have a simple DrillThrough action which has been working for sometime. However, it has now stopped working and will not return any data... even if I invoke it from the highest level. I have re-deployed and processed the whole database from scratch. There is no conditional statement to this so I cannot see why it would not run.

What could possibly been happening? Has anyone had this problem before? Any suggestions about finding the source of this problem?

Ta

Dirc

Hello Dirc/Dorc,

Have you ever been able to solve this? I seem to be experiencing the same thing.

Regards, Jeroen