Merging two dbs: working

Showing posts with label working. Show all posts

Friday, March 30, 2012

Microsoft SQL Server Certifications

Hello all,

I'm working as DBA.I wish to do certifications for Microsoft sql server.So Could i know what are the certifications are available and what i've to do for certififcations.

Thanks in advance
David RPlease find all the details here.

Wednesday, March 28, 2012

Microsoft SQL SERVER 2000 not working!

I'm unable to gain connectivity to two stand- alone PC's having WIN XP PROFESSIONAL, v. 2002, through Microsoft SQL SERVER 2000. :eek:

Both the PC's are stand-alone and have both have WIN XP PROF. I installed Microsoft SQL SERVER 2000 in which only the Client Components were successfully installed.

I have the Administrator Rights on both the PC's.

The Problem on both the PC's that I'm facing is as follows:-

On starting SQL Server Enterprise Manager, the PC was not detected as a Server under the SQL Server Group, and thus I selected the option 'New SQL Server Registration', then I gave my full computer name (as appears in the properties of 'my computer'), selected Windows Authentication and then selected OK to start or connect.

It did not connect and then I got the following error message:

--------------
A connection could not be established to 'COMPUTER NAME'.

Reason: SQL Server does not exist or access denied.

ConnectionOpen(Connect())

Please verify SQL Server is running and check your SQL Server registration property (by right click on the COMPUTER NAME_node) and try again.

OK

--------------

On starting the SQL Query Analyzer, firstly the message connecting to 'COMPUTER NAME' was seen and then it also gave the following error message:

--------------
Unable to connect to server 'COMPUTER NAME':

Server: Msg 17, Level 16, State 1

[Microsoft][ODBC SQL Server Driver][Shared Memory] SQL Server does not exist or access denied.

OK

--------------

where the 'COMPUTER NAME' stands for the name of the particular system that can be seen in System Properties-> Computer Name-> Full computer name.

I had installed the MDAC Components from the Windows Update website.

I checked the registration properties and have tried many times but everytime the same error messages were received in both the systems and all the efforts bore no fruits.

Both the systems are:
Intel P4
WINXP PROF. v2002
128MB DDR RAM
40 GB HDD

Please help soon as I am studying SQL Server!

THANKS. ;)You can only install client tools on a XP professional machine, but not the database (server part) itsself. You have to install the database on a Windows 2K(3) Server system. Another solution is to use the SQL Server 2000 developer edition, which can be installed on XP computers.|||Alternatively install MSDE!|||MSDE is a good alternative to use instead of the SQL, but probably not the best choice if you want to study on the functionalities of the SQL Server and its tools as MSDE does not have the graphical interface.|||This is true Johan.
However in the absence of a server product and with client tools already installed it'd be better than nowt.|||I was able to install the same on my PC having WINXP HOME, then why not on the other two systems, also having WINXP PROF.

THANKS TO ALL HELPING MEMBERS - ATISH ;)

"Compatibilty without Flexibility is a Liability." :up:

My System:
MODEL: COMPAQ PRESARIO 6171AI
INTEL P4-1.9 GHZ
256MB + 256MB(SIMMTRONICS) = 512MB DDRSD RAM
40GB MAXTOR 6E040L0 HDD
WINDOWS XP HOME EDITION
[5.1 (BUILD 2600) SERVICE PACK 2. RC 1 v.2096]
SOUNDMAX INTEGRATED DIGITAL AUDIO|||Sorry Atish does that mean you got it working.
See http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp for required specs.

J|||I actually have that setup in my house without a problem. Are you sure that the two computers can see each other? And you said that you registered the server with just the computer name? Make sure that you have the [computer name]\[named instance of the server]:
e.g. MyComputer\MSSQL2K...|||I actually have that setup in my house without a problem. Are you sure that the two computers can see each other? And you said that you registered the server with just the computer name? Make sure that you have the [computer name]\[named instance of the server]:
e.g. MyComputer\MSSQL2K...
The two computers are stand-alone and not networked. Installation is to be done on a stand-alone.|||Sorry Atish does that mean you got it working.
See http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp for required specs.

J
As you said that even if it gets installed it will not work.

