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

Drop the end of an ip address

does anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"

I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.

i'm using sql server and linking tables thru access

fiskyou can do reverse and then a right with a nested charindex. with another reverse at the end.

or you can stuff with a nested charindex.|||i'm getting "undefined function" error.

SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, REVERSE(dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
FROM dbo_AeXInv_AeX_AC_TCPIP;

the reverse string function seems pretty simple, but I must be missing something.

fisk|||Same error message for charindex

SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, charindex('.', dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
FROM dbo_AeXInv_AeX_AC_TCPIP;

I just ran this in sql analyzer and it worked fine. Any idea why i'm getting this error in access?

fisk|||microsoft access does not use the same functions as microsoft sql server

i mean, you would think it would, what with sql standards and all, but you gots to remember, microsoft bought access from a different company than it bought sql server from

:)

as long as we're playing loosey-goosey with database platforms, why don't you run it on mysql? it has a wonderful function called SUBSTRING_INDEX

select substring_index(`IP Address`, '.', 3) as stuff_before_the_third_period
from dbo_AeXInv_AeX_AC_TCPIP|||I would have thought that access would just pass the sql statement to sql server and let it figure it out. I guess not.|||it will only do that if you have declared it as a pass-through query|||Not quiet. For linked tables, Access will attempt to format the statement as a pass-through query on its own, but if it cannot (and it cannot handle anything more than moderately complex) then it will execute it locally (and ineffeciently).|||Yep, that's pretty much what's happening.

Oh well, thanks anyway guys.

fisk|||For linked tables, Access will attempt to format the statement as a pass-through query on its ownholey moley

i've been using access with linked tables for about 10 years, and i didn't know that

so, what does it do when the sql contains an access function like InStr()?

it barfs, right?

so when you say "moderately complex" you really mean "anything even slightly more complex than SELECT foo FROM bar WHERE qux=937", eh?|||It's somewhere in the documentation. I don't know how complex it can get before it has to run it locally. It may depend upon whether it has embedded functions, or it may depend upon whether it is able to succesfully translate MS Access SQL to TSQL.|||You should try some simple queries and then monitor server activity to see what gets sent through.

Drop Down Parameter List

I have a string with values that are separated by a space. It looks like this:DAY MICS SUN FIX STD.I would like to take the string and make it a drop down list in a report parameter.I have tried the split function but it errors on me.How can I accomplish this?

Is the value dynamic?
If not, could have manually put into parameter list

If it's dynamic, I think best way is to use a Query for the parameters
and do the split in T-SQL

split function
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp|||Thanks for responding. Yes the value is dynamic. When you say do the split in T-SQL (Transaction SQL?) how do you do that? I dropped the code from the article link you sent me into the code section under report properties. I get an error "There is an error on line 0 of custom code: [BC30188] Declaration expected." Maybe that is not where it goes? My query looks like this so far: SELECT ImplodedsubKeywordsList WHERE FORMULA("@.Contains(keywords;\"KI Product Demand Category\")" ) Do I just tack that code onto the end of it somehow? Thank you.

Drilldown doesn't work on wildcard search!

I have a report parameter â'spare part numberâ' thatâ's configured like this:
Data type: string, non-queried (empty list), no default value. This field is
rendered as a text box in preview. I also have other parameters but these are
all based on queries and datasets and are therefore rendered as drop downs.
What I really want is the user to be able to ask for a report on many spare
part numbers in one go. I canâ't get this to work, so if you know how, please
let me know.
What works is to leave this field empty and have a filter like:
=Fields! PART_NR.Value Like =iif(trim( Parameters!SparePart.Value
)="","*",Parameters!SparePart.Value )
When I enter a valid spare part number (e.g.: 3215323) or do a wildcard
search (e.g.: 32*) The correct result is shown, but my drilldown no longer
works. Clicking the â'+â' sends the report back to me collapsed (This is in
preview mode).
Anyone know anything about this?
Regards
AK411MultiValued parameters are being added to the Yukon version of RS. There
are other posts in this newsgroup that may have a solution, but I do not
have one off of the top of my head.
--
| Thread-Topic: Drilldown doesn't work on wildcard search!!!
| thread-index: AcTMqhu37sydctEqQ0aX2mQTQ1wFQA==| X-WBNR-Posting-Host: 193.212.219.1
| From: =?Utf-8?B?Si4gS2FuZG8gNDEx?= <JKando411@.discussions.microsoft.com>
| Subject: Drilldown doesn't work on wildcard search!!!
| Date: Wed, 17 Nov 2004 05:34:05 -0800
| Lines: 23
| Message-ID: <03E96D3C-A721-435D-881D-4AB818C8B6EB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:35151
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a report parameter â'spare part numberâ' thatâ's configured
like this:
| Data type: string, non-queried (empty list), no default value. This field
is
| rendered as a text box in preview. I also have other parameters but these
are
| all based on queries and datasets and are therefore rendered as drop
downs.
|
| What I really want is the user to be able to ask for a report on many
spare
| part numbers in one go. I canâ't get this to work, so if you know how,
please
| let me know.
|
| What works is to leave this field empty and have a filter like:
| =Fields! PART_NR.Value Like =iif(trim( Parameters!SparePart.Value
| )="","*",Parameters!SparePart.Value )
|
| When I enter a valid spare part number (e.g.: 3215323) or do a wildcard
| search (e.g.: 32*) The correct result is shown, but my drilldown no
longer
| works. Clicking the â'+â' sends the report back to me collapsed (This
is in
| preview mode).
|
| Anyone know anything about this?
|
| Regards
| AK411
|
|