Problems instancing Reporting Services

I set up Reporting Services as a Web Reference and when I try to instance it, I get a "type expected" error message. Let's say the web reference is named SQLRS. When I do a DIM rs as NEW SQLRS, a blue squiggly appears under SQLRS and it displays the type expected error message.

What am I missing? Anyone know or have a link to an example I could look at? Thanks for any help.Additional information:

When I bring up the Reporting Services as a web reference, I don't get a drop down list of the methods and properties that are listed in the Add a Web Reference dialogue box and from what I've seen in examples online. For instance, there should be a ListChildren method, but I don't see it when intellisense kicks in.

Example: Let's say I name the web service ReportingService. If I try to instance it by going DIM rs as NEW ReportingService, I get the blue squiggly line complaining that "Type expected". If I type ReportingService. intellisense kicks in, but what appears in the drop down box is not what I expect. There are no ListChildren, ListEvents, ListJobs, etc.

What I suspect is that I need to DIM the ReportingService with one of the selections from the intellisense dropdown box, but I have no idea which one as all the examples I've seen online show differently. Has there been changes to this with service pack 2 that I'm not aware of? Any help would be appreciated.|||Ok, I just love answering my own questions.

It turns out that all the examples I've been trying to follow are a bit off. To create an instance of the Reporting Services as a web service, first add the web service as a web reference to your project. (i.e., right click on references in the solution explorer and type in the web address of the reporting services: http://myserver/ReportServer/ReportService.asmx)

Then, to create an instance, use something like:

DIM rs as NEW ReportingService.ReportingService

Now you will have an object called rs that will have the properties and methods of the ReportingService web service such as ListChildren, ListJobs, Render, etc.

Problems installing MSDE

Hi

I've recently downloaded and installed MSDE. I used the instructions from the Microsoft website that makes it as an instance of VSDOTNET. When I rebooted after installing it, there was absolutely no service for the SQL Server Service Manager to do anything with - the icon was in my tray with a blank circle, not a green Play or a red Stop symbol. So I uninstalled it, with an aim to reinstalling it as I had done before - without specifying an instance name.

Only trouble is, I'm now trying to reinstall it using:

setup SAPWD=<pword> SecurityMode=SQL

and I'm getting an error message that says "Setup failed to configure the server. Refer to the serveer error logs and setup error logs for more information." when the progress bar is about 2-thirds of the way along. It does this when I try to install as an instance of VSDOTNET again. It just won't play, and I don't know how to get at the logs to see what's up.

I'm running Win2K SP3, with .NET 1.1. Anyone able to help me?

Thanks
JonCheck to see if you are installing SP3a. If so include the DISABLENETPROTOCOLS=0 in the setup command line. This will ensure that you have enabled the network protocols for a new install. You also don't have to create an instance at installation time.sql

Problems creating mirroring on 2005.