Now that I'm able to:
1. Access and change the SQL Server Properties Page,
2. Use the Query Analyzer for making tables and doing queries,
3. Use the Enterprise Manager for starting and stopping the services of the Server, create new Database, etc.

Now that above all is almost the entire functionality of a SQL Server 2000 that I'm having on my stand-alone PC whose
configuration is as stated under.

When it is installable on my system, then it must be possible to install it and make it work on a stand-alone PC having WINXP PRO which is more advanced.

How to make this possible?|||What version of SQL Server have you installed? Standard, enterprise, personal, developer?|||What version of SQL Server have you installed? Standard, enterprise, personal, developer?
I have installed SQL Server 2000 Enterprise Edition.|||When you installed it, did you create the instance with the same name as your computer?

I did this once and had to connect to the SQL Server with <computer name>\<computer name>, eg: WinPC1\WinPC1|||When you installed it, did you create the instance with the same name as your computer?

I did this once and had to connect to the SQL Server with <computer name>\<computer name>, eg: WinPC1\WinPC1
I did not have to do it. Installation took it by default, i.e., (LOCAL)|||can you provide the output of
Select @.@.version
GO
Select Serverproperty('Servername'),
Serverproperty('Edition'),
Serverproperty('Engine Edition'),
Serverproperty('InstanceName')
go

from the Install which is working. I highly doubt it is Enterprise Version|||can you provide the output of
Select @.@.version
GO
Select Serverproperty('Servername'),
Serverproperty('Edition'),
Serverproperty('Engine Edition'),
Serverproperty('InstanceName')
go

from the Install which is working. I highly doubt it is Enterprise Version
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2, v.2096)

CPQ81732947724
Desktop Engine
NULL
NULL

The above is the output of the Query.|||I thought so..

If it was installed with Ent Edition CD it would show result like this

Server_name
Enterprise Edition
3
Null

BTW there is a typo 'Engine Edition' is one word "EngineEdition"

Seems like you have MSDE Installed or you have a personal edition installed or you may have developers edition

Usually the MSDE comes with Visual Studio stuff if I remember correctly|||I thought so..

If it was installed with Ent Edition CD it would show result like this

Server_name
Enterprise Edition
3
Null

BTW there is a typo 'Engine Edition' is one word "EngineEdition"

Seems like you have MSDE Installed or you have a personal edition installed or you may have developers edition

Usually the MSDE comes with Visual Studio stuff if I remember correctly
But I am dead sure that I have installed it with an Enterprise Edition CD and I don't know whether MSDE can be installed even on installing Enterprise Edition that too with an SQL Server 2000 Enterprise Edition CD!|||You need to look on your CD. It probably has multiple versions. You definitely don't have Enterprise Edition if that's what it showed.|||You need to look on your CD. It probably has multiple versions. You definitely don't have Enterprise Edition if that's what it showed.

I only have the 'exe' of Microsoft SQL Server 2000 Enterprise Edition CD from which I have installed my SQL Server. This can also be verified from the screen-shot/Print Screen view that I have attached with this post named 'Installation.JPG'. That is the utmost I feel I can provide to prove that I tried to install from Enterprise Edition CD. :)|||Please take a note of the installation. Images further to this attachment will follow in the next post.|||In continuation to the last post!
Further images contd. in next post...|||Please take a final look into what has been installed!
Now can anyone tell that even inspite of having installed from purely an Enterprise Edition CD how has MSDE been installed on my system from the same installation and how is it working.
And even if it is working, how to make the same thing work on the other PC that I had mentioned about, in the earlier posts in this thread, having WIN XP PRO.|||:) I missed the XP PRO part earlier. The only thing you are installing when you use this machine is the client tools (Query Analyzer and Enterprise Manager). You are only seeing MSDE because it was already installed.

You cannot install the Enterprise Edition on XP PRO. You will need to buy the Developers Edition or just continue to use MSDE. If you want it to work on another XP PRO machine, you can download the MSDE version of SQL Server from:

http://www.microsoft.com/sql/msde/downloads/default.asp

You can then install the Client tools from your CD.|||:) I missed the XP PRO part earlier. The only thing you are installing when you use this machine is the client tools (Query Analyzer and Enterprise Manager). You are only seeing MSDE because it was already installed.

