Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

sql

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

Removal from sys.transmission_queues on exceeding LIFETIME

Hello,

We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:

1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.

2. Detach the database.

3. Re-attach the database.

4. Re-enable the queue

Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".

Has anyone else experienced this or can anyone suggest what we are doing wrong.

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Any suggestions gratefully recieved.

We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||

Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.

So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.

|||

Ian Mitchen wrote:

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.

For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.

|||

Thanks for a great response, that's filled another gap in my knowledge.

Cheers,

Ian

Removal from sys.transmission_queues on exceeding LIFETIME

Hello,

We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:

1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.

2. Detach the database.

3. Re-attach the database.

4. Re-enable the queue

Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".

Has anyone else experienced this or can anyone suggest what we are doing wrong.

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Any suggestions gratefully recieved.

We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||

Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.

So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.

|||

Ian Mitchen wrote:

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.

For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.

|||

Thanks for a great response, that's filled another gap in my knowledge.

Cheers,

Ian

sql

Remotely installing DSN

Is there any way to install a System DSN to all the pcs on my 2003 network?
(clients running xp)
either by a GPO or something else?]
i dont want to have to go around every pc doing itSystem DSN's are stored in the registry. making it somewhat difficult to
distribute.
You could change ther registry on one computer, save the registry to .reg
file, distrubute to all computers, and execute a custom script to import the
value.
ODBC entries are located in the registry in two spots. Both are under the
HKEY_Local_Machine\Software\ODBC
The first is found in the ODBC Data Sources subkey
The second set of registry entries go in the ODBC.INI subkey.
there are applications to help you do this as well...
http://www.freevbcode.com/ShowCode.asp?ID=3369
If it was me I would try my hardest to change the Datasource on the
application level before changing alot of computers.
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
*/
"MatthewWilsonAPD" <MatthewWilsonAPD@.discussions.microsoft.com> wrote in
message news:2DF39EDF-40A9-4FAB-9E72-7BEB554A7FAA@.microsoft.com...
> Is there any way to install a System DSN to all the pcs on my 2003
> network?
> (clients running xp)
> either by a GPO or something else?]
> i dont want to have to go around every pc doing it|||DSNs are just registry entries so you could create a
VBScript to deploy or call from login scripts...whichever.
Take a look at the following example:
http://www.databasejournal.com/feat...cle.php/2238221
-Sue
On Fri, 8 Sep 2006 08:24:02 -0700, MatthewWilsonAPD
<MatthewWilsonAPD@.discussions.microsoft.com> wrote:

>Is there any way to install a System DSN to all the pcs on my 2003 network?
>(clients running xp)
>either by a GPO or something else?]
>i dont want to have to go around every pc doing it

Remote User

Hi, im new to here and SQL server.

I am building a program in vb.net 2005 as a booking system using SQL server as backend. I have just finally managed to get SQL server working on the server, DIY Windows XP Server, but I cannot make remote logins. I have my client computers connected through a workground called JNKVPN. I can connect to all the client computers on the network through the workgroup computers menu off network places. I want to add a remote user account to SQL Server management studio express but none of the client computers are listed as domains.

Is there any way I can get them listed. I have been getting very fustrated with this and i need to have the program i am writing completed by 28th Sept 2007 so this is really delaying me substatially.

Thanks in advance for reply's

Regards
Matt

Hi,

As you said your machines are not on domain network, so you can not add the windows users to your server, rather you may add sql server login(s) in this case. Add the machine(s) to doamains so that they appears or else you need to create a windows login on your sql server machine with the same user id /password as they are on your workgroup machine. I.E. the name of your machine on workgroup is MachA and it has a user "squser1" and password "squser1", you need to create a windows user on your sql server with same username "squser1" and password "squser1" , But the best bet is to add those machine on your domain and then add/map the users to your sql server.

|||

Hi, thanks for your reply.

You have said

Hemantgiri S. Goswami wrote:

Add the machine(s) to doamains so that they appears

This is what I want to know how to do.

In theory should the workgroup computers automatically be added to the listed locations on the add login window or is there a process I must go through to get them on there. As I stated before I can see the other computers in the network places > view workgroup computers.

Regards

Matt

|||I have solved my problem. I was trying to add sql user with windows domain and account details and it was throwing an error at this. This post just illustrates my inexperience with SQL.

Thanks anyway for the reply

Remote User

Hi, im new to here and SQL server.

I am building a program in vb.net 2005 as a booking system using SQL server as backend. I have just finally managed to get SQL server working on the server, DIY Windows XP Server, but I cannot make remote logins. I have my client computers connected through a workground called JNKVPN. I can connect to all the client computers on the network through the workgroup computers menu off network places. I want to add a remote user account to SQL Server management studio express but none of the client computers are listed as domains.

Is there any way I can get them listed. I have been getting very fustrated with this and i need to have the program i am writing completed by 28th Sept 2007 so this is really delaying me substatially.

Thanks in advance for reply's

Regards
Matt

Hi,

As you said your machines are not on domain network, so you can not add the windows users to your server, rather you may add sql server login(s) in this case. Add the machine(s) to doamains so that they appears or else you need to create a windows login on your sql server machine with the same user id /password as they are on your workgroup machine. I.E. the name of your machine on workgroup is MachA and it has a user "squser1" and password "squser1", you need to create a windows user on your sql server with same username "squser1" and password "squser1" , But the best bet is to add those machine on your domain and then add/map the users to your sql server.