SQL2K5 sp1
Instance names:
Development\MySQL2K5
Development\Subscriber
Development\Witness
Im trying to set up mirroring on 3 instances on my desktop and having some
issues (not ideal I know, but it's what I've got to work with for now). Here
is what I've done:
--Create endpoint on principal instance and set recovery model
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
--Create endpoint on mirror instance
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
--Create endpoint on witness instance
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5024 )
FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO
--Backup principal database
BACKUP DATABASE AdventureWorks
TO DISK='C:\MirrorBackup\AWBackup.BAK'
with init;
GO
--Restore mirror database
RESTORE DATABASE AdventureWorks
FROM DISK='C:\MirrorBackup\AWBackup.BAK'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
GO
--On the mirror server set the principal server
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://MySQL2K5:5022'
GO
--On the principal server set the mirror server
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://MySQL2K5:5023'
GO
That last step blows up with:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://MYSQL2K5:5023" can not be reached or does
not exist. Check the network address name and that the ports for the local
and remote endpoints are operational.
Any ideas?
TIA, ChrisRWhat about the security configuration? Have you granted connect permissions
to the other servers' accounts to the database mirroring endpoints?
You should use
grant connect on endpoint::endpoint_mirroring to [domain\account]
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> SQL2K5 sp1
> Instance names:
> Development\MySQL2K5
> Development\Subscriber
> Development\Witness
> Im trying to set up mirroring on 3 instances on my desktop and having some
> issues (not ideal I know, but it's what I've got to work with for now). Here
> is what I've done:
> --Create endpoint on principal instance and set recovery model
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5022 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> ALTER DATABASE AdventureWorks
> SET RECOVERY FULL;
> --Create endpoint on mirror instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5023 )
> FOR DATABASE_MIRRORING (ROLE=PARTNER);
> GO
> --Create endpoint on witness instance
> CREATE ENDPOINT endpoint_mirroring
> STATE = STARTED
> AS TCP ( LISTENER_PORT = 5024 )
> FOR DATABASE_MIRRORING (ROLE=WITNESS);
> GO
> --Backup principal database
> BACKUP DATABASE AdventureWorks
> TO DISK='C:\MirrorBackup\AWBackup.BAK'
> with init;
> GO
> --Restore mirror database
> RESTORE DATABASE AdventureWorks
> FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> WITH NORECOVERY,
> MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> GO
> --On the mirror server set the principal server
> ALTER DATABASE AdventureWorks
> SET PARTNER => 'TCP://MySQL2K5:5022'
> GO
> --On the principal server set the mirror server
> ALTER DATABASE AdventureWorks
> SET PARTNER = 'TCP://MySQL2K5:5023'
> GO
> That last step blows up with:
> Msg 1418, Level 16, State 1, Line 1
> The server network address "TCP://MYSQL2K5:5023" can not be reached or does
> not exist. Check the network address name and that the ports for the local
> and remote endpoints are operational.
> Any ideas?
> TIA, ChrisR
>
>|||That didn't fix it. As an FYI all 3 instances are running under the same
domain account.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> What about the security configuration? Have you granted connect
permissions
> to the other servers' accounts to the database mirroring endpoints?
> You should use
> grant connect on endpoint::endpoint_mirroring to [domain\account]
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > SQL2K5 sp1
> >
> > Instance names:
> >
> > Development\MySQL2K5
> > Development\Subscriber
> > Development\Witness
> >
> > Im trying to set up mirroring on 3 instances on my desktop and having
some
> > issues (not ideal I know, but it's what I've got to work with for now).
Here
> > is what I've done:
> >
> > --Create endpoint on principal instance and set recovery model
> > CREATE ENDPOINT endpoint_mirroring
> > STATE = STARTED
> > AS TCP ( LISTENER_PORT = 5022 )
> > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > GO
> > ALTER DATABASE AdventureWorks
> > SET RECOVERY FULL;
> > --Create endpoint on mirror instance
> > CREATE ENDPOINT endpoint_mirroring
> > STATE = STARTED
> > AS TCP ( LISTENER_PORT = 5023 )
> > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > GO
> > --Create endpoint on witness instance
> > CREATE ENDPOINT endpoint_mirroring
> > STATE = STARTED
> > AS TCP ( LISTENER_PORT = 5024 )
> > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > GO
> > --Backup principal database
> > BACKUP DATABASE AdventureWorks
> > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > with init;
> > GO
> > --Restore mirror database
> > RESTORE DATABASE AdventureWorks
> > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > WITH NORECOVERY,
> > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > GO
> > --On the mirror server set the principal server
> > ALTER DATABASE AdventureWorks
> > SET PARTNER => > 'TCP://MySQL2K5:5022'
> > GO
> > --On the principal server set the mirror server
> > ALTER DATABASE AdventureWorks
> > SET PARTNER = 'TCP://MySQL2K5:5023'
> > GO
> >
> > That last step blows up with:
> > Msg 1418, Level 16, State 1, Line 1
> > The server network address "TCP://MYSQL2K5:5023" can not be reached or
does
> > not exist. Check the network address name and that the ports for the
local
> > and remote endpoints are operational.
> >
> > Any ideas?
> > TIA, ChrisR
> >
> >
> >|||Let us try something else then.
Is MYSQL2K5 the name of your computer?
BOL recommends to use the fully qualified domain name. Try ipconfig /all and
look for host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> That didn't fix it. As an FYI all 3 instances are running under the same
> domain account.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> >
> > What about the security configuration? Have you granted connect
> permissions
> > to the other servers' accounts to the database mirroring endpoints?
> >
> > You should use
> >
> > grant connect on endpoint::endpoint_mirroring to [domain\account]
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "ChrisR" wrote:
> >
> > > SQL2K5 sp1
> > >
> > > Instance names:
> > >
> > > Development\MySQL2K5
> > > Development\Subscriber
> > > Development\Witness
> > >
> > > Im trying to set up mirroring on 3 instances on my desktop and having
> some
> > > issues (not ideal I know, but it's what I've got to work with for now).
> Here
> > > is what I've done:
> > >
> > > --Create endpoint on principal instance and set recovery model
> > > CREATE ENDPOINT endpoint_mirroring
> > > STATE = STARTED
> > > AS TCP ( LISTENER_PORT = 5022 )
> > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > GO
> > > ALTER DATABASE AdventureWorks
> > > SET RECOVERY FULL;
> > > --Create endpoint on mirror instance
> > > CREATE ENDPOINT endpoint_mirroring
> > > STATE = STARTED
> > > AS TCP ( LISTENER_PORT = 5023 )
> > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > GO
> > > --Create endpoint on witness instance
> > > CREATE ENDPOINT endpoint_mirroring
> > > STATE = STARTED
> > > AS TCP ( LISTENER_PORT = 5024 )
> > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > GO
> > > --Backup principal database
> > > BACKUP DATABASE AdventureWorks
> > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > with init;
> > > GO
> > > --Restore mirror database
> > > RESTORE DATABASE AdventureWorks
> > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > WITH NORECOVERY,
> > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > GO
> > > --On the mirror server set the principal server
> > > ALTER DATABASE AdventureWorks
> > > SET PARTNER => > > 'TCP://MySQL2K5:5022'
> > > GO
> > > --On the principal server set the mirror server
> > > ALTER DATABASE AdventureWorks
> > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > GO
> > >
> > > That last step blows up with:
> > > Msg 1418, Level 16, State 1, Line 1
> > > The server network address "TCP://MYSQL2K5:5023" can not be reached or
> does
> > > not exist. Check the network address name and that the ports for the
> local
> > > and remote endpoints are operational.
> > >
> > > Any ideas?
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||The box name is Development. The instance name is MySQL2K5. I have tried
development\mysql2k5 and many variations of it and get incorrect syntax
errors. I also tried the FQDN and still got the original error.
Thanks for all the help.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> Let us try something else then.
> Is MYSQL2K5 the name of your computer?
> BOL recommends to use the fully qualified domain name. Try ipconfig /all
and
> look for host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > That didn't fix it. As an FYI all 3 instances are running under the same
> > domain account.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > >
> > > What about the security configuration? Have you granted connect
> > permissions
> > > to the other servers' accounts to the database mirroring endpoints?
> > >
> > > You should use
> > >
> > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "ChrisR" wrote:
> > >
> > > > SQL2K5 sp1
> > > >
> > > > Instance names:
> > > >
> > > > Development\MySQL2K5
> > > > Development\Subscriber
> > > > Development\Witness
> > > >
> > > > Im trying to set up mirroring on 3 instances on my desktop and
having
> > some
> > > > issues (not ideal I know, but it's what I've got to work with for
now).
> > Here
> > > > is what I've done:
> > > >
> > > > --Create endpoint on principal instance and set recovery model
> > > > CREATE ENDPOINT endpoint_mirroring
> > > > STATE = STARTED
> > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > GO
> > > > ALTER DATABASE AdventureWorks
> > > > SET RECOVERY FULL;
> > > > --Create endpoint on mirror instance
> > > > CREATE ENDPOINT endpoint_mirroring
> > > > STATE = STARTED
> > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > GO
> > > > --Create endpoint on witness instance
> > > > CREATE ENDPOINT endpoint_mirroring
> > > > STATE = STARTED
> > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > GO
> > > > --Backup principal database
> > > > BACKUP DATABASE AdventureWorks
> > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > with init;
> > > > GO
> > > > --Restore mirror database
> > > > RESTORE DATABASE AdventureWorks
> > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > WITH NORECOVERY,
> > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > GO
> > > > --On the mirror server set the principal server
> > > > ALTER DATABASE AdventureWorks
> > > > SET PARTNER => > > > 'TCP://MySQL2K5:5022'
> > > > GO
> > > > --On the principal server set the mirror server
> > > > ALTER DATABASE AdventureWorks
> > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > GO
> > > >
> > > > That last step blows up with:
> > > > Msg 1418, Level 16, State 1, Line 1
> > > > The server network address "TCP://MYSQL2K5:5023" can not be reached
or
> > does
> > > > not exist. Check the network address name and that the ports for the
> > local
> > > > and remote endpoints are operational.
> > > >
> > > > Any ideas?
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||On
ALTER DATABASE AdventureWorks
> > > > > SET PARTNER => > > > > 'TCP://MySQL2K5:5022'
you need to specify your host name and domain name NOT the SQL Server
instance name. For example, development.yourdomain.com.
Run ipconfig /all and look into host name and primary dns suffix.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> The box name is Development. The instance name is MySQL2K5. I have tried
> development\mysql2k5 and many variations of it and get incorrect syntax
> errors. I also tried the FQDN and still got the original error.
> Thanks for all the help.
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> >
> > Let us try something else then.
> >
> > Is MYSQL2K5 the name of your computer?
> >
> > BOL recommends to use the fully qualified domain name. Try ipconfig /all
> and
> > look for host name and primary dns suffix.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "ChrisR" wrote:
> >
> > > That didn't fix it. As an FYI all 3 instances are running under the same
> > > domain account.
> > >
> > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > > >
> > > > What about the security configuration? Have you granted connect
> > > permissions
> > > > to the other servers' accounts to the database mirroring endpoints?
> > > >
> > > > You should use
> > > >
> > > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "ChrisR" wrote:
> > > >
> > > > > SQL2K5 sp1
> > > > >
> > > > > Instance names:
> > > > >
> > > > > Development\MySQL2K5
> > > > > Development\Subscriber
> > > > > Development\Witness
> > > > >
> > > > > Im trying to set up mirroring on 3 instances on my desktop and
> having
> > > some
> > > > > issues (not ideal I know, but it's what I've got to work with for
> now).
> > > Here
> > > > > is what I've done:
> > > > >
> > > > > --Create endpoint on principal instance and set recovery model
> > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > STATE = STARTED
> > > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > GO
> > > > > ALTER DATABASE AdventureWorks
> > > > > SET RECOVERY FULL;
> > > > > --Create endpoint on mirror instance
> > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > STATE = STARTED
> > > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > GO
> > > > > --Create endpoint on witness instance
> > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > STATE = STARTED
> > > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > > GO
> > > > > --Backup principal database
> > > > > BACKUP DATABASE AdventureWorks
> > > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > with init;
> > > > > GO
> > > > > --Restore mirror database
> > > > > RESTORE DATABASE AdventureWorks
> > > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > WITH NORECOVERY,
> > > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > > GO
> > > > > --On the mirror server set the principal server
> > > > > ALTER DATABASE AdventureWorks
> > > > > SET PARTNER => > > > > 'TCP://MySQL2K5:5022'
> > > > > GO
> > > > > --On the principal server set the mirror server
> > > > > ALTER DATABASE AdventureWorks
> > > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > > GO
> > > > >
> > > > > That last step blows up with:
> > > > > Msg 1418, Level 16, State 1, Line 1
> > > > > The server network address "TCP://MYSQL2K5:5023" can not be reached
> or
> > > does
> > > > > not exist. Check the network address name and that the ports for the
> > > local
> > > > > and remote endpoints are operational.
> > > > >
> > > > > Any ideas?
> > > > > TIA, ChrisR
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Doh! I thought it was instance name.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:5B6FD5FC-E687-4B1F-A01B-57C0C265D5DE@.microsoft.com...
> On
> ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> you need to specify your host name and domain name NOT the SQL Server
> instance name. For example, development.yourdomain.com.
> Run ipconfig /all and look into host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > The box name is Development. The instance name is MySQL2K5. I have tried
> > development\mysql2k5 and many variations of it and get incorrect syntax
> > errors. I also tried the FQDN and still got the original error.
> >
> > Thanks for all the help.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> > >
> > > Let us try something else then.
> > >
> > > Is MYSQL2K5 the name of your computer?
> > >
> > > BOL recommends to use the fully qualified domain name. Try ipconfig
/all
> > and
> > > look for host name and primary dns suffix.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "ChrisR" wrote:
> > >
> > > > That didn't fix it. As an FYI all 3 instances are running under the
same
> > > > domain account.
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > > > >
> > > > > What about the security configuration? Have you granted connect
> > > > permissions
> > > > > to the other servers' accounts to the database mirroring
endpoints?
> > > > >
> > > > > You should use
> > > > >
> > > > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "ChrisR" wrote:
> > > > >
> > > > > > SQL2K5 sp1
> > > > > >
> > > > > > Instance names:
> > > > > >
> > > > > > Development\MySQL2K5
> > > > > > Development\Subscriber
> > > > > > Development\Witness
> > > > > >
> > > > > > Im trying to set up mirroring on 3 instances on my desktop and
> > having
> > > > some
> > > > > > issues (not ideal I know, but it's what I've got to work with
for
> > now).
> > > > Here
> > > > > > is what I've done:
> > > > > >
> > > > > > --Create endpoint on principal instance and set recovery model
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET RECOVERY FULL;
> > > > > > --Create endpoint on mirror instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > --Create endpoint on witness instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > > > GO
> > > > > > --Backup principal database
> > > > > > BACKUP DATABASE AdventureWorks
> > > > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > with init;
> > > > > > GO
> > > > > > --Restore mirror database
> > > > > > RESTORE DATABASE AdventureWorks
> > > > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > WITH NORECOVERY,
> > > > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > > > GO
> > > > > > --On the mirror server set the principal server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> > > > > > GO
> > > > > > --On the principal server set the mirror server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > > > GO
> > > > > >
> > > > > > That last step blows up with:
> > > > > > Msg 1418, Level 16, State 1, Line 1
> > > > > > The server network address "TCP://MYSQL2K5:5023" can not be
reached
> > or
> > > > does
> > > > > > not exist. Check the network address name and that the ports for
the
> > > > local
> > > > > > and remote endpoints are operational.
> > > > > >
> > > > > > Any ideas?
> > > > > > TIA, ChrisR
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks a lot!
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:5B6FD5FC-E687-4B1F-A01B-57C0C265D5DE@.microsoft.com...
> On
> ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> you need to specify your host name and domain name NOT the SQL Server
> instance name. For example, development.yourdomain.com.
> Run ipconfig /all and look into host name and primary dns suffix.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "ChrisR" wrote:
> > The box name is Development. The instance name is MySQL2K5. I have tried
> > development\mysql2k5 and many variations of it and get incorrect syntax
> > errors. I also tried the FQDN and still got the original error.
> >
> > Thanks for all the help.
> >
> > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > news:2F186919-F424-4707-9320-68474A3A59C1@.microsoft.com...
> > >
> > > Let us try something else then.
> > >
> > > Is MYSQL2K5 the name of your computer?
> > >
> > > BOL recommends to use the fully qualified domain name. Try ipconfig
/all
> > and
> > > look for host name and primary dns suffix.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "ChrisR" wrote:
> > >
> > > > That didn't fix it. As an FYI all 3 instances are running under the
same
> > > > domain account.
> > > >
> > > > "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> > > > news:D8493E83-C7A3-4A76-9302-7FA2C77EE3EF@.microsoft.com...
> > > > >
> > > > > What about the security configuration? Have you granted connect
> > > > permissions
> > > > > to the other servers' accounts to the database mirroring
endpoints?
> > > > >
> > > > > You should use
> > > > >
> > > > > grant connect on endpoint::endpoint_mirroring to [domain\account]
> > > > >
> > > > > Ben Nevarez, MCDBA, OCP
> > > > > Database Administrator
> > > > >
> > > > >
> > > > > "ChrisR" wrote:
> > > > >
> > > > > > SQL2K5 sp1
> > > > > >
> > > > > > Instance names:
> > > > > >
> > > > > > Development\MySQL2K5
> > > > > > Development\Subscriber
> > > > > > Development\Witness
> > > > > >
> > > > > > Im trying to set up mirroring on 3 instances on my desktop and
> > having
> > > > some
> > > > > > issues (not ideal I know, but it's what I've got to work with
for
> > now).
> > > > Here
> > > > > > is what I've done:
> > > > > >
> > > > > > --Create endpoint on principal instance and set recovery model
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5022 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET RECOVERY FULL;
> > > > > > --Create endpoint on mirror instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5023 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=PARTNER);
> > > > > > GO
> > > > > > --Create endpoint on witness instance
> > > > > > CREATE ENDPOINT endpoint_mirroring
> > > > > > STATE = STARTED
> > > > > > AS TCP ( LISTENER_PORT = 5024 )
> > > > > > FOR DATABASE_MIRRORING (ROLE=WITNESS);
> > > > > > GO
> > > > > > --Backup principal database
> > > > > > BACKUP DATABASE AdventureWorks
> > > > > > TO DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > with init;
> > > > > > GO
> > > > > > --Restore mirror database
> > > > > > RESTORE DATABASE AdventureWorks
> > > > > > FROM DISK='C:\MirrorBackup\AWBackup.BAK'
> > > > > > WITH NORECOVERY,
> > > > > > MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Data.mdf',
> > > > > > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > > > > > Server\MSSQL.2\MSSQL\DATA\AdventureWorks_Log.ldf';
> > > > > > GO
> > > > > > --On the mirror server set the principal server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER => > > > > > 'TCP://MySQL2K5:5022'
> > > > > > GO
> > > > > > --On the principal server set the mirror server
> > > > > > ALTER DATABASE AdventureWorks
> > > > > > SET PARTNER = 'TCP://MySQL2K5:5023'
> > > > > > GO
> > > > > >
> > > > > > That last step blows up with:
> > > > > > Msg 1418, Level 16, State 1, Line 1
> > > > > > The server network address "TCP://MYSQL2K5:5023" can not be
reached
> > or
> > > > does
> > > > > > not exist. Check the network address name and that the ports for
the
> > > > local
> > > > > > and remote endpoints are operational.
> > > > > >
> > > > > > Any ideas?
> > > > > > TIA, ChrisR
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Problems Creating Instance in SQL 2005 management studio