You cannot install the Enterprise Edition on XP PRO. You will need to buy the Developers Edition or just continue to use MSDE. If you want it to work on another XP PRO machine, you can download the MSDE version of SQL Server from:

http://www.microsoft.com/sql/msde/downloads/default.asp

You can then install the Client tools from your CD.
How did MSDE got installed on my machine when I didn't do it myself?

Microsoft Search service vulnerabilities

I am working on a "best practices" document for my company and came across
the suggestion to disable any unnecessary SQL Server services. We do not us
e
full-text search so we have no need for the service. However, I would like
to be able to provide reasons why this service should be disabled. I've
searched for vulnerabilites related to the Microsoft Search service and have
come up empty. Does anyone know of any vulnerabilities?
Thanks!I'm not aware of any vulnerabilities but it's still a good idea to shut it
down if you know you are not going to use it. That way, if someone comes up
with an attack later, you aren't at risk. Reducing the surface that an
attacker can attack to only what's required is always a good idea.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cltdba" <cltdba@.discussions.microsoft.com> wrote in message
news:14FA55C3-C274-458F-B7A8-ABB90EAD3E2C@.microsoft.com...
>I am working on a "best practices" document for my company and came across
> the suggestion to disable any unnecessary SQL Server services. We do not
> use
> full-text search so we have no need for the service. However, I would
> like
> to be able to provide reasons why this service should be disabled. I've
> searched for vulnerabilites related to the Microsoft Search service and
> have
> come up empty. Does anyone know of any vulnerabilities?
> Thanks!

Microsoft Search service can't start after SQL2005 installation!

Hi everybody I had a server with SQL2000 Standard on Server2003 Standard
working, but I was told to uninstall and install SQl2005 Standard, but now
when I start the server I receive an error saying that a service could not
start, I went to see what service the server was talking about and it was
the Microsoft Search service. The error says that the specified file can't
be found, so I went to "C:\Program Files\Common
Files\System\MSSearch\Bin\mssearch.exe" and the file mssearch.exe is
missing!!!
Was it the uninstall of SQL2000 that screwed this up, or the installation of
SQL2005?
I dont know what to do from here, what can I do?...Any help would be
appreciated.
Thanks in advance..Hi
The version numbers in your post seem to be inconsistent!
I assume that you have removed SQL 2000 and only have a named instance of
SQL 2005. If you want the instance of SQL 2005 to be the default instance you
will need to install SQL 2005 again anyhow, and possibly remove the named
instance of SQL 2005 if it is not wanted. If you want to reinstall the
current named instance you can install it from the command line with the
REINSTALLMODE=OMUS option see
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode
REINSTALLMODE=O will replace the missing file, but it would probably not do
any harm having the other options.
John
"PG" wrote:
> Hi everybody I had a server with SQL2000 Standard on Server2003 Standard
> working, but I was told to uninstall and install SQl2005 Standard, but now
> when I start the server I receive an error saying that a service could not
> start, I went to see what service the server was talking about and it was
> the Microsoft Search service. The error says that the specified file can't
> be found, so I went to "C:\Program Files\Common
> Files\System\MSSearch\Bin\mssearch.exe" and the file mssearch.exe is
> missing!!!
> Was it the uninstall of SQL2000 that screwed this up, or the installation of
> SQL2005?
> I dont know what to do from here, what can I do?...Any help would be
> appreciated.
> Thanks in advance..
>
>|||I'm sorry if I didn't make myself clear, what I did was uninstall SQL2000
and afterwards install SQL2005 on the same machine (WindowsServer2003).
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:82ECF7F8-D4A2-4213-9A68-76656BC41C79@.microsoft.com...
> Hi
> The version numbers in your post seem to be inconsistent!
> I assume that you have removed SQL 2000 and only have a named instance of
> SQL 2005. If you want the instance of SQL 2005 to be the default instance
> you
> will need to install SQL 2005 again anyhow, and possibly remove the named
> instance of SQL 2005 if it is not wanted. If you want to reinstall the
> current named instance you can install it from the command line with the
> REINSTALLMODE=OMUS option see
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode
> REINSTALLMODE=O will replace the missing file, but it would probably not
> do
> any harm having the other options.
> John
> "PG" wrote:
>> Hi everybody I had a server with SQL2000 Standard on Server2003 Standard
>> working, but I was told to uninstall and install SQl2005 Standard, but
>> now
>> when I start the server I receive an error saying that a service could
>> not
>> start, I went to see what service the server was talking about and it was
>> the Microsoft Search service. The error says that the specified file
>> can't
>> be found, so I went to "C:\Program Files\Common
>> Files\System\MSSearch\Bin\mssearch.exe" and the file mssearch.exe is
>> missing!!!
>> Was it the uninstall of SQL2000 that screwed this up, or the installation
>> of
>> SQL2005?
>> I dont know what to do from here, what can I do?...Any help would be
>> appreciated.
>> Thanks in advance..
>>|||Hi
"PG" wrote:
> I'm sorry if I didn't make myself clear, what I did was uninstall SQL2000
> and afterwards install SQL2005 on the same machine (WindowsServer2003).
>
mssearch.exe is for SQL 2000 if you want to reinstall it see
http://support.microsoft.com/default.aspx/kb/827449 but if you have SQL 2005
you should see an instance of SQL Server FullText Search (MSSQLSERVER) in
your services which runs "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\msftesql.exe". The article also shows how to
remove it.
John