|||

Hi, thanks for your reply.

You have said

Hemantgiri S. Goswami wrote:

Add the machine(s) to doamains so that they appears

This is what I want to know how to do.

In theory should the workgroup computers automatically be added to the listed locations on the add login window or is there a process I must go through to get them on there. As I stated before I can see the other computers in the network places > view workgroup computers.

Regards

Matt

|||I have solved my problem. I was trying to add sql user with windows domain and account details and it was throwing an error at this. This post just illustrates my inexperience with SQL.

Thanks anyway for the reply

Remote User

Hi, im new to here and SQL server.

I am building a program in vb.net 2005 as a booking system using SQL server as backend. I have just finally managed to get SQL server working on the server, DIY Windows XP Server, but I cannot make remote logins. I have my client computers connected through a workground called JNKVPN. I can connect to all the client computers on the network through the workgroup computers menu off network places. I want to add a remote user account to SQL Server management studio express but none of the client computers are listed as domains.

Is there any way I can get them listed. I have been getting very fustrated with this and i need to have the program i am writing completed by 28th Sept 2007 so this is really delaying me substatially.

Thanks in advance for reply's

Regards
Matt

Hi,

As you said your machines are not on domain network, so you can not add the windows users to your server, rather you may add sql server login(s) in this case. Add the machine(s) to doamains so that they appears or else you need to create a windows login on your sql server machine with the same user id /password as they are on your workgroup machine. I.E. the name of your machine on workgroup is MachA and it has a user "squser1" and password "squser1", you need to create a windows user on your sql server with same username "squser1" and password "squser1" , But the best bet is to add those machine on your domain and then add/map the users to your sql server.

|||

Hi, thanks for your reply.

You have said

Hemantgiri S. Goswami wrote:

Add the machine(s) to doamains so that they appears

This is what I want to know how to do.

In theory should the workgroup computers automatically be added to the listed locations on the add login window or is there a process I must go through to get them on there. As I stated before I can see the other computers in the network places > view workgroup computers.

Regards

Matt

|||I have solved my problem. I was trying to add sql user with windows domain and account details and it was throwing an error at this. This post just illustrates my inexperience with SQL.

Thanks anyway for the reply

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Red
make sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> have
> calls
>
|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:

> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
sql

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Redmake sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > I want to be able to have a stored procedure on server A to call a
> > stored procedure on Server B. I have my stored procedures in place. I
> have
> > used Linked servers on server A to define Server B so, that server A can
> > talk to server B. However I seem to have missed something. This is the
> > message that I get:
> > Could not find stored procedure 'sp_tt_load'.(42000,2812)
> > Procedure(sp_tt_dumpandload_for_standby).
> >
> >
> > sp_tt_dumpandload_for_standby (server A) does a database dump and then
> calls
> > sp_tt_load ( server B). Server B will load the dump onto it's standby
> > database.
> >
> > TIA for all the help.
> >
> > Red
> >
> >
>|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>

Remote Server login

I got a problem a system administrator can not log on to sql remote server so he can back up the system tables. Hes doing it through active directory, not sure what the problem is. can anyone help please. reason says not associated with a trusted sql connection?Is Builtin Administrators group registered with the server with sysadmin server role? And if not, is the server configured for Mixed Security Mode? How is your admin trying to connect?|||hes logging in through windows only, not in mix mode|||Then you need to look into what account he uses and if that account is explicitly present in the Logins of the server or if its group is present there. I'd make sure that Builtins is in the logins and tell him that he should fix it himself, - include Domain Admins or his account into Local Administrators group (Builtin Administrators).|||Actually, if the BUILTIN/Administrators group is not part of SQL Server, that's a good thing. The DBA group should be the ONLY group with sysadmin rights on SQL Servers. The Domain Admins should be allowed backup permissions if that's part of their job description. Normally, the DBA does that also though.|||Well, not if you're doing replication.|||Well, I doubt they're doing replication if they can't even figure out how to log into a box. Codd, what were you thinking?|||Hey, you know we're talking about Brett here (wink-wink), Codd knows what he does there...between margaritas... :D...I'm loosing my mind...OF COURSE IT'S a WRONG THREAD!!! Oh well, eventually Brett will show up and say what I thought he already did...How many of those shots did I have?

Remote Process of Report

Hi,

We have a reports running in a system in our internal network. Now our requirement is we have to publish the same report into web for the public. How can I do that..?

Note :

I can not install reporting service in my Web server system.

There should be a way to put the report from the reporting server to the web server and display in the webpage to the public.

This is really an urgent requirement.

Are you using RS2005? If so, you can embed the report viewer control into your ASP.Net application.|||

I'm using 2003, Is there any way to do this. My problem is I have to show a report to a website which is running in diffrent system in the same network.

the user will be provided by a hyperlink in the asp.net webpage by clicking the link the user should see the report.

Thanks

Mohan

|||

It is tricky in 2003 without the viewer control.

Your user's won't be authenticated against your RS backend, so you will have to get the contents of the report from within your ASP.Net application. You will want to use the HTMLFragment deviceinfo setting. The trickier part is resolving the secondary streams (like images and charts). You will have to use the StreamRoot deviceinfo to redirect image requests back to your ASP.Net application, so you can make them on behalf of your users.

remote instances