Hello all,

Thanks in advance for any help you can provide.

I am having a problem creating a new NS instance using SQL 2005 management

studio.

I am using the database element in my instance and application xml files like

this,

in order to create the NS application in an existing database.

<Database>

<DatabaseName>%databasename%</DatabaseName>

<SchemaName>%schemaname%</SchemaName>

</Database>

the problem that I am having is that if I encounter an error somewhere in the

process of creating this new instance the instance name and schema name I

specified are still bound somewhere, and if I correct for the error and try to

create the instance again, I receive a new error saying that there is already

an instance with my instance name, or using the schema name.

If I try to list versions it tells me there are no instances, yet if I try to

add a new instance it tells me there is already an instance using my instance

name.

I have tried searching master database tables and views for anywhere

where it might be stored, also tried poking around most of management studio

and the registry, and just can not seem to find where it is remembering that I

had tried to create this instance before.

It is like it is partially creating the instance, running into an error, and

then not rolling back its changes somewhere.
Any help would be very much appreciated

-Derek-

Web Services

Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation,

are men who want crops without plowing up the ground. They want rain without

thunder and lightning. They want the ocean without the awful roar of its

many waters. Power concedes nothing without demand. It never did and it

never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT * FROM NS90.NSInstanceInfo

SELECT * FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-

