Hoe To Connect two different instances of SQL SERVER

How to connect two differect instances of SQL SERVER on LEN or WAN for Data Migration.Refer to your thread http://www.dbforums.com/t995930.html

HistoryID Problem?

Hi I'm trying to use SOAP in a VAB Access 2003 Project. I can connect to the Reporting Services an Create fr example Folders. But if I want to Render a Report i am Getting a Problem.
Error: -2147221504
For ' snapshotID ' indicated parameter value does not correspond to the type of parameter.
I passed an empty String for the optional Value HistoryID.
I dont know where the problem is.
Can anybody help me ?
CODE:
Dim ReportName As String
Dim RenderFormat As String
Dim HistoryID As Variant
Dim DeviceInfo As String
Dim Parameters(2) As struct_ParameterValue
Dim Credentials() As struct_DataSourceCredential
Dim ShowHideToggle As String
Dim resultFile() As Byte
Dim resultEncoding As String
Dim resultMimeType As String
Dim resultParametersUsed() As struct_ParameterValue
Dim resultWarnings() As struct_Warning
Dim resultStreamIds() As String
Set RS = New clsws_ReportingService
ReportName = "/SampleReports/Employee Sales Summary"
RenderFormat = "mhmtl"
HistoryID = 0
DeviceInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
ShowHideToggle = ""
Set Parameters(0) = New struct_ParameterValue
Parameters(0).Name = "EmpID"
Parameters(0).Value = "38"
Set Parameters(1) = New struct_ParameterValue
Parameters(1).Name = "ReportMonth"
Parameters(1).Value = "6" ' June
Set Parameters(2) = New struct_ParameterValue
Parameters(2).Name = "ReportYear"
Parameters(2).Value = "2004"
resultFile = RS.wsm_Render( _
ReportName, _
RenderFormat, _
nil, _
DeviceInfo, _
Parameters, _
Credentials, _
ShowHideToggle, _
resultEncoding, _
resultMimeType, _
resultParametersUsed, _
resultWarnings, _
resultStreamIds)
thx for your helpSorry for double posting... but there was an error maeesage so i tryed again.sql

highly appreciated.

I am trying to connect the named instance from my client computer... I can
easily connect the default instance of my remote machine but when I tried to
connect through named instance it gave me an error that SQL Server doesn't
exist or access denied...
In the client network utility I have defined the IP and alias of that server
but failed to connect.
Can any one give their expert idea.
Thanks
Roy wrote:
> I am trying to connect the named instance from my client computer...
> I can easily connect the default instance of my remote machine but
> when I tried to connect through named instance it gave me an error
> that SQL Server doesn't exist or access denied...
> In the client network utility I have defined the IP and alias of that
> server but failed to connect.
> Can any one give their expert idea.
> Thanks
Why are you defining an alias? Was it because you had no luck connecting
using the named instance? The named instance runs on a different port
than the default 1433. I think it might use 1434, but am not sure. Try
using the instance name first before resorting to using an alias. For
example, if you remote computer name is MY_COMPUTER and the named
instance is SQL2, try the following as the server name:
MY_COMPUTER\SQL2
If you still have trouble, it could be a firewall issue. Try temporarily
disabling any firewall software and see if that helps.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||First of all David, thanks for your reply...
I am at client computer and how client computer diagnose MY_COMPUTER\SQL2
until and unless I have to mention that the ip which is running behind the
MY_Computer... this My_Computer doesn't exist on the network... that's why I
have to mention this My_Computer IP in the client network utility so that it
can easily diagnose it... I am mentioning you what I did for connecting named
instance... if I am wrong then please correct me...
Step 1: I did findout the port No of named instance through SQL Server
Network Utility that was 1434 right.
Step 2: I have mantioned that port and IP in client network utility like
Netowork Utility > Alias >
Alias: RemoteServer
Server: 209.45.23.55
Port : 1434
Protocol: TCP/IP
when I registered this named instance server through enterprise manager I
have defiled RemoteServer/Instancename and then trying to connect but
failed... sql server doesn't exist or access denied...
David, as you said I have to connect simply through MY_COMPUTER\SQL2 but how
my client machine know MY_Computer IP ?
I hope you understand what I wanna tell you... I would really appreciate if
you reply me ASAP... thanks and have a great day,
"David Gugick" wrote:

> Roy wrote:
> Why are you defining an alias? Was it because you had no luck connecting
> using the named instance? The named instance runs on a different port
> than the default 1433. I think it might use 1434, but am not sure. Try
> using the instance name first before resorting to using an alias. For
> example, if you remote computer name is MY_COMPUTER and the named
> instance is SQL2, try the following as the server name:
> MY_COMPUTER\SQL2
> If you still have trouble, it could be a firewall issue. Try temporarily
> disabling any firewall software and see if that helps.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||David, what I am thinking right now... do I need to create linked server
before registering the named instance on my machine ?
"David Gugick" wrote:

> Roy wrote:
> Why are you defining an alias? Was it because you had no luck connecting
> using the named instance? The named instance runs on a different port
> than the default 1433. I think it might use 1434, but am not sure. Try
> using the instance name first before resorting to using an alias. For
> example, if you remote computer name is MY_COMPUTER and the named
> instance is SQL2, try the following as the server name:
> MY_COMPUTER\SQL2
> If you still have trouble, it could be a firewall issue. Try temporarily
> disabling any firewall software and see if that helps.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Rogers wrote:
> David, what I am thinking right now... do I need to create linked
> server before registering the named instance on my machine ?
>
If you're just connecting over the internet (which I assume from your
last post that you are), you're correct that you'll need to use the IP
address. This just makes me think there's a firewall issue on the remote
PC or remote network preventing communications on that IP over port
1434. To determine this, you could swap ports on the remote server and
make the named instance 1433 and cycle the SQL Server. If you then
connect to the named instance, you'll know it's a firewall issue.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David, you are very right but how can I change the port of default instance
to 1434 and named instance to 1433 ... basically this is our production
machine and couple of databases are running to hit default instance.. what u
think if i do this then all would be in trouble right...
Looking forward to your reply.
Thanks
"David Gugick" wrote:

> Rogers wrote:
> If you're just connecting over the internet (which I assume from your
> last post that you are), you're correct that you'll need to use the IP
> address. This just makes me think there's a firewall issue on the remote
> PC or remote network preventing communications on that IP over port
> 1434. To determine this, you could swap ports on the remote server and
> make the named instance 1433 and cycle the SQL Server. If you then
> connect to the named instance, you'll know it's a firewall issue.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Rogers wrote:
> David, you are very right but how can I change the port of default
> instance to 1434 and named instance to 1433 ... basically this is our
> production machine and couple of databases are running to hit default
> instance.. what u think if i do this then all would be in trouble
> right...
>
Talk to your network guys and see if the firewall is in play.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Alright..... David, tell me one thing just for information... we can change
the default and named instance port through sql server network utilities
right ?
Thanks and looking forward to your reply
thanks david !
"David Gugick" wrote:

> Rogers wrote:
> Talk to your network guys and see if the firewall is in play.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Rogers wrote:
> Alright..... David, tell me one thing just for information... we can
> change the default and named instance port through sql server network
> utilities right ?
> Thanks and looking forward to your reply
You should be able to, but since your default instance is an in-use
production database, I'd caution doing so during production hours.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes but if both the databases are not in used then I can change the port of
named and default instance right through SQL Server Network Utlitiy .
Thanks
"David Gugick" wrote:

> Rogers wrote:
> You should be able to, but since your default instance is an in-use
> production database, I'd caution doing so during production hours.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

High CPU time per user as soon as they connect?