config - 2 machines in a workgroup, service running under local system account. Tried the following config. from Rushi. Running profiles on both machines. on source - starts the conversation, and a send. then get an event subclass 2-remote at a periodic interval (in a loop), no activty on the other end. any suggestions
In master:
1. Create master key for database 'master'.
2. Create certificate for user 'dbo' in database 'master'.
3. Create service broker endpoint using certificate created above.
4. Grant connect on above endpoint to the login 'Public'.

In your database:
1. Create master key.
2. Grant send on target service to user 'public'.
3. Create routes to initiator services on other servers.
4. Create routes to target services on other servers.

Now begin dialog with encryption = off and send.
It could be an incorrect address used in the route (a typo in the DNS name), a port number mismatch between the route and the endpoint configuration, a port being blocked by personal firewall, IPSEC blocking a TCP connection etc. What is the value of transmission_status column in the sys.transmission_queue view in the initiatr database?

Have you tried using the service listing stored procs from Rushi's site? They make this kind of tasks much easier.

Regards,
~ Remus|||The service listings stored procs are now available on our CodeGallery:

http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319
Later,

Rushi

Remote Distributor and Subscriber on same machine - is that OK?

Hi,

Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).

Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.

Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?

Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,

Smitha

This is actually a rather popular configuration (at least the ones that I have seen) inside Microsoft as this allows you to upgrade the Distributor and Subscriber to a new release together and then use the Subscriber as a test bed for the new release while receiving continuous updates from the production system. You do need good network connectivity between the publisher and the distributor\subscriber as pulling snapshot data (or backup\restore for that matter) can easily saturate a 100Mbit Ethernet connection. So to minimize impact on the production system, you may want to get a dedicated connection between the two.

-Raymond

|||

Thanks Raymond. I shouldn't have to worry about the snapshot/backup+restore because I've used this second server as the backup files' location in the past (and still perform copy-only FULL production DB backups to this server). The restore takes a while since it reads from and writes to the same drive but this is OK for an operation that may happen 2-3 times a year.

Thanks again,

Smitha

Remote connection problem

I have SQL Server 2005. My system is in a local network. In the network others are able to access my database. I have the permission to access the database of others but still I am not able to access the others database.

Can any body tell me that what can be the reason?

Best Regards,

Kanhaiya Lal

Which error do you get during connecting?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Please provide more detail info here, such as what error message you came across? Did you see any clue in Server ERRORLOG? Is it connection failure or data operation failure? or others? What is you configuration? such as client connection string, server sku? Remote/local? client and server OS?

Thanks!

Ming

|||What authentication mode was the server installed with ?|||

The error was timeout expired and I have solve it.

Thanks

Kanhaiya

remote connection problem