Monday, March 26, 2012

Microsoft Please help-Web Service Problem

We had an app that called across to the web services to run batches of statements. Everything worked fine and now they stopped working. The report can still be ran from report manager. What things do we need to check to find the problem? Any help would be greatly appreciated.

Try turning on tracing and view the logs. Also see event viewer if there are any issues. It could be an issue with the app or connectivity instead of Reporting Services, since the web service is used to service Report Manager.

More info on logging.

http://msdn2.microsoft.com/en-us/library/ms156500.aspx

cheers,

Andrew

|||Thank you Andrew for your prompt response. We will look at the logs and see if that leads us in the right direction.|||

Some security patches were applied to the server in December. Is it possible that these could have messed with the way that authentication is handled?

Patches:

KB925398

KB925454

KB923689

KB911897

KB926247

KB923694

|||The app is getting the message back "The report server cannot open a connection to the report server database. A conection to the database is required for all requests and processing. SQL server does not exist or acess denied."|||Is it possible that any of these security patches could have messed with our authentication? Any suggestions Please.

Microsoft OLEDB Provider Oracle

Hi,

Previously i was using oracle8i with ssis.ssis was working fine.later i have upgraded 8i to oracle 9i.now when ever i try to establish a new connection using Microsoft OLEDB Provider for Oracle.i am getting the following error "Oracle error occurred, but error message could not be retrieved from Oracle" .but i am able to access the oracle 9i database thru pl/sql and toad. what could be the problem?

Thanks

Jegan.T

We're running fine 9.2 on a development server (2005) along with SSIS packages.

What about tnsnames.ora?

|||

Hi Enric,

my tnsnames.ora is correct .bcoz i am able to login via toad/sqlplus.while upgradeing i have unistalled 8i and installed 9i . do we need to install any drivers?

Thanks

Jegan.T

Friday, March 23, 2012

Microsoft OLE DB Provider for SQL Server error '80004005'

Hello Group,

I need some help in the worst way. I had a program/database I was working on a particular machine. I took all the data from there and placed it on a another machine. Now I'm unable to navigate through the web pages on the new machine. I am getting the error...

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

......asp, line 19

Can you please help!!

Quote:

Originally Posted by ConfusedDeuce

Hello Group,

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

......asp, line 19

Can you please help!!

On line 19, your connection to the database is failing. Are you on the same network segment/subnet as you previously were? What is the physical difference between the computers?|||

Quote:

Originally Posted by ConfusedDeuce

Hello Group,

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

......asp, line 19

Can you please help!!

How are you now specifying the server name when connecting to this machine?
You'll need to allow the server port on that new machine if it's running a firewall.|||

Quote:

Originally Posted by Motoma

On line 19, your connection to the database is failing. Are you on the same network segment/subnet as you previously were? What is the physical difference between the computers?