Problems connecting to SSAS instance over HTTP in Excel 2003

Our Biz users are all running Excel 2003, and we've recently set up a bunch of cubes that we'd like to open up to them.

I followed the steps here: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
to set up HTTP access to SSAS2005. It works great from my machine, I figured because I had SQL 05 installed locally and all the requisite drivers, etc.

On our biz users' desktops, however, it wasn't as peachy.

I first figured out I had to install MSXML6 and the SSAS 9.0 driver on their machine to connect to the cube. I downloaded those from here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Once I was able to set up the connection to the cube, and about to create a pivot table in Excel, the Query wizard failed, with this error:

"XML parsing failed at line 1, column 9: DTD is prohibited"

Googling didn't seem to uncover anything useful, has anyone seen this before?

Not sure how google works, but using Windows Live Search (www.live.com) discovered the following thread:

http://www.topxml.com/MS-XML-Analysis/rn-132023_HTTP-XML/A-access-to-AS2005-cause-an-error.aspx

In the thread user Jeje attached the HTTP trace, which is pretty helpful - it shows that first client components tried to use 9.0 driver, but then falled back to 8.0 driver, which is probably the reason for the error. Why did it fall back to 8.0 - perhaps a permission issue ?

|||Whoops, now I feel bad for not using Live . I'll have to be sure to use both now!