Hi all,
I'm trying to connect to a MSDE database on a remote system.
I have no problems connecting locally.
I have changed the security to mixed mode, and i have made a NAT entry
that forwards port 1433 to the internal server with the MSDE database
on it.
(just for the record, the server is running five different MSDE
instances)
I wanted to verify that the instance i need access to is at port 1433,
so i tried opening SVRNETCN.exe but it just gives me the following
error message "The specified module could not be found" (The title of
the alert window is "126")
So i went to the registration database trying to confirm it there -
however the only related key i could find was:
-------
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client
\SuperSocketNetLib]
"ProtocolOrder"=hex(7):74,00,63,00,70,00,00,00,6e, 00,70,00,00,00,00,00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client
\SuperSocketNetLib\LastConnect]
"winfinans.mondo.dk"="-134086648:tcp:winfinans.mondo.dk,1433"
"winfinans.pitweb.dk"="-150536184:tcp:winfinans.pitweb.dk,1433"
"MYSERVER\\SHAREPOINT"="-150536184:lpc:MYSERVER\\SHAREPOINT"
"MYSERVER\\PADMINISTRATOR"="-134086648:lpc:MYSERVER\\PADMINISTRATOR"
"MYSERVER\\SBSMONITORING"="-33423352:lpc:MYSERVER\\SBSMONITORING"
"MYSERVER\\WINFINANS"="-150536184:lpc:MYSERVER\\WINFINANS"
"MYSERVER\\INSIGTH"="-150536184:lpc:MYSERVER\\INSIGTH"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client
\SuperSocketNetLib\Tcp]
"DefaultPort"=dword:00000599
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client
\SuperSocketNetLib\VIA]
"RecognizedVendors"="Giganet,QLogic"
-------
It is the instance "winfinans" i need access to.
I tried searching the web for an alternative method to make sure that
it is the right port - without any luck.
Thanks in advance for you're help.
Have a nice day.
Here is the output from netstat -a maybe somebody can see from that
what port i'm supposed to use.
Active Connections
Proto Local Address Foreign Address State
TCP myserver:smtp myserver.domain.dk:0 LISTENING
TCP myserver:nameserver myserver.domain.dk:0 LISTENING
TCP myserver:domain myserver.domain.dk:0 LISTENING
TCP myserver:http myserver.domain.dk:0 LISTENING
TCP myserver:kerberos myserver.domain.dk:0 LISTENING
TCP myserver:epmap myserver.domain.dk:0 LISTENING
TCP myserver:imap myserver.domain.dk:0 LISTENING
TCP myserver:ldap myserver.domain.dk:0 LISTENING
TCP myserver:https myserver.domain.dk:0 LISTENING
TCP myserver:444 myserver.domain.dk:0 LISTENING
TCP myserver:microsoft-ds myserver.domain.dk:0 LISTENING
TCP myserver:kpasswd myserver.domain.dk:0 LISTENING
TCP myserver:593 myserver.domain.dk:0 LISTENING
TCP myserver:ldaps myserver.domain.dk:0 LISTENING
TCP myserver:691 myserver.domain.dk:0 LISTENING
TCP myserver:993 myserver.domain.dk:0 LISTENING
TCP myserver:1025 myserver.domain.dk:0 LISTENING
TCP myserver:1027 myserver.domain.dk:0 LISTENING
TCP myserver:1069 myserver.domain.dk:0 LISTENING
TCP myserver:1094 myserver.domain.dk:0 LISTENING
TCP myserver:1103 myserver.domain.dk:0 LISTENING
TCP myserver:1104 myserver.domain.dk:0 LISTENING
TCP myserver:1105 myserver.domain.dk:0 LISTENING
TCP myserver:1106 myserver.domain.dk:0 LISTENING
TCP myserver:1116 myserver.domain.dk:0 LISTENING
TCP myserver:1148 myserver.domain.dk:0 LISTENING
TCP myserver:1150 myserver.domain.dk:0 LISTENING
TCP myserver:1184 myserver.domain.dk:0 LISTENING
TCP myserver:1192 myserver.domain.dk:0 LISTENING
TCP myserver:1370 myserver.domain.dk:0 LISTENING
TCP myserver:ms-sql-s myserver.domain.dk:0 LISTENING
TCP myserver:3268 myserver.domain.dk:0 LISTENING
TCP myserver:3269 myserver.domain.dk:0 LISTENING
TCP myserver:3389 myserver.domain.dk:0 LISTENING
TCP myserver:4485 myserver.domain.dk:0 LISTENING
TCP myserver:4486 myserver.domain.dk:0 LISTENING
TCP myserver:4487 myserver.domain.dk:0 LISTENING
TCP myserver:4791 myserver.domain.dk:0 LISTENING
TCP myserver:5225 myserver.domain.dk:0 LISTENING
TCP myserver:5226 myserver.domain.dk:0 LISTENING
TCP myserver:5989 myserver.domain.dk:0 LISTENING
TCP myserver:6001 myserver.domain.dk:0 LISTENING
TCP myserver:6002 myserver.domain.dk:0 LISTENING
TCP myserver:6004 myserver.domain.dk:0 LISTENING
TCP myserver:7560 myserver.domain.dk:0 LISTENING
TCP myserver:8008 myserver.domain.dk:0 LISTENING
TCP myserver:8081 myserver.domain.dk:0 LISTENING
TCP myserver:19226 myserver.domain.dk:0 LISTENING
TCP myserver:23164 myserver.domain.dk:0 LISTENING
TCP myserver:23165 myserver.domain.dk:0 LISTENING
TCP myserver:domain myserver.domain.dk:0 LISTENING
TCP myserver:ldap myserver.domain.dk:5918 TIME_WAIT
TCP myserver:ldap myserver.domain.dk:57373 ESTABLISHED
TCP myserver:1133 myserver.domain.dk:0 LISTENING
TCP myserver:1133 myserver.domain.dk:1134 ESTABLISHED
TCP myserver:1134 myserver.domain.dk:1133 ESTABLISHED
TCP myserver:1206 myserver.domain.dk:0 LISTENING
TCP myserver:2301 myserver.domain.dk:0 LISTENING
TCP myserver:2381 myserver.domain.dk:0 LISTENING
TCP myserver:4663 myserver.domain.dk:5225 CLOSE_WAIT
TCP myserver:4669 myserver.domain.dk:5225 CLOSE_WAIT
TCP myserver:5226 myserver.domain.dk:42330 ESTABLISHED
TCP myserver:8005 myserver.domain.dk:0 LISTENING
TCP myserver:42330 myserver.domain.dk:5226 ESTABLISHED
TCP myserver:57373 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:epmap myserver.domain.dk:5927 ESTABLISHED
TCP myserver:netbios-ssn myserver.domain.dk:0 LISTENING
TCP myserver:ldap myserver.domain.dk:1115 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:5895 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:5911 TIME_WAIT
TCP myserver:ldap myserver.domain.dk:5923 TIME_WAIT
TCP myserver:ldap myserver.domain.dk:57369 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57370 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57371 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57380 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57381 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57382 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57383 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57384 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57386 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57387 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57390 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57391 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57392 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57393 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57394 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57395 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57396 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57412 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57422 ESTABLISHED
TCP myserver:ldap myserver.domain.dk:57430 ESTABLISHED
TCP myserver:691 myserver.domain.dk:1131 ESTABLISHED
TCP myserver:691 myserver.domain.dk:1190 ESTABLISHED
TCP myserver:691 myserver.domain.dk:1197 ESTABLISHED
TCP myserver:1025 myserver.domain.dk:1098 ESTABLISHED
TCP myserver:1025 myserver.domain.dk:1222 ESTABLISHED
TCP myserver:1025 myserver.domain.dk:1245 ESTABLISHED
TCP myserver:1025 myserver.domain.dk:1503 ESTABLISHED
TCP myserver:1025 myserver.domain.dk:2158 ESTABLISHED
TCP myserver:1025 myserver.domain.dk:51771 ESTABLISHED
TCP myserver:1081 myserver.domain.dk:4791 ESTABLISHED
TCP myserver:1098 myserver.domain.dk:1025 ESTABLISHED
TCP myserver:1115 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:1131 myserver.domain.dk:691 ESTABLISHED
TCP myserver:1142 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:1148 myserver.domain.dk:5928 ESTABLISHED
TCP myserver:1173 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:1174 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:1176 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:1177 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:1178 myserver.domain.dk:3268 CLOSE_WAIT
TCP myserver:1179 myserver.domain.dk:3268 CLOSE_WAIT
TCP myserver:1190 myserver.domain.dk:691 ESTABLISHED
TCP myserver:1197 myserver.domain.dk:691 ESTABLISHED
TCP myserver:1222 myserver.domain.dk:1025 ESTABLISHED
TCP myserver:1245 myserver.domain.dk:1025 ESTABLISHED
TCP myserver:1503 myserver.domain.dk:1025 ESTABLISHED
TCP myserver:2158 myserver.domain.dk:1025 ESTABLISHED
TCP myserver:2301 myserver.domain.dk:0 LISTENING
TCP myserver:2381 myserver.domain.dk:0 LISTENING
TCP myserver:3268 myserver.domain.dk:4693 ESTABLISHED
TCP myserver:3268 myserver.domain.dk:57376 ESTABLISHED
TCP myserver:3268 myserver.domain.dk:57378 ESTABLISHED
TCP myserver:3268 myserver.domain.dk:57379 ESTABLISHED
TCP myserver:3268 myserver.domain.dk:57388 ESTABLISHED
TCP myserver:3522 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:3602 myserver.domain.dk:3268 CLOSE_WAIT
TCP myserver:3603 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:3633 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:4693 myserver.domain.dk:3268 ESTABLISHED
TCP myserver:4791 myserver.domain.dk:1081 ESTABLISHED
TCP myserver:4972 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:5286 myserver.domain.dk:3268 CLOSE_WAIT
TCP myserver:5511 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:5895 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:5907 myserver.domain.dk:epmap TIME_WAIT
TCP myserver:5908 myserver.domain.dk:epmap TIME_WAIT
TCP myserver:5909 myserver.domain.dk:epmap TIME_WAIT
TCP myserver:5910 myserver.domain.dk:1025 TIME_WAIT
TCP myserver:5911 myserver.domain.dk:ldap TIME_WAIT
TCP myserver:5912 myserver.domain.dk:ldap TIME_WAIT
TCP myserver:5919 myserver.domain.dk:ldap TIME_WAIT
TCP myserver:5920 myserver.domain.dk:3268 TIME_WAIT
TCP myserver:5921 myserver.domain.dk:domain TIME_WAIT
TCP myserver:5922 myserver.domain.dk:domain TIME_WAIT
TCP myserver:5927 myserver.domain.dk:epmap ESTABLISHED
TCP myserver:5928 myserver.domain.dk:1148 ESTABLISHED
TCP myserver:25872 myserver.domain.dk:ldap CLOSE_WAIT
TCP myserver:49366 myserver.domain.dk:3268 CLOSE_WAIT
TCP myserver:51771 myserver.domain.dk:1025 ESTABLISHED
TCP myserver:57369 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57370 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57371 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57376 myserver.domain.dk:3268 ESTABLISHED
TCP myserver:57378 myserver.domain.dk:3268 ESTABLISHED
TCP myserver:57379 myserver.domain.dk:3268 ESTABLISHED
TCP myserver:57380 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57381 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57382 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57383 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57384 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57386 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57387 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57388 myserver.domain.dk:3268 ESTABLISHED
TCP myserver:57390 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57391 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57392 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57393 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57394 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57395 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57396 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57412 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57422 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:57430 myserver.domain.dk:ldap ESTABLISHED
TCP myserver:59543 myserver.domain.dk:ldap CLOSE_WAIT
UDP myserver:nameserver *:*
UDP myserver:epmap *:*
UDP myserver:snmp *:*
UDP myserver:microsoft-ds *:*
UDP myserver:isakmp *:*
UDP myserver:1038 *:*
UDP myserver:1066 *:*
UDP myserver:1117 *:*
UDP myserver:1135 *:*
UDP myserver:1136 *:*
UDP myserver:1151 *:*
UDP myserver:1193 *:*
UDP myserver:1246 *:*
UDP myserver:ms-sql-m *:*
UDP myserver:3456 *:*
UDP myserver:3457 *:*
UDP myserver:4500 *:*
UDP myserver:4670 *:*
UDP myserver:16826 *:*
UDP myserver:16828 *:*
UDP myserver:16830 *:*
UDP myserver:16994 *:*
UDP myserver:16995 *:*
UDP myserver:16997 *:*
UDP myserver:19226 *:*
UDP myserver:27140 *:*
UDP myserver:42308 *:*
UDP myserver:domain *:*
UDP myserver:1065 *:*
UDP myserver:1067 *:*
UDP myserver:1095 *:*
UDP myserver:1118 *:*
UDP myserver:1141 *:*
UDP myserver:1147 *:*
UDP myserver:1152 *:*
UDP myserver:1185 *:*
UDP myserver:1194 *:*
UDP myserver:1201 *:*
UDP myserver:1207 *:*
UDP myserver:1216 *:*
UDP myserver:1427 *:*
UDP myserver:1599 *:*
UDP myserver:3456 *:*
UDP myserver:3457 *:*
UDP myserver:5904 *:*
UDP myserver:5982 *:*
UDP myserver:7272 *:*
UDP myserver:11974 *:*
UDP myserver:24647 *:*
UDP myserver:42247 *:*
UDP myserver:42439 *:*
UDP myserver:domain *:*
UDP myserver:bootps *:*
UDP myserver:bootpc *:*
UDP myserver:kerberos *:*
UDP myserver:netbios-ns *:*
UDP myserver:netbios-dgm *:*
UDP myserver:389 *:*
UDP myserver:427 *:*
UDP myserver:kpasswd *:*
UDP myserver:1328 *:*
UDP myserver:2535 *:*
|||Ah, by default, SQL Server 2005 named instances use dynamic port
assignments. That is, when the instance is started (each time it's started)
it asks the OS for an open port. The SQL Browser service monitors these
assignments and reports this port assignment back to any application
attempting to connect. Hard-coding a port (therefore) is problematic at
best. If you must get a static port, you have to use SQL Server
Configuration Manager to change the port config.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Tokatrash" <martin@.deamon.dk> wrote in message
news:1183020630.563134.253610@.q75g2000hsh.googlegr oups.com...
> Here is the output from netstat -a maybe somebody can see from that
> what port i'm supposed to use.
> Active Connections
> Proto Local Address Foreign Address State
> TCP myserver:smtp myserver.domain.dk:0 LISTENING
> TCP myserver:nameserver myserver.domain.dk:0 LISTENING
> TCP myserver:domain myserver.domain.dk:0 LISTENING
> TCP myserver:http myserver.domain.dk:0 LISTENING
> TCP myserver:kerberos myserver.domain.dk:0 LISTENING
> TCP myserver:epmap myserver.domain.dk:0 LISTENING
> TCP myserver:imap myserver.domain.dk:0 LISTENING
> TCP myserver:ldap myserver.domain.dk:0 LISTENING
> TCP myserver:https myserver.domain.dk:0 LISTENING
> TCP myserver:444 myserver.domain.dk:0 LISTENING
> TCP myserver:microsoft-ds myserver.domain.dk:0 LISTENING
> TCP myserver:kpasswd myserver.domain.dk:0 LISTENING
> TCP myserver:593 myserver.domain.dk:0 LISTENING
> TCP myserver:ldaps myserver.domain.dk:0 LISTENING
> TCP myserver:691 myserver.domain.dk:0 LISTENING
> TCP myserver:993 myserver.domain.dk:0 LISTENING
> TCP myserver:1025 myserver.domain.dk:0 LISTENING
> TCP myserver:1027 myserver.domain.dk:0 LISTENING
> TCP myserver:1069 myserver.domain.dk:0 LISTENING
> TCP myserver:1094 myserver.domain.dk:0 LISTENING
> TCP myserver:1103 myserver.domain.dk:0 LISTENING
> TCP myserver:1104 myserver.domain.dk:0 LISTENING
> TCP myserver:1105 myserver.domain.dk:0 LISTENING
> TCP myserver:1106 myserver.domain.dk:0 LISTENING
> TCP myserver:1116 myserver.domain.dk:0 LISTENING
> TCP myserver:1148 myserver.domain.dk:0 LISTENING
> TCP myserver:1150 myserver.domain.dk:0 LISTENING
> TCP myserver:1184 myserver.domain.dk:0 LISTENING
> TCP myserver:1192 myserver.domain.dk:0 LISTENING
> TCP myserver:1370 myserver.domain.dk:0 LISTENING
> TCP myserver:ms-sql-s myserver.domain.dk:0 LISTENING
> TCP myserver:3268 myserver.domain.dk:0 LISTENING
> TCP myserver:3269 myserver.domain.dk:0 LISTENING
> TCP myserver:3389 myserver.domain.dk:0 LISTENING
> TCP myserver:4485 myserver.domain.dk:0 LISTENING
> TCP myserver:4486 myserver.domain.dk:0 LISTENING
> TCP myserver:4487 myserver.domain.dk:0 LISTENING
> TCP myserver:4791 myserver.domain.dk:0 LISTENING
> TCP myserver:5225 myserver.domain.dk:0 LISTENING
> TCP myserver:5226 myserver.domain.dk:0 LISTENING
> TCP myserver:5989 myserver.domain.dk:0 LISTENING
> TCP myserver:6001 myserver.domain.dk:0 LISTENING
> TCP myserver:6002 myserver.domain.dk:0 LISTENING
> TCP myserver:6004 myserver.domain.dk:0 LISTENING
> TCP myserver:7560 myserver.domain.dk:0 LISTENING
> TCP myserver:8008 myserver.domain.dk:0 LISTENING
> TCP myserver:8081 myserver.domain.dk:0 LISTENING
> TCP myserver:19226 myserver.domain.dk:0 LISTENING
> TCP myserver:23164 myserver.domain.dk:0 LISTENING
> TCP myserver:23165 myserver.domain.dk:0 LISTENING
> TCP myserver:domain myserver.domain.dk:0 LISTENING
> TCP myserver:ldap myserver.domain.dk:5918 TIME_WAIT
> TCP myserver:ldap myserver.domain.dk:57373 ESTABLISHED
> TCP myserver:1133 myserver.domain.dk:0 LISTENING
> TCP myserver:1133 myserver.domain.dk:1134 ESTABLISHED
> TCP myserver:1134 myserver.domain.dk:1133 ESTABLISHED
> TCP myserver:1206 myserver.domain.dk:0 LISTENING
> TCP myserver:2301 myserver.domain.dk:0 LISTENING
> TCP myserver:2381 myserver.domain.dk:0 LISTENING
> TCP myserver:4663 myserver.domain.dk:5225 CLOSE_WAIT
> TCP myserver:4669 myserver.domain.dk:5225 CLOSE_WAIT
> TCP myserver:5226 myserver.domain.dk:42330 ESTABLISHED
> TCP myserver:8005 myserver.domain.dk:0 LISTENING
> TCP myserver:42330 myserver.domain.dk:5226 ESTABLISHED
> TCP myserver:57373 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:epmap myserver.domain.dk:5927 ESTABLISHED
> TCP myserver:netbios-ssn myserver.domain.dk:0 LISTENING
> TCP myserver:ldap myserver.domain.dk:1115 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:5895 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:5911 TIME_WAIT
> TCP myserver:ldap myserver.domain.dk:5923 TIME_WAIT
> TCP myserver:ldap myserver.domain.dk:57369 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57370 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57371 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57380 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57381 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57382 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57383 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57384 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57386 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57387 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57390 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57391 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57392 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57393 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57394 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57395 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57396 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57412 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57422 ESTABLISHED
> TCP myserver:ldap myserver.domain.dk:57430 ESTABLISHED
> TCP myserver:691 myserver.domain.dk:1131 ESTABLISHED
> TCP myserver:691 myserver.domain.dk:1190 ESTABLISHED
> TCP myserver:691 myserver.domain.dk:1197 ESTABLISHED
> TCP myserver:1025 myserver.domain.dk:1098 ESTABLISHED
> TCP myserver:1025 myserver.domain.dk:1222 ESTABLISHED
> TCP myserver:1025 myserver.domain.dk:1245 ESTABLISHED
> TCP myserver:1025 myserver.domain.dk:1503 ESTABLISHED
> TCP myserver:1025 myserver.domain.dk:2158 ESTABLISHED
> TCP myserver:1025 myserver.domain.dk:51771 ESTABLISHED
> TCP myserver:1081 myserver.domain.dk:4791 ESTABLISHED
> TCP myserver:1098 myserver.domain.dk:1025 ESTABLISHED
> TCP myserver:1115 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:1131 myserver.domain.dk:691 ESTABLISHED
> TCP myserver:1142 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:1148 myserver.domain.dk:5928 ESTABLISHED
> TCP myserver:1173 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:1174 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:1176 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:1177 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:1178 myserver.domain.dk:3268 CLOSE_WAIT
> TCP myserver:1179 myserver.domain.dk:3268 CLOSE_WAIT
> TCP myserver:1190 myserver.domain.dk:691 ESTABLISHED
> TCP myserver:1197 myserver.domain.dk:691 ESTABLISHED
> TCP myserver:1222 myserver.domain.dk:1025 ESTABLISHED
> TCP myserver:1245 myserver.domain.dk:1025 ESTABLISHED
> TCP myserver:1503 myserver.domain.dk:1025 ESTABLISHED
> TCP myserver:2158 myserver.domain.dk:1025 ESTABLISHED
> TCP myserver:2301 myserver.domain.dk:0 LISTENING
> TCP myserver:2381 myserver.domain.dk:0 LISTENING
> TCP myserver:3268 myserver.domain.dk:4693 ESTABLISHED
> TCP myserver:3268 myserver.domain.dk:57376 ESTABLISHED
> TCP myserver:3268 myserver.domain.dk:57378 ESTABLISHED
> TCP myserver:3268 myserver.domain.dk:57379 ESTABLISHED
> TCP myserver:3268 myserver.domain.dk:57388 ESTABLISHED
> TCP myserver:3522 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:3602 myserver.domain.dk:3268 CLOSE_WAIT
> TCP myserver:3603 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:3633 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:4693 myserver.domain.dk:3268 ESTABLISHED
> TCP myserver:4791 myserver.domain.dk:1081 ESTABLISHED
> TCP myserver:4972 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:5286 myserver.domain.dk:3268 CLOSE_WAIT
> TCP myserver:5511 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:5895 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:5907 myserver.domain.dk:epmap TIME_WAIT
> TCP myserver:5908 myserver.domain.dk:epmap TIME_WAIT
> TCP myserver:5909 myserver.domain.dk:epmap TIME_WAIT
> TCP myserver:5910 myserver.domain.dk:1025 TIME_WAIT
> TCP myserver:5911 myserver.domain.dk:ldap TIME_WAIT
> TCP myserver:5912 myserver.domain.dk:ldap TIME_WAIT
> TCP myserver:5919 myserver.domain.dk:ldap TIME_WAIT
> TCP myserver:5920 myserver.domain.dk:3268 TIME_WAIT
> TCP myserver:5921 myserver.domain.dk:domain TIME_WAIT
> TCP myserver:5922 myserver.domain.dk:domain TIME_WAIT
> TCP myserver:5927 myserver.domain.dk:epmap ESTABLISHED
> TCP myserver:5928 myserver.domain.dk:1148 ESTABLISHED
> TCP myserver:25872 myserver.domain.dk:ldap CLOSE_WAIT
> TCP myserver:49366 myserver.domain.dk:3268 CLOSE_WAIT
> TCP myserver:51771 myserver.domain.dk:1025 ESTABLISHED
> TCP myserver:57369 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57370 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57371 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57376 myserver.domain.dk:3268 ESTABLISHED
> TCP myserver:57378 myserver.domain.dk:3268 ESTABLISHED
> TCP myserver:57379 myserver.domain.dk:3268 ESTABLISHED
> TCP myserver:57380 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57381 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57382 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57383 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57384 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57386 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57387 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57388 myserver.domain.dk:3268 ESTABLISHED
> TCP myserver:57390 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57391 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57392 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57393 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57394 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57395 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57396 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57412 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57422 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:57430 myserver.domain.dk:ldap ESTABLISHED
> TCP myserver:59543 myserver.domain.dk:ldap CLOSE_WAIT
> UDP myserver:nameserver *:*
> UDP myserver:epmap *:*
> UDP myserver:snmp *:*
> UDP myserver:microsoft-ds *:*
> UDP myserver:isakmp *:*
> UDP myserver:1038 *:*
> UDP myserver:1066 *:*
> UDP myserver:1117 *:*
> UDP myserver:1135 *:*
> UDP myserver:1136 *:*
> UDP myserver:1151 *:*
> UDP myserver:1193 *:*
> UDP myserver:1246 *:*
> UDP myserver:ms-sql-m *:*
> UDP myserver:3456 *:*
> UDP myserver:3457 *:*
> UDP myserver:4500 *:*
> UDP myserver:4670 *:*
> UDP myserver:16826 *:*
> UDP myserver:16828 *:*
> UDP myserver:16830 *:*
> UDP myserver:16994 *:*
> UDP myserver:16995 *:*
> UDP myserver:16997 *:*
> UDP myserver:19226 *:*
> UDP myserver:27140 *:*
> UDP myserver:42308 *:*
> UDP myserver:domain *:*
> UDP myserver:1065 *:*
> UDP myserver:1067 *:*
> UDP myserver:1095 *:*
> UDP myserver:1118 *:*
> UDP myserver:1141 *:*
> UDP myserver:1147 *:*
> UDP myserver:1152 *:*
> UDP myserver:1185 *:*
> UDP myserver:1194 *:*
> UDP myserver:1201 *:*
> UDP myserver:1207 *:*
> UDP myserver:1216 *:*
> UDP myserver:1427 *:*
> UDP myserver:1599 *:*
> UDP myserver:3456 *:*
> UDP myserver:3457 *:*
> UDP myserver:5904 *:*
> UDP myserver:5982 *:*
> UDP myserver:7272 *:*
> UDP myserver:11974 *:*
> UDP myserver:24647 *:*
> UDP myserver:42247 *:*
> UDP myserver:42439 *:*
> UDP myserver:domain *:*
> UDP myserver:bootps *:*
> UDP myserver:bootpc *:*
> UDP myserver:kerberos *:*
> UDP myserver:netbios-ns *:*
> UDP myserver:netbios-dgm *:*
> UDP myserver:389 *:*
> UDP myserver:427 *:*
> UDP myserver:kpasswd *:*
> UDP myserver:1328 *:*
> UDP myserver:2535 *:*
>
|||On 28 Jun., 17:52, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> Ah, by default, SQL Server 2005 named instances use dynamic port
> assignments. That is, when the instance is started (each time it's started)
> it asks the OS for an open port. The SQL Browser service monitors these
> assignments and reports this port assignment back to any application
> attempting to connect. Hard-coding a port (therefore) is problematic at
> best. If you must get a static port, you have to use SQL Server
> Configuration Manager to change the port config.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Tokatrash" <mar...@.deamon.dk> wrote in message
> news:1183020630.563134.253610@.q75g2000hsh.googlegr oups.com...
>
>
> ...
> ls mere
I need to get a remote connection to the server trough a firewall.
Do you know a way to make that possible besides using a static port
and NAT?
Thanks for youre help.
|||The problem is, since the server can (might) reassign the port number when
the service is started, it's impossible to open a specific port in the
firewall. Microsoft's (and my) recommendation is to use static IP
assignments.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Tokatrash" <martin@.deamon.dk> wrote in message
news:1183477208.003917.49190@.q75g2000hsh.googlegro ups.com...
On 28 Jun., 17:52, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> Ah, by default, SQL Server 2005 named instances use dynamic port
> assignments. That is, when the instance is started (each time it's
> started)
> it asks the OS for an open port. The SQL Browser service monitors these
> assignments and reports this port assignment back to any application
> attempting to connect. Hard-coding a port (therefore) is problematic at
> best. If you must get a static port, you have to use SQL Server
> Configuration Manager to change the port config.
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Tokatrash" <mar...@.deamon.dk> wrote in message
> news:1183020630.563134.253610@.q75g2000hsh.googlegr oups.com...
>
>
> ...
> ls mere
I need to get a remote connection to the server trough a firewall.
Do you know a way to make that possible besides using a static port
and NAT?
Thanks for youre help.
|||On 4 Jul., 22:43, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> The problem is, since the server can (might) reassign the port number when
> the service is started, it's impossible to open a specific port in the
> firewall. Microsoft's (and my) recommendation is to use static IP
> assignments.
That's sadly just not a possebility as their security policy doesn't
allow every port to be open.
Gues i gotta find a VPN solution then.
Thanks a thousand for all you're help!!!