The thing is that I'm not completely sure what the other machine had on it because they data was copied over from that machine and the machine was immediately taken away. Now I am using VMWare and virtually connected to new machine (which shouldnt be the problem). I'm not sure of any of the specs of the last machine : (|||

Quote:

Originally Posted by r035198x

How are you now specifying the server name when connecting to this machine?
You'll need to allow the server port on that new machine if it's running a firewall.

I'm not connecting to the old machine anymore. That machine is out of the office and no longer in physical reach.|||

Quote:

Originally Posted by ConfusedDeuce

Do you have the SQL Server database up and running on the new machine? Have you added the appropriate accounts and permissions for the users?|||

Quote:

Originally Posted by Motoma

Do you have the SQL Server database up and running on the new machine? Have you added the appropriate accounts and permissions for the users?

Yes it is up and running. How do i add the accounts and permissions?|||

Quote:

Originally Posted by ConfusedDeuce

Yes it is up and running. How do i add the accounts and permissions?

Enterprise Manager -> Server -> Security -> Logins
Enterprise Manager -> Server -> Databases -> Database -> Users
Enterprise Manager -> Server -> Databases -> Database -> Roles|||

Quote:

Originally Posted by ConfusedDeuce

I'm not connecting to the old machine anymore. That machine is out of the office and no longer in physical reach.

Actually I was refering to that new machine. That error means that the connection to the database server was not done correctly. The reasons could be
wrong hostname being specified
firewall blocking connections
change of IP
wrong port being used or break in TCP/IP conection while accessing the database server
e.t.c

Microsoft Neural Network, how do I feed it ?

Hello there,

I'm working with Analysis sevices 2005 developer edition. Looking through the documentation i becomes apperent that the NN algorithm takes 255 input attributes by default. This can be changed to any integer value, OK....

My problem is that I want to feed the network with 40000 input variables. In order to do so, I will have to do a select:

SELECT fld1, fld2, ...... fld39999, fld40000

FROM tblSometable

However, this is not possible, as the books online describes it is only possible to return 4096 columns from a select statement.

Question : How do I populate a NN in AS2005, with nmore than 4096 inputs ?!

The limit may actually be smaller. SQL Server supports, I think, only 1024 columns

Analysis Services 2005 supports the concept of nested table which allows multiple related tables to be included in modeling.

With nested tables, Analysis Services can use much larger input sets (even hundreds of thousands) represented in multiple tables. Some details on the nested table concept are available here. http://msdn2.microsoft.com/en-us/library/ms175659.aspx

However, this is a data modeling issue and not related to the NN 255 default limit.

The NN algorithm (and many other algorithms included in SQL Server 2005) will always perform feature selection,using (by default) 255 input attributes. it means that, even when your data contains 40K inputs, NN will still use, by default, only the most significant 255. To increase this limit, change the value of the MAXIMUM_INPUT_ATTRIBUTES algorithm parameter. This parameter has the same name for all the algorithms that perform feature selection. If you cange this parameter value to, say, 40000, then all 40K inputs will be used.

|||

Thanks.

I suspected I could do this using nested tables. Thanks for confirming this. Time might be an issue, however. Mu current test-network consisting of 1000 inputs (MAX_INPUT_ATTRIB = 1000), that are zeros and ones, takes +3 hours on a 3.0Ghz PentiumD with 3 GB om memory. Is the time going to grow exponential or linear with the number of input nodes ?

regs,

Mads

Microsoft Neural Network, how do I feed it ?

Hello there,

My problem is that I want to feed the network with 40000 input variables. In order to do so, I will have to do a select:

SELECT fld1, fld2, ...... fld39999, fld40000

FROM tblSometable

However, this is not possible, as the books online describes it is only possible to return 4096 columns from a select statement.

Question : How do I populate a NN in AS2005, with nmore than 4096 inputs ?!

The limit may actually be smaller. SQL Server supports, I think, only 1024 columns

Analysis Services 2005 supports the concept of nested table which allows multiple related tables to be included in modeling.

However, this is a data modeling issue and not related to the NN 255 default limit.

|||

Thanks.

regs,

Mads

Wednesday, March 7, 2012

Metadata info retrieval

I am working on a project that needs to retrieve the metadata details of a s
chema (in MS SQL Server).
Could anyone help me out..
TIA,
ArrunHi
Check out the INFORMATION_SCHEMA views. More information can be found in
books online.
John
"Arrun S" <projdqe@.yahoo.co.in> wrote in message
news:CD63C343-75B4-4B6A-AE65-D9C552B25FAB@.microsoft.com...

quote:

> I am working on a project that needs to retrieve the metadata details of a

schema (in MS SQL Server).

quote:

> Could anyone help me out..
> TIA,
> Arrun

Saturday, February 25, 2012

Messages stopped appearing in Queue!

I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.

I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)