Mosha - security sounds like it could be a probable cause. I'll try experimenting with permission issues tomorrow. Also, on a related thread in the same forum a MS employee noted:

Sent date: 01/06/2006
From: "Akshai Mirchandani [MS]" <(email address - cut out)>
Message:
This is a known issue -- a hotfix should be available soon from support.

Thanks,

Akshai

(http://cc.msnscache.com/cache.aspx?q=4937159748319&lang=en-US&mkt=en-US&FORM=CVRE2)

Are you aware of any related hotfixes that address this issue?|||Argh, I tried completely loosening security on the olap/ folder and its children, but this doesn't seem to help at all. I checked our IIS logs and like in Mosha's link the client seems to be attempting the 9.0 driver and then falling back to the 8.0 driver, which of course doesn't work.

Anyone know if there's a hotfix for this?|||Interesting. Changing IIS auth to anonymous seems like it completely solves this problem...although I'm unsure of the security implications -

Client -> IIS (msmdpump.dll) -> SSAS

If IIS is not using windows auth, does SSAS just use the anonymous user's credentials (ie the IUSR_ acct) to authenticate?|||

If you run Profiler against the Analysis Server, you can see which user attempted to connect -- if it was the IUSR_xxx or similar user then you know it is a security issue and the Pump is not able to delegate the user credentials to the Analysis Server for some reason.

I believe the hotfix was about using Basic authentication which doesn't seem to be your scenario.

Thanks,

Akshai

|||

Hi all,

I've also been encountering issues with HTTP access to SSAS 2005, but with excel 2007.

Although it's working from excel 2003 (but only if the option to save the username/password is checked when creating the data source for the pivot table), it won't let me access it from excel 2007. I always get an access denied-like message when i try to set up the connection to the data source.

We haven't been able to identify where is the issue. We set the HTTP access as via the link mentionned before, but that won't let us access from excel 2007.

We've also been facing some backward compatibility issues using excel 2003 with OLE DB provider for analysis services 9.0 installed.

With it we can not connect to our SSAS 2000 cubes (again via http) anymore, the error is the response returned via the HTTP server is not valid (even though we try using the OPAL 8.0 driver).

Any idea how we could solve that issue? did we miss a component to install to ensure compatibility with both version of SSAS?

thanks in advance for your help.

Cheers, and happy new year!

sql

Problems connecting to SQL Server DB using Perl CGI

Please help. I am at my wits end. :confused:

I have SQL Server 2000 on a Windows 2003 server. I have my database on a Nmade Instance . Named Pipes is disabled and only TCP/IP is enabled. Authentication is set to Windows only. The server is in Active Directory.

My web site is on a Windows 2000 server with IIS 5. I have installed the Client Network Utilities and set the alias to TCP/IP also. I can connect to the SQL server using the Query Analyzer.

I have downloaded and installed ActiveState's ActivePerl 5.6.1.638-MSWin32-x86.msi on both machines. I have also installed DBI and DBD::ODBC on both machine. Both machine have MDAC 2.8 installed.

The code I am using to connect is as follows:
my $user = 'user';
my $pass = 'password';
my $db = "database";
my $server = "servername\\instancename";
my $ipadd = "xxx.xx.xx.xxx,xxxx";
my $driver = '{SQL Server}';
Option #1
$DSN = 'driver=$driver;Server=$server;uid=$user;pwd=$pass ;database=$db;network=DBMSSOCN;address=$ipadd;';
$dbh = DBI->connect("dbi:ODBC:$DSN") or die 'Cannot Open SQL Server Database $DBI::errstr\n';

Option #2
$DSN = 'driver=$driver;Server=$server;uid=$user;pwd=$pass ;database=$db';
$dbh = DBI->connect("dbi:ODBC:$DSN") or die 'Cannot Open SQL Server Database $DBI::errstr\n';

Option #3
$dbh = DBI->connect('dbi:ODBC:driver=$driver:dsn',$user,$pass) or die 'Cannot Open SQL Server Database $DBI::errstr\n';

Option #4
$dbh = DBI->connect("dbi:ODBC:database=$db;server=$server",$user,$pass) or die '$DBI::errstr\n';

From all that I have read and from previous scripts I have written, any of these should work, but I am getting the following error.

DBI connect('driver=$driver:dsn','user',...) failed: at C:\cgi-bin\CGI.pl line xxx Cannot Open SQL Server Database $DBI::errstr\n at C:\cgi-bin\CGI.pl line xxx.
Where xxx = the line number of the connection call.

All of my previous scripts have been written for forms where the web server(Apache), database(MySQL), and script(Perl CGI) are all on the same machine. I am not at all familiar with SQL Server, and am struggling to learn quickly. PLease and ideas would be greatly appreciated.
Thank you
LANACI don't know Perl, but VBScript would look very simple using Option #1:

user = "user"
pass = "password"
db = "database"
server = "servername\\instancename"
ipadd = "xxx.xx.xx.xxx,xxxx"
driver = "{SQL Server}"
connect_string="driver=" & driver & ";server=" & server & ";uid=" & user & ";pwd=" & pass & ";database=" & db & ";network=dbmssocn"|||Try setting up a SQL Profiler trace on the database server. Trace all error/warning events, and see if you are getting anything at all from the client.

I am afraid I have never worked with DBI, so I can not tell you if there is anything obviously wrong in the code.

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmaz
themarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Problem: DTS parallel tasks running sequentially

Hi

I have a SQL Server 2000 instance running on a Windows Server 2003 box with 4 processors. SQL Server is configured to use all 4 processors, and use all available processors for parallelism.

I have created a simple DTS package which has 2 "execute external process" tasks with no precedence constraints between them. There are no connections required or defined for the two tasks (sequential
processing is forced on tasks sharing connections). The DTS package
properties have the "limit the number of tasks to execute in parallel"
set to 4.

However, despite the above configuration, the two steps are never executed in parallel, but always sequentially.

Does anyone have any ideas as to why these tasks are not being executed in parallel?

Any suggestions welcome.

Thanks.My first guess would be that you've established a precedence between the tasks by creating either a success or a failure connector between them. This forces DTS to complete the first task in order to know if it succeeds or fails before it can start the second task. Remove the presedence, and they ought to run in parallel.

-PatP|||Pat

Thanks for the reply but as I stated in my original post, there are no precedent constraints in place.

I have subsequently done further testing and it seems related to the task type. I can successfully run external commands in parallel via the ActiveX script task shelling out to call the executables, but cannot acheive parallel running using the "Execute External Process" task type.