On a couple of our servers, users are currently scoring very
high on "CPU Time" as reported by Enterprise Manager and by
sp_who2, both as soon as they log in, and afterwards -
5,000,000 at login and 47,000,000 later on are the top figures.
Most of the fleet is on SQL Server 2000 Service Pack 2, with
between 2 and 8 hard-working processors. The hardest working
server is on Service Pack 3(a), clustered, and its top 5
current values in CPU time are between 382,847 and 705,879.
The servers are all on clients' sites and networks, with client's
choice of firewall. Usual application is a Java interface using
jTDS, but CPU time is also reported high for a new Query Analyzer
connection. SQL Server, not Windows, accounts are most often used.
Clients aren't complaining of poor performance, apparently, but
maybe they already gave up phoning for that.
So, should we worry? Reboot? Reindex? Patch? Disinfect?
Or ask around, "Who left SQL Profiler running"...i would run profiler to find out whats going on.
i would also run perfmon to find out what average cpu is
over the entire day
otherwise, you have nothing from which to make reasonable
assessments
>--Original Message--
>On a couple of our servers, users are currently scoring
very
>high on "CPU Time" as reported by Enterprise Manager and
by
>sp_who2, both as soon as they log in, and afterwards -
>5,000,000 at login and 47,000,000 later on are the top
figures.
>Most of the fleet is on SQL Server 2000 Service Pack 2,
with
>between 2 and 8 hard-working processors. The hardest
working
>server is on Service Pack 3(a), clustered, and its top 5
>current values in CPU time are between 382,847 and
705,879.
>The servers are all on clients' sites and networks, with
client's
>choice of firewall. Usual application is a Java
interface using
>jTDS, but CPU time is also reported high for a new Query
Analyzer
>connection. SQL Server, not Windows, accounts are most
often used.
>Clients aren't complaining of poor performance,
apparently, but
>maybe they already gave up phoning for that.
>So, should we worry? Reboot? Reindex? Patch?
Disinfect?
>Or ask around, "Who left SQL Profiler running"...
>.
>

hierarchical trees

Hi all and thanx in advance...

There is a command in Oracle which is "connect by prior" and i think it's a function to build "hierarchical trees", so where can I find the source of this function, or if anybody knows it please tell me...

What i mean is there would be an SQL function before "connect by prior" function written...

Example... I can write a function called called sub

function sub(x,y)
{
Z= X - Y
return z
}

So and then in my program i can use z=sub(5,3) instead of z=5-3, i think connect by prior is same thing but of course longer coding....
Where can i get this code?Next MSSQLSERVER version will have ANSI resursive join implemented (maybe).
See http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20514065.html
for hints and resources where to start.

Good luck !

Hierarchical queries in SQL Server 2000

Hi,
Do we have Hierarchical queries in SQL Server 2000 (like
that by using start with...connect by prior... in
Oracle)?
If someone has worked on some work-around to do so
in SQL Server 2000, pl. let me know.
Thanks in advance.
Regards
M. Subbaiahwe don't have any such things in SQL Server 2000. Though we have it in SQL
Server 2005 which is the Common table Expression.
The work around is to use a table variable and poplute it in a while loop or
use a recursive stored procedure.
--
"Subbaiah" wrote:

> Hi,
> Do we have Hierarchical queries in SQL Server 2000 (like
> that by using start with...connect by prior... in
> Oracle)?
> If someone has worked on some work-around to do so
> in SQL Server 2000, pl. let me know.
>
> Thanks in advance.
> Regards
> M. Subbaiah
>
>|||Take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||>> Do we have Hierarchical queries in SQL Server 2000 (like that by using st
art with...connect by prior... in Oracle)? <<
The Oracle construct is a cursor hidden in the proprietary syntax.
Geta copy of TREES & HIERARCHIES IN SQL for several other ways to do
this kidn of thing by haivng proper DDL instead of doing it with
recursive or procedural code.

Hiding System Tables when connecting with non-microsoft clients

I have a user who would like to connect to a SQL Server database using SAS (a
statistical application) and have the system tables not be displayed
Currently, when the user connects they are shown all of the tables
(including system table). We have inquired on the SAS side and have been
told that it is something that must be on the database side.
Do I need to remove some default permissions? Or send a special connection
string? I know in Ent. Manager, you just clear the show system tables
checkbox - but there is not an option like that when connecting with SAS.
No, it is not something that you can do in the database. You'll have to get
your SAS guys to change their query that retreives the table list. For
example, the following query returns system and user tables, where as the
second one doesn't:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'isMSShipped') = 0
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'isMSShipped') = 0
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike" <Mics_79@.online.nospam> wrote in message
news:AD6C6A91-7912-429F-90BA-7169CA29B641@.microsoft.com...
I have a user who would like to connect to a SQL Server database using SAS
(a
statistical application) and have the system tables not be displayed
Currently, when the user connects they are shown all of the tables
(including system table). We have inquired on the SAS side and have been
told that it is something that must be on the database side.
Do I need to remove some default permissions? Or send a special connection
string? I know in Ent. Manager, you just clear the show system tables
checkbox - but there is not an option like that when connecting with SAS.