The database is still set to trustyworthy.

There are also no messages in sys.transmission_queue

I used profiler with all the broker events enabled and the result was as follows:

Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify

But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!

Thanks,

Andy

Hi,

I had the complete conversation - including the end of the conversation - in a stored procedure.

This was the reason i wasn't getting anything in sys.transmission_queue - i didnt end the conversation and the error appeared.

It turned out to be a permissions thing as when i'd restored the database it had set the owner to [SA]

Sorry for wasting your time if you'd bothered to read my problem!

Cheers,

Andy

Messages stopped appearing in Queue!

I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.

I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)

The database is still set to trustyworthy.

There are also no messages in sys.transmission_queue

I used profiler with all the broker events enabled and the result was as follows:

Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify

But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!

Thanks,

Andy

Hi,

I had the complete conversation - including the end of the conversation - in a stored procedure.

This was the reason i wasn't getting anything in sys.transmission_queue - i didnt end the conversation and the error appeared.

It turned out to be a permissions thing as when i'd restored the database it had set the owner to [SA]

Sorry for wasting your time if you'd bothered to read my problem!

Cheers,

Andy

Monday, February 20, 2012

message lost

Hi...

My service broker was working...all of a sudden it stopped working...so i figured out destination queue was disabled and I set it as

ALTER QUEUE SERVERQUEUE

WITH STATUS = ON, RETENTION = ON, ACTIVATION (

STATUS = ON, PROCEDURE_NAME = usp_OnReceiveMessage,

MAX_QUEUE_READERS = 5, EXECUTE AS OWNER )

Also altered my database for trustworthy after going through some posts previously.

Now the message is sent...i dont see any message in my tranmission queue but i dont receive the message in destination server.

When i checked endpoints...select * from sys.conversation_endpoints I see the last message with st_desc as conversing...

how do I solve this...?

Thanks,

Pramod

The queue must have been disabled because you must have rolled back a transaction that performed a RECEIVE on the queue 5 or more times consecutively. Read about poised message handling here.

If the message is neither in transmission queue nor in the destination queue, chances are that the dialog was errored. Look in the initiator queue as well. I don't get what you mean by "the last message" in sys.conversation_endpoints. The conversation endpoints view does not contain any messages. The st_desc column describes the state of the conversation endpoint.

|||

Rushi:

I reconfigured the service broker...this time...in transmission queue its showing this error....

Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal.
State 89.

What does this mean ?

Thanks,

|||

You are using certificates for adjacent (i.e. transport) security. The certificate used by the peer belongs to a user in master who is not mapped to a login. This could happen either because:

i) You created a user in master without login.

ii) You created a user in master with a login, but later dropped that login, thus orphaning that user.

The fix would be to create a SQL login, then create a user in master for this login and alter authorization of the peers certificate to be owned by this user. Next grant connection permission on the service broker endpoint to the newly created login.

Hope that helps.

|||

Rushi:

I want to confirm few things here...before proceeding...

Lets take a login sbuser(created to login with sql authentication). This is the login i would be using to create endpoints, certificates, service etc for server service broker.

Should I have to login as sa to create those or I am fine with sbuser ?

Next step...in master database I create a user lets say proxysbuser which will be used to connect endpoints on both sides ie.initiator and target service brokers.

Then I i use proxysbuser to alter authorization of the peers certificate.

Am I in the right track ? Let me know.

Thanks,

Pramod

|||

Configuring dialog security (i.e. between services) is independent of configuring adjacent security (i.e. between instances) and should not be confused. For configuring adjacent security the steps are as follows:

1> Instance A: Create certificate owned by the dbo of master.
2> Instance A: Create SSB endpoint using the above certificate for authentication.
3> Instance A: Export certificate to \\shared\A.crt

4> Instance B: Create certificate owned by the dbo of master.
5> Instance B: Create SSB endpoint using the above certificate for authentication.
6> Instance B: Export certificate to \\shared\B.crt

