Multivalue Parameter against Oracle 9 datasource?

Everything I have read says that this is possible in Oracle 9 and greater versions, but I can't get it to work...

Here is my query:

select * from employee where employee_id in ('111','222')

This works fine in the data tab until I replace the values with a parameter. I get the pop-up to enter the parameter values but no matter how I enter them, I don't get any rows returned. I've tried entering them as '111','222' and as 111,222 and changing the parameter type from a string to an int, but nothing seems to work.

I am running this on SQL Server RS 2005 and my Oracle driver (Oracle in OraHome92) is version 9.02.

Executing queries with multi-value parameters is not supported on the data tab (through the parameter popup), but you can run the report in preview.

-- Robert

|||I tried it in the preview window but didn't receive any rows on my report. I know there are records for the 2 values I entered. I tried entering it as '111','222' and as 111,222. They are varchar values in the database. Any other suggestions?|||If I changed the datasource type to Oracle, rather than ODBC, I was able to get the multivalue parameter to work correctly.|||

Yes, multi value query parameters are only supported through the "Oracle" data source type (because the SSRS Oracle data extension wrapper performs special handling for multi value query parameters as they are not supported by the underlying Oracle provider).

-- Robert

Multiple versions of SQL Server 2005 - What order to install?

I currently have three SQL Server instances installed on my notebook. One is v8, which I believe is used by an accounting application. The other two are as follows:-

SQL Server MSSMLBIZ

SQL Server SQLEXPRESS

The second one (SQLEXPRESS) fails to start generating the following error:-

The SQL Server (SQLEXPRESS) service terminated with service-specific error 17058 (0x42A2).

Other services listed in SQL Server Configuration Manager are:-

SQL Server Integration Services - which runs apparently normally.

SQL Server Analysis Services (MSSQLSERVER)

SQL Server Reporting Services (MSSQLSERVER)

SQL Server Browser

The last thre of these are run under LocalSystem.

I have been trying to install the SQLServer SP2 to the SQLEXPRESS instance for some number of days now, but each time it runs, it fails to install.

I am beginning to wonder if there is a sequence of installation for each of the above instances that should be followed. Can anyone give me any suggestions, or answers to the above problems?

Thanks,

It may be that the SQL Server Express installation is not using a 'good' login account.

Go to [Control Panel], [Admin Tools], [Services]. Find the SQLExpress instance, and double-click, then select the [Log On] tab. Select 'Local System Account', and start the service.

If it does not start, you need to uninstall and then re-install SQL Express.

If it does start up. refer to these articles about properly setting up a service account.

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

|||

You might try changing the problem service to start by using the local system...

Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
Simon
Simon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/de..._adda_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegro ups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>
|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/de..._adda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegro ups.com...
>
|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon

Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
SimonSimon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
>> Hi,
>> I'm currrently running two computers with sql server on and in each
>> server there are a number of databases. If I'm running query analyzer
>> in on server say server 1 how do I access a table in server 2? For
>> example where you would normally say:
>> select * from dababase.owner.table_name how do I specify the
>> actual server?
>> Thanks
>> Simon
>|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon

Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
SimonSimon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/d... />
a_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/d...>
dda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
>|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon

Multiple SQL Server Native Client Versions

One of the big problems with the old MDAC was different versions on different client machines. You would test your app with the latest version say, but when you deployed it, it might fail as the client has a different version.

My company develops software for Municipal Government clients. These clients use other SQL Server applications as well as ours, but they can only use one version of the client software (MDAC) on a given desktop. That means if we require a particular MDAC version, but the clients other applications from other vendors don't officially support that MDAC version, the client is in a real jam.

Our software also supports Oracle, which allows our software to specify a particular Oracle Home which points to a directory with a particular version of the Oracle client dlls (along with corresponding registry entries for that Oracle Home), such that we control the exact client version of the Oracle software that the client uses with our applications. This will not interfere with, and is completely seperate, from the default Oracle home installed when you install the Oracle client software.

What I would like to see for the Native Client is the ability to have our applications use the version of the Native Client that we wish to support and deploy without interfering with the Native Client version used by other applications. Have a default Native Client, but allow applications to somehow specify a different Native Client version/set of DLLs.

Is there any such functionality with the Native Client? (I didn't see any mention of such in the documentation, but I thought I'd ask)

If not, are there any future plans to support multiple Native Client versions on the same desktop?

At present, there is only one version of the SQL Server Native Client, so the particular issue you've raised isn't technically a concern at this point (which may explain why you didn't find any obvious discussion of the topic).

In the SQL Server Native Client blog, Chris and/or Acey discuss the history of issues that were encountered with multiple versions of MDAC and specifically discuss the issue you've brought up above (the blog posting is at: http://blogs.msdn.com/sqlnativeclient/archive/2006/09/27/774286.aspx ). They go on to specifically mention:

By wrapping the OLE DB and ODBC technologies into a single library, we are able to avoid these issues by making a clean break from MDAC so that you can effectively deploy SQL Native Client as needed, without concern about if it will “play nicely” with other versions of MDAC.

So, for you app today, you can build against the SQL Server Native Client and deploy that application (including the SQL Server Native Client redistributable) to your clients without breaking existing MDAC based applications.

As to the future, making this clean break only to commit to the same course of action with the new SQL Server Native Client would be less than ideal and we are actively considering how best to support this need in the next release that will come with future versions of Microsoft SQL Server. If you have specific scenarios or issues you would like considered, we would very much like to hear your ideas.

Multiple SP Versions

Hi,
I have a database, the interface to which is completely implemented as a set
of stored procedures. I want the application which uses the database to use
side-by-side versioning, i.e. there may be more than one version of the
application current and needing to access the database.
As you can imagine, over time and versions the interface to the database may
change, so I will need to have a set of stored procedures for each version
of the application that is current. What I am thinking about is, for each
version, to have a "shell" database on the same server which only has the
stored procedures which acess the tables on the "real" database... hence
keeping separate the interface for each version of the application.
Does anyone have any comments on the performance of this arrangement? Or a
better way to go about it?
Regards
Greg BacchusHi
Stored procedures can contain a verson number see "; number" in http://msdn.micros
oft.com/l...e=tr
ue. I would not call the stored procedure through the application but not by
using dynamic
SQL everywhere.
You may well have serious maintenance issue with running more than a few ver
sions of a stored procedure, therefore you may want to think about a more fo
rmal upgrade process.
John