ODBC datasource wont work on report server!!

Hi,

I have VS2005 and sql server express and ive created some reports using sql server
as a backend. everything works as it should.

THEN...

i created a report that uses a system DSN odbc source and it works in VS2005 it works fine.
i have deployed it to the report server and the datasource appears t be there, but when i try and run the report i ge the following message

An error has occurred during report processing.

An attempt has been made to use a data extension 'ODBC' that is not registered for this report server.anyone got any ideas how i can make this work? i have tried to find an explanation using google, but i cannot seem to see any thing that answers the question.

Regards,

Russ
Express versions of Reporting Services allow for data sources to only be local and must be a matching SKU of SQL Server.

ODBC DataSource name problem

Hi All
We have an application which is used the ODBC Data Source to comunicate
with the database. So during the installation of the product the
installation creating
the database name like : VAPost and server = datasource name which is 'VAPos
t'
not the real server name in the Microsoft SQL server DSN Configuration optio
n.
Also the installation is created the Alias in SQL Server Client Network
Utility for
'VAPost' which has Server alias = 'VAPost', NetWork library = 'TCP/IP' and
the Connection parameters is the ip address of the server.
So my application is used the DSN name 'VAPost' to connect to the database b
ut
there is an error message regarding "Communication Link Failure". I could
not figure out where is the problem but the same configuration it works on
other server. Any help is greatly appreciated.
Thanks,
JodieHi Jodie,
You are using a system DSN? You should access that DSN using a ".Net
Providers\ODBC data providers" Connection. Configure it to use the DSN.
Then within the dataflow, create a DataReader data source. Set it to use th
e
DSN connection. You control what is being pulled by the SQLCommand on the
second tab.
-Steven
"Jodie" wrote:

> Hi All
> We have an application which is used the ODBC Data Source to comunicate
> with the database. So during the installation of the product the
> installation creating
> the database name like : VAPost and server = datasource name which is 'VAP
ost'
> not the real server name in the Microsoft SQL server DSN Configuration opt
ion.
> Also the installation is created the Alias in SQL Server Client Network
> Utility for
> 'VAPost' which has Server alias = 'VAPost', NetWork library = 'TCP/IP' and
> the Connection parameters is the ip address of the server.
> So my application is used the DSN name 'VAPost' to connect to the database
but
> there is an error message regarding "Communication Link Failure". I could
> not figure out where is the problem but the same configuration it works on
> other server. Any help is greatly appreciated.
> Thanks,
> Jodie
>

ODBC datasource / dataset problem

Hi,
I'm using the Visual Studio designer to create my RS reports. When I
add a new project to the solution, the new project wizard allows me to
choose ODBC as the datasource type I want to use. However, I amn't
given the ODBC option when I try to add a datasource to an existing
project. I work around this by copying and manually editing the ODBC
.rds file that was created by the new project wizard. Does anyone know
"the right way" to add an ODBC datasource to a project without using
the project wizard? I'm a bit concerned about my workaround because I
now have multiple .rds files in the same project with the same
<DataSourceID>.
Also, I don't know if this problem is caused by my workaround, but when
I use the "hacked" .rds file as a report's datasource and open the
"Data" tab I see the message:
"The query could not be loaded. Verify your connection string and query
string"
The report still runs fine, and if I open the "Generic Query Designer"
(in the "Data" tab), the query appears, and returns results if I click
the "!" button.
Thanks in advance,
DonRight click on shared data sources, add new data source. When the data link
properties box comes up it is on the connection tab, click on the Provider
tab where you can now pick ODBC.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Don" <donalmurtagh@.yahoo.co.uk> wrote in message
news:1127296726.880744.15570@.g44g2000cwa.googlegroups.com...
> Hi,
> I'm using the Visual Studio designer to create my RS reports. When I
> add a new project to the solution, the new project wizard allows me to
> choose ODBC as the datasource type I want to use. However, I amn't
> given the ODBC option when I try to add a datasource to an existing
> project. I work around this by copying and manually editing the ODBC
> .rds file that was created by the new project wizard. Does anyone know
> "the right way" to add an ODBC datasource to a project without using
> the project wizard? I'm a bit concerned about my workaround because I
> now have multiple .rds files in the same project with the same
> <DataSourceID>.
>
> Also, I don't know if this problem is caused by my workaround, but when
> I use the "hacked" .rds file as a report's datasource and open the
> "Data" tab I see the message:
> "The query could not be loaded. Verify your connection string and query
> string"
> The report still runs fine, and if I open the "Generic Query Designer"
> (in the "Data" tab), the query appears, and returns results if I click
> the "!" button.
>
> Thanks in advance,
> Don
>

ODBC Data Source On Clustered SQL Server

We created an ODBC datasource on a SQL Server in the cluster. Then there was
a hardware failover and subsequently unrelated DTS processes failed because
the data source wasn't defined on the failover machine. Even though those
processes did not use the ODBC source, in fact nothing used the ODBC source,
it was just created to be used in testing.
So, how do you create an ODBC datasouce that will be cluster-wide and not
just machine specific? Do you have to do it through the Cluster Management
console?
Thanks,
Bob
ODBC DSN's are not services and not applications; they are specifications,
typically stored in the registry. If you need it, just like mapped
networked drives, then you will need to create them separately on each
cluster node that requires them. There is no way to cluster them.
However, if you switched to using Data Links or ODBC file-based DSNs, then
you could store them on one of the shared clustered drives. In this case,
they would failover with the group, retain the same drive letter and path;
so, they would always be present with the virtual server.
On to another topic. Although we allow retaining the DTS package in the
MSDB repository and logging to that package, we do not allow the execution
of DTSRun on the DBMS servers. We require our clients to execute those on
an Application Server.
We try to minimize the number of external processes that run alongside the
DBMS memory space in order to maximize performance and system stability.
The last thing we want is for an external process to foul connected to an
internal SQL Server thread.
Sincerely,
Anthony Thomas

"Bob C" <BobC@.discussions.microsoft.com> wrote in message
news:65CC8979-C05A-43A9-92D6-9B156958E8CD@.microsoft.com...
> We created an ODBC datasource on a SQL Server in the cluster. Then there
was
> a hardware failover and subsequently unrelated DTS processes failed
because
> the data source wasn't defined on the failover machine. Even though those
> processes did not use the ODBC source, in fact nothing used the ODBC
source,
> it was just created to be used in testing.
> So, how do you create an ODBC datasouce that will be cluster-wide and not
> just machine specific? Do you have to do it through the Cluster Management
> console?
> Thanks,
> Bob

ODBC Create a new Data Souce to SQL not displaying

On a Windows XP PC, why doesn't the Create a new Data
Source to SQL Server window pop up when you select to add
a new system dsn from the ODBC data source
administrator? I have one particular PC where this won't
come up, not all XP PCs though. Anyone see this before!
Thanks.
.Did you try reinstalling MDAC? You'd want to at least check
the MDAC configuration on the PC using component checker.
You can download the latest MDAC as well as component
checker from:
http://msdn.microsoft.com/downloads/list/dataaccess.asp
-Sue
On Mon, 1 Dec 2003 08:06:15 -0800, "Nil"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>On a Windows XP PC, why doesn't the Create a new Data
>Source to SQL Server window pop up when you select to add
>a new system dsn from the ODBC data source
>administrator? I have one particular PC where this won't
>come up, not all XP PCs though. Anyone see this before!
>Thanks.
>.
>

---

Obtaining values on datasource inserting event

Hi,

I want to be able to spot when the same employee name gets added to my grid. This is to ensure that I cannot not have the same firstname and lastname (i.e. cannot have 2 John Smiths).

It is kind of like spoting for duplicates but they are not PKs. I was hoping if there was a way you could identify the the feild values on the "inserting" event of the datasource so I could put some logic in. The reason for placing it there is because we have the e.cancel = true command.

Thanks in advance,

Jon

Since you are going to have to query the database to get this information, why not just use If Not Exists in your SQL?

|||

Mike,

Thanks for getting back so promptly. I understand what you are saying what does the SQL look like ? would you be able to provide a very basic example?

Just to give you a little more background, I am using a mixture of standard ASP controls and also using the RAD controls. Currently I focus on the controls to perform this kind of checking and would like a more generic solution at the Datasource level, this is so I dont need to worry about specific coding based on which control I use.

Thanks

|||

You would put it in a stored procedure:http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5

Note: there are some other ideas in that article. You can review those as well.

|||

Thanks, marked as answered

|||

You can create a procedure like below and bind it as the insert command for your sql datasource. Then you can execute the insert method in a try catch block and display the error message to the user:

create procedure pcheckDuplicates_insert(@.firstNamevarchar(50) ,@.lastNamevarchar(50))asbeginifexists (select 1from checkDuplicateswhere firstName = @.firstNameand lastName = @.lastName )beginraiserror ('A person with this name already exists',16,1)with nowaitreturnendelsebegininsert checkDuplicates ( firstName , lastName )values ( @.firstName , @.lastName )endreturnend

I don't think you'll need that inserting event of the datasource. You can directly write all your duplicate prevention logic in the procedure itself.

Hope this will help.