7> Instance A: Create login for instance B, let's say InstanceB.
8> Instance A: Create user in master for login InstanceB.
9> Instance A: Import certificate \\shared\B.crt into master with owner InstanceB.
10> Instance A: Grant connect permission on SSB endpoint to login InstanceB.

11> Instance B: Create login for instance A, let's say InstanceA.
12> Instance B: Create user in master for login InstanceA.
13> Instance B: Import certificate \\shared\A.crt into master with owner InstanceA.
14> Instance B: Grant connect permission on SSB endpoint to login InstanceA.

The steps for securing dialogs are different from this. Refer to the docs for that.

If you find this tedious, you can use Remus' Service Listings Manager which automates this process for you.

Rushi

|||

Rushi:

I have been trying to use Remus Service Listing I have been refering to this link...https://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx

In this step The administrator of the SQL Server instance hosting service ‘A’

In import mode the created script throws this error....

Msg 6522, Level 16, State 1, Procedure ssb_create_certificate_from_blob, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'ssb_create_certificate_from_blob': System.Data.SqlClient.SqlException: A certificate with name 'COSCOSB' already exists or this certificate already has been added to the database.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServiceBroker.Samples.CertificatesBlob.CertificatesBlob.ssb_create_certificate_from_blob(SqlString databaseName, SqlString certificateName, SqlString authorizationUser, SqlBinary certificateBody)

The script which is throwing error is IF EXISTS(SELECT * FROM sys.certificates WHERE name = 'COSCOSB')
DROP CERTIFICATE [COSCOSB];
declare @.dbname sysname;
select @.dbname = DB_NAME();
print @.dbname -- this prints master
exec msdb.dbo.ssb_create_certificate_from_blob @.dbname, 'COSCOSB','COSCOSB',0x308201ED30820156A00302010202104B7566A1DEA72E844040F300FC947B6C300D06092A864886F70D010105050030353133303106035504031E2A0043004F00530043004F005F00430045005200540049004600490043004100540045005F005300550042301E170D3036303832373030303030305A170D3037303832383030303030305A30353133303106035504031E2A0043004F00530043004F005F00430045005200540049004600490043004100540045005F00530055004230819F300D06092A864886F70D010101050003818D0030818902818100D5DDA3F09C737A7F39E9E262C6B6C62524C3C8368750DC014AEE582FCDF97E29760C0452513414520FD8DB0A8CD3A389E15D0C5C5B006228145E405ADC2344ABF0DC08BF7647BEE413525C5E29B39107424479D70F25CD32F4FB95B43974E785C81410EF506EEC59E2C6CC6135F009300F5B496FF837E50AF8EB02F806CA99870203010001300D06092A864886F70D01010505000381810038C506E25FB74C59D08C58E56D9ED505A01AE0C33E5F28E5135D9E492F5E6B33703550E14042FF87EE83E8F8C43522EAEE9EDA417E7178BFB8BF7B5B27EA05926D4C52C9CAE9B7915A890422FCE67A4ED473E29154E1205A3CAF3245B0477A469192D409A0C5EB52BC307217CF7CA3C675F7305FC11503ECA17503EB1B493A4D;
GO

I dont have any certifcate in sys.certificates....why is it throwing error ? Could you pls tell me.

Thanks,
Pramod

|||

I think this is a known bug. I'm not sure if Remus has fixed it yet. You can try using the latest version and see if that works. Presently, Remus is on vacation, but I'll ask him to follow up on this bug when he gets back.

In the mean time, look at sys.certificates for any other certificate which may have been created (possibly with a different name).

Rushi

|||

Rushi:

Thanks for quick response...but I am sure i am using the latest version Service Lsiting Manager v1.1.3 which says that bug is fixed...sys.certifcates is empty. But will keep working on it.

Thanks,

Pramod

|||Are you in the right database?|||

Yup....I am in master....

|||

Rushi...my client db is standard sql2005 and server is developer sql2005 do u think that might be causing problem ?

|||No. The error is unrelated to licensing.|||On the correct instance? Service Listing Manager is simply running the SELECT * FROM sys.certificates query and finding a certificate but you are not.|||

Rushi:

Service Listing Manager is working fine in developer edition...I tried with 2 instances of standard edition on 2 different m/c it fails at this step exec msdb.dbo.ssb_create_certificate_from_blob @.dbname .......

Message --"Configuration option 'show advanced options' changed from 0 to 1. Run the RECO

i am working on storedprocedures and after the Succesful parsing of the procedure .. on executing its giving me 3 msgs ....

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and not displaying me the output.......

The Procedure i am executing is as follows

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[uspRcopiaExport12] AS

DROP TABLE Skagway..RCOPIA_EXPORT

-- Get Patients' Home Addresses

SELECT pa.PersonID, pa.Street1, pa.Street2, pa.City, pa.State, Skagway.dbo.StripNonDigits(pa.ZipCode) AS Zip

INTO #ADDRESS

FROM Person..PHYSICAL_ADDRESS pa

INNER JOIN Person..PHYSICAL_ADDRESS_DESC pad ON (pa.PhysicalAddrDescID = pad.PhysicalAddrDescID)

WHERE pad.PhysicalAddrDesc = 'Home'

-- Get Patients' Home Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #HOME_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Home'

ORDER BY tdt.TelecomDevTypeID

-- Get Patients' Work Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #WORK_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Work'

ORDER BY tdt.TelecomDevTypeID

-- Get SCP patient demographic data

SELECT 'update_patient_4' AS DataType,

p.FirstName AS FIRST_NAME,

p.MiddleInit AS MIDDLE_INITIAL,

p.LastName AS LAST_NAME,

CASE p.Gender

WHEN 'm' THEN 'M'

WHEN 'f' THEN 'F'

WHEN 'M' THEN 'M'

WHEN 'F' THEN 'F'

ELSE 'U'

END AS SEX,

-- (SELECT TOP 1 Street1 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_1,

-- (SELECT TOP 1 Street2 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_2,

-- (SELECT TOP 1 City FROM #ADDRESS WHERE PersonID = p.PersonID) AS CITY,

-- (SELECT TOP 1 State FROM #ADDRESS WHERE PersonID = p.PersonID) AS STATE_CODE,

-- (SELECT TOP 1 Zip FROM #ADDRESS WHERE PersonID = p.PersonID) AS ZIP,

-- (SELECT TOP 1 TelecomNumber FROM #HOME_PHONE WHERE PersonID = p.PersonID) AS PHONE,

-- (SELECT TOP 1 TelecomNumber FROM #WORK_PHONE WHERE PersonID = p.PersonID) AS WORK_PHONE,

' 'AS ADDRESS_LINE_1,

' 'AS ADDRESS_LINE_2,

' 'AS CITY,

' 'AS STATE_CODE,

' 'AS ZIP,

' 'AS PHONE,

' 'AS WORK_PHONE,

' ' AS OTHER_PHONE,

-- ea.EmailAddress AS EMAIL,

' ' AS EMAIL,

p.BirthDate AS BIRTHDATE,

p.SocSecNum AS SOCIAL_SECURITY,

' ' AS INSURANCE,

' ' AS LAST_VISIT_DATE,

'PHMS-SCP-' + CAST(p.PersonID AS VARCHAR(8)) AS EXTERNAL_ID,

' ' AS PROVIDER_FIRST_NAME,

' ' AS PROVIDER_MIDDLE_NAME,

' ' AS PROVIDER_LAST_NAME

INTO Skagway..RCOPIA_EXPORT

FROM Skagway..PERSON p

INNER JOIN Skagway..EMAIL_ADDRESS ea ON (p.PersonID=ea.PersonID)

INNER JOIN Skagway..CARE_TEAM_ASSIGNMENT cta ON (p.PersonID=cta.PersonID)

INNER JOIN Skagway..SHARED_CARE_PLAN scp ON (cta.CarePlanID=scp.CarePlanID)

WHERE scp.SCPInactiveFlag <> 1

AND cta.CareTeamRoleID = 23

AND p.BirthDate IS NOT NULL

AND p.SocSecNum IS NOT NULL

AND NOT p.SocSecNum IN ('999999999', '')

-- Replace all NULLs with single spaces (required by Rcopia for import)

UPDATE Skagway..RCOPIA_EXPORT

SET MIDDLE_INITIAL = ' '

WHERE MIDDLE_INITIAL IS NULL OR MIDDLE_INITIAL = ''