Hello,
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
SylviaTry specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegroups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:\')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegroups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>|||That did it! Thanks much - it's just the kludge I needed to get past
this.
Tuesday, March 27, 2012
Accessing lower level members
Hi All,
returns all members of State Provinces which have USA as its parent.
I have used .Children function to retrieve members of dimension at one
level below the current level of dimension. For example (using FoodMart
2000 and AS 2000)
Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( [Customers].[Country].[USA].Children ) on rows
>From sales
This query returns me Children of member Country which is USA. Itreturns all members of State Provinces which have USA as its parent.
Can I access or retrieve all members of City with Country member being
USA?
I am building a web application where user applies filters. This is
necessary because if user wants to view sales data about City which
belongs to country USA.
Many thanks in advance.
Raghu
Hi Raghu. Yes, you can retrieve members of City where the Country is USA. Use the MDX DESCENDANTS() function. Change your query to the following:
Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( DESCENDANTS([Customers].[Country].[USA], [Customers].[City]) ) on rows
From sales
Here's a link to the BOL description of the DESCENDANTS() function:
http://msdn2.microsoft.com/en-us/library/ms146075.aspx
Good luck - Paul Goldy
|||Thanks Paul.
This solved it.
Raghu
Accessing local temporary tables
I've been able to get the local temporary table name from sysobjects, but I still can't select from it.
for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.
I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.
I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.
Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.
They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.
for example, the following select statement won't work
select * from #temptable_____00015
It says invalid object name.
I've even stored the temp table name in a variable and tryed to execute dynamic sql to get to it--no luck.
I need this technique to handle two support situations: A user freezes during data entry to a temp table, I want to capture the data before they reboot, so they don't have to reenter.
Temp tables are used among several stored procedures and then crunched into other tables. I'm getting incorrect results and want to see the raw data in the temp tables to assist me in figuring out what's going on.Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.|||Originally posted by rnealejr
Local temporary tables are connection based in scope so only the connection that created it may use it. Depending on your use you may need to use global temporary tables.
They exist on the hard drive. Maybe if I changed the status value in sysobjects, I could select from them?|||Please define in detail the issue you are trying to solve.
Accessing linked servers dynamically
We have a view in one database that consists of the union of all the
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!
Accessing linked server
Is there any way to access a linked server without using four-part naming?
My problem is that I am trying to find a way to connect to an access database on a 64 bit system (there is no 64 Jet OleDb provider) and I have to run in a 64 bit process so no WoW solutions will work for me 
I am looking into trying to use a Sql Linked server but am trying to find a workaround without having to re-write all our queries to use four-part naming.
Is there someway to configure a connectionstring to default to executing against a linked server?
Any help or suggestions would be welcomed.
You can create a synonym (if your server is SQL 2005) for a four-part name.
See CREATE SYNONYM.|||Brilliant thanks for that Anton,
Had a bit of a play and it seems to work nicely. I guess I will run into trouble if any of the queries contain VBA expressions but that gets me a long way.
I am going to make a dumby Sql Database that contains no objects only the synonyms for the tables and views that I need in the Access database, then I can create a connection to that dumby database for any of the Jet work I need to do.
As a thought: I guess given that the synonyms are entities of the Sql database then preprossing will occur at the Sql Server, therefore it is unlikely that you could configure seemless passthrough style execution (in terms of the Sql dialect to use) to the Jet Linked server because Sql will always want its own dialect rather than the Jet dialect.
For example if I wanted a PIVOT/Cross Tab query, I would need to decide to send a request to a precompiled Jet query that contained a 'TRANSFORM' statement or send a Sql String with the Sql Server Style 'PIVOT....FOR...' Statement. Would this be right?
Thanks
Simon
Accessing Linked Excel Server
I created a linked Excel server that is stored in a SQL2000 database.
I can run the following from the SQL server with no problem.
Select * From CSCNEDI...EDI$
When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Thanks
David Davis
Schuette Inc.
Hi, David,
The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:
== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again
== If you are using NT authentication, try using SQL authentication to see if this changes the effect
== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Accessing jobs in EM
Hello!
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorThere is no supported way to do this with Enterprise
Manager. The proxy account doesn't really come in to play
here. The system stored procedures involved in displaying
the job info in Enterprise Manager have checks for job owner
or sysadmin server role membership.
-Sue
On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Hello!
>
> I would like to allow non-admin users seeing all jobs (including ones
>they do now own) in Enterpise Manager.SQL Profiler displays execution of
>exec msdb..sp_help_job when querying job list. According to BOL: ' A user
>who is not a member of the sysadmin fixed role can use sp_help_job to view
>only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't b
e
>of any help in this case. Is this possible for non-admin users to see all
>jobs?
>
>Thanks,
>Igor
>|||Thanks,Sue. I have found another way:
1.. Grant access to MSDB
2.. Add users to the member of TargetServerRole.
Regards,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
> There is no supported way to do this with Enterprise
> Manager. The proxy account doesn't really come in to play
> here. The system stored procedures involved in displaying
> the job info in Enterprise Manager have checks for job owner
> or sysadmin server role membership.
> -Sue
> On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>|||Okay but just remember it's not supported though and how
this works with this role depends on what service pack you
are on.
-Sue
On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Thanks,Sue. I have found another way:
> 1.. Grant access to MSDB
> 2.. Add users to the member of TargetServerRole.
>Regards,
>Igor
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
>|||Thanks a lot,Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5f5dv0d14fq7ajas3r6qq577is20d0gn57@.
4ax.com...
> Okay but just remember it's not supported though and how
> this works with this role depends on what service pack you
> are on.
> -Sue
> On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>
I would like to allow non-admin users seeing all jobs (including ones
they do now own) in Enterpise Manager.SQL Profiler displays execution of
exec msdb..sp_help_job when querying job list. According to BOL: ' A user
who is not a member of the sysadmin fixed role can use sp_help_job to view
only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't be
of any help in this case. Is this possible for non-admin users to see all
jobs?
Thanks,
IgorThere is no supported way to do this with Enterprise
Manager. The proxy account doesn't really come in to play
here. The system stored procedures involved in displaying
the job info in Enterprise Manager have checks for job owner
or sysadmin server role membership.
-Sue
On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Hello!
>
> I would like to allow non-admin users seeing all jobs (including ones
>they do now own) in Enterpise Manager.SQL Profiler displays execution of
>exec msdb..sp_help_job when querying job list. According to BOL: ' A user
>who is not a member of the sysadmin fixed role can use sp_help_job to view
>only the jobs he/she owns.'. I suppose xp_sqlagent_proxy_account wouldn't b
e
>of any help in this case. Is this possible for non-admin users to see all
>jobs?
>
>Thanks,
>Igor
>|||Thanks,Sue. I have found another way:
1.. Grant access to MSDB
2.. Add users to the member of TargetServerRole.
Regards,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
> There is no supported way to do this with Enterprise
> Manager. The proxy account doesn't really come in to play
> here. The system stored procedures involved in displaying
> the job info in Enterprise Manager have checks for job owner
> or sysadmin server role membership.
> -Sue
> On Fri, 21 Jan 2005 14:46:12 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>|||Okay but just remember it's not supported though and how
this works with this role depends on what service pack you
are on.
-Sue
On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
<igormarchenko@.hotmail.com> wrote:
>Thanks,Sue. I have found another way:
> 1.. Grant access to MSDB
> 2.. Add users to the member of TargetServerRole.
>Regards,
>Igor
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:lvnav0d2r3c3n64s620e9me5o0o7t1pdl6@.
4ax.com...
>|||Thanks a lot,Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5f5dv0d14fq7ajas3r6qq577is20d0gn57@.
4ax.com...
> Okay but just remember it's not supported though and how
> this works with this role depends on what service pack you
> are on.
> -Sue
> On Tue, 25 Jan 2005 10:39:09 -0800, "Igor Marchenko"
> <igormarchenko@.hotmail.com> wrote:
>
>
Accessing Java within SQL Server
have a Java Class that I want to be able to access from within a SQL Server Stored procedure. I know we can access C# and other .NET Framework code, but how can you access a Java Class?
How would you access a Java class from "normal" .NET code?Niels
Accessing Initiating Event + Data From Existing External DB
Hello,
I have been exploring NS and I would like to use it as a rudimentary transfer tool. I have an existing database table from where I would to transfer a record to subscribers and then possibly move the records out of that table.
I have been exploring NS and I would like to use it as a rudimentary transfer tool. I have an existing database table from where I would to transfer a record to subscribers and then possibly move the records out of that table.
1. How do I set up the ..\SubscriptionClasses\SubscriptionClass\EventRules\EventRule so that it reads from another database? The TSQL Statement should probably work accross databases?
2. As I scroll through the sample ADFs I would think that ..\Providers\HostedProvider should also change too to another type.
Thank you very much,
Lubomir
Hi Lubomir -
Using the SQL Server Event Provider, you can collect events from tables in other databases or even other instances of SQL Server.
Configure the HostedProvider node to use the SQL Server Event Provider. Use the EventsQuery element to enter the T-SQL code that you will use to recognize new rows (or events of interest) in the table. Here's a sample.
<HostedProvider>
<ProviderName>SqlPrEP</ProviderName>
<ClassName>SQLProvider</ClassName>
<SystemName>%_NSSystem_%</SystemName>
<Schedule>
<Interval>P0DT00H00M60S</Interval>
</Schedule>
<Arguments>
<Argument>
<Name>EventsQuery</Name>
<Value>SELECT rowId, col1, col2, col3 FROM AnotherDb.dbo.vwCurrentRows WHERE rowId NOT IN (SELECT rowId FROM MyChron)</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>PressRelease</Value>
</Argument>
</Arguments>
</HostedProvider>
Next you can use the EventRule node of the SubscriptionClass to define your match rule; that is to write the T-SQL code that matches the events that you've collected to those subscribers who are interested in your events.
You EventRule would look something like this.
<EventRule>
<EventClassName>PressRelease</EventClassName>
<RuleName>PrEventRule</RuleName>
<Action>
INSERT INTO PrNotifications(
SubscriberId,
DeviceName,
SubscriberLocale,
col1,
col2)
SELECT
s.SubscriberId,
s.SubscriberDeviceName,
s.SubscriberLocale,
e.col1,
e.col2
FROM
PressRelease e,
PrSubscription s
WHERE
e.col3 = s.col3
</Action>
<ActionTimeout>P0DT00H00M45S</ActionTimeout>
</EventRule>
HTH...
Joe
Accessing Index Server from SQL Server on different system
Hi
I have SQL Server and Index Server running on 2 different system.
How can I call Index server from SQL Server (store procedure) in such
case.
Regards
> Hi
> I have SQL Server and Index Server running on 2 different system.
> How can I call Index server from SQL Server (store procedure) in such
> case.
> Regards
You can connect use the OLE DB Provider for Microsoft Indexing Service to
connect to the remote Index Server. More info:
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_qd_12_0h0l.asp
Eric Crdenas
Support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
I have SQL Server and Index Server running on 2 different system.
How can I call Index server from SQL Server (store procedure) in such
case.
Regards
> Hi
> I have SQL Server and Index Server running on 2 different system.
> How can I call Index server from SQL Server (store procedure) in such
> case.
> Regards
You can connect use the OLE DB Provider for Microsoft Indexing Service to
connect to the remote Index Server. More info:
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_qd_12_0h0l.asp
Eric Crdenas
Support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Accessing Index Server from SQL Server on different system
Hi
I have SQL Server and Index Server running on 2 different system.
How can I call Index server from SQL Server (store procedure) in such
case.
Regards
> Hi
> I have SQL Server and Index Server running on 2 different system.
> How can I call Index server from SQL Server (store procedure) in such
> case.
> Regards
--
You can connect use the OLE DB Provider for Microsoft Indexing Service to
connect to the remote Index Server. More info:
http://msdn.microsoft.com/library/d...-us/acdata/ac_8
_qd_12_0h0l.asp
Eric Crdenas
Support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
I have SQL Server and Index Server running on 2 different system.
How can I call Index server from SQL Server (store procedure) in such
case.
Regards
> Hi
> I have SQL Server and Index Server running on 2 different system.
> How can I call Index server from SQL Server (store procedure) in such
> case.
> Regards
--
You can connect use the OLE DB Provider for Microsoft Indexing Service to
connect to the remote Index Server. More info:
http://msdn.microsoft.com/library/d...-us/acdata/ac_8
_qd_12_0h0l.asp
Eric Crdenas
Support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Accessing Index Server from SQL Server on different system
Hi
I have SQL Server and Index Server running on 2 different system.
How can I call Index server from SQL Server (store procedure) in such
case.
Regards> Hi
> I have SQL Server and Index Server running on 2 different system.
> How can I call Index server from SQL Server (store procedure) in such
> case.
> Regards
--
You can connect use the OLE DB Provider for Microsoft Indexing Service to
connect to the remote Index Server. More info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_qd_12_0h0l.asp
--
Eric Cárdenas
Support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
I have SQL Server and Index Server running on 2 different system.
How can I call Index server from SQL Server (store procedure) in such
case.
Regards> Hi
> I have SQL Server and Index Server running on 2 different system.
> How can I call Index server from SQL Server (store procedure) in such
> case.
> Regards
--
You can connect use the OLE DB Provider for Microsoft Indexing Service to
connect to the remote Index Server. More info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_qd_12_0h0l.asp
--
Eric Cárdenas
Support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
accessing image files stored as binary data
Hi
When images are uploaded and stored directly into a sql database as binary data (eg in the club starter kit) how can those images be accessed and displayed.
When I open the images table in VWD and select display data, the cells holding the image data hold a <binary data> tag. What I want to be able to do is get at that data, or actually get at the image so that it is displayed. My reason is this, at the moment the only way to access the images in the sql database after they have been uploaded is to log into the website and view them as an administrator of the site. It would be much simpler if I could access the database directly and view the contents of the images table.
Any ideas?
Thanks
If you're trying to displaying the image stored in sql server from a grid view, perhaps you should read the following post:
http://forums.asp.net/thread/1337670.aspx
Hope that helps
|||This is the same post you made here:http://forums.asp.net/thread/1337011.aspx. Please do not post the same question multiple times.Accessing HttpContext.Current from code section in report
I am trying to access HttpContext.Current in my report code section (report properties) since I have to look at a cookie. However, HttpContext.Current is always null. Has anyone managed to access HttpContex.Current from a report?
Thanks in advance.
Ok, it was security permission problem with the code section. After deploying the report I got the #Error when trying to access HttpContext.Current. I modified the rssrvpolicy.config (location c:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\) to FullTrust for the Code section and then it started working.
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust"
Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report
expressions and Code element. ">
instead of
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="Execution"
Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report
expressions and Code element. ">
Probably not recommended but for now I am just testing. Will move the code to an assembly later and give that assembly FullTrust instead.
Accessing grouped values "inside" fields
Hello,
I'm struggling to make operations on results obtained from grouping by
specific dimensions. To be more specific, I'd like to substract value of
e.g. sales for some product for one year from values from the preceding
year.
I'd like to somehow access the resulting recordset from grouping and make
some operations on these values, just like the SUBTOTAL function does.
Thanks in advance for any help.
Best wishes,
Marek T³uczekOne correction to my question:
> I'd like to somehow access the resulting recordset from grouping and make
> some operations on these values, just like the SUBTOTAL function does.
I meant not recordset, but set of fields.
> Thanks in advance for any help.
> Best wishes,
> Marek T³uczek
>
>
I'm struggling to make operations on results obtained from grouping by
specific dimensions. To be more specific, I'd like to substract value of
e.g. sales for some product for one year from values from the preceding
year.
I'd like to somehow access the resulting recordset from grouping and make
some operations on these values, just like the SUBTOTAL function does.
Thanks in advance for any help.
Best wishes,
Marek T³uczekOne correction to my question:
> I'd like to somehow access the resulting recordset from grouping and make
> some operations on these values, just like the SUBTOTAL function does.
I meant not recordset, but set of fields.
> Thanks in advance for any help.
> Best wishes,
> Marek T³uczek
>
>
Accessing Global Cursor
hi friends,
Here is the stored procedures that I used.
--------------------------
create procedure globalCursor
AS
DECLARE abc CURSOR GLOBAL FOR
select * from sales
OPEN abc
create procedure globalCursorTest
AS
DECLARE @.sdate datetime
DECLARE @.sperson varchar(15)
DECLARE @.sregion varchar(15)
DECLARE @.sales int
EXECUTE globalCursor
FETCH NEXT FROM abc INTO @.sdate, @.sperson, @.sregion, @.sales
print @.sdate
print @.sperson
print @.sregion
print @.sales
--------------------------
When I execute globalCursorTest using SQL Query Analyser, it says
--------------------------
Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
A cursor with the name 'abc' already exists.
Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
The cursor is already open.
--------------------------
how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.
JakeLooks like the abc cursor is not closed/deallocated. Does either one of the procedures perform these actions?|||Ummm...
Do you have an Oracle background?
To my knowledge it doesn't work that way, though I'll go test it out...
And yes, as Kaiowas points out you need to
CLOSE ABC
DEALLOCATE ABC
But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||hi brett,
I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.
The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.
I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.
Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.
Appreciate your he
Jake
Originally posted by Brett Kaiser
Ummm...
Do you have an Oracle background?
To my knowledge it doesn't work that way, though I'll go test it out...
And yes, as Kaiowas points out you need to
CLOSE ABC
DEALLOCATE ABC
But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||anybody know about this....
Originally posted by Jake K
hi brett,
I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.
The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.
I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.
Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.
Appreciate your he
Jake|||"In DB2, one procedure can access the cursors opened by another procedure, after calling it."
Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||I guess my best suggestion would be to rewrite your cursor procedure as a table function.|||hi,
it's definitely not scope disaster!!! By default, the cursors opened in a procedure could not be accessed from another procedure. If one wants this kind of feature, the cursor has to be specially declared with the option "WITH RETURN TO CALLER/CLIENT". It's like Sequel's local & global cursor concept. In global cursor, the cursor can be accessed from outside where it is declared.
Jake
Originally posted by blindman
"In DB2, one procedure can access the cursors opened by another procedure, after calling it."
Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||thanks for your suggestion. as of now, i don't know about table function. I will try it out...
but i have another way of achieving this. the procedure that i attached in the starting mail is working fine, of course after including close & disallocate stmts at the end of the second procedure, globalCursorTest.
previously i used SQL Query Analyser GUI which will compile & execute the procedure at one shot. Thus the globalCursor procedure executed twice, which caused the 'cursor already opened' error.
As I mentioned in my earlier mails, i search for a mechanism which will only compile & create the procedure into the db without executing it. I find isql command line tool creates the procedure into the db without executing it.
After creating both the procedures, i executed second procedure, globalCursorTest. It works fine.
friends, Thanks for your time.
Jake
Originally posted by blindman
I guess my best suggestion would be to rewrite your cursor procedure as a table function.
Here is the stored procedures that I used.
--------------------------
create procedure globalCursor
AS
DECLARE abc CURSOR GLOBAL FOR
select * from sales
OPEN abc
create procedure globalCursorTest
AS
DECLARE @.sdate datetime
DECLARE @.sperson varchar(15)
DECLARE @.sregion varchar(15)
DECLARE @.sales int
EXECUTE globalCursor
FETCH NEXT FROM abc INTO @.sdate, @.sperson, @.sregion, @.sales
print @.sdate
print @.sperson
print @.sregion
print @.sales
--------------------------
When I execute globalCursorTest using SQL Query Analyser, it says
--------------------------
Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
A cursor with the name 'abc' already exists.
Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
The cursor is already open.
--------------------------
how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.
JakeLooks like the abc cursor is not closed/deallocated. Does either one of the procedures perform these actions?|||Ummm...
Do you have an Oracle background?
To my knowledge it doesn't work that way, though I'll go test it out...
And yes, as Kaiowas points out you need to
CLOSE ABC
DEALLOCATE ABC
But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||hi brett,
I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.
The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.
I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.
Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.
Appreciate your he
Jake
Originally posted by Brett Kaiser
Ummm...
Do you have an Oracle background?
To my knowledge it doesn't work that way, though I'll go test it out...
And yes, as Kaiowas points out you need to
CLOSE ABC
DEALLOCATE ABC
But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||anybody know about this....
Originally posted by Jake K
hi brett,
I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.
The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.
I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.
Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.
Appreciate your he
Jake|||"In DB2, one procedure can access the cursors opened by another procedure, after calling it."
Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||I guess my best suggestion would be to rewrite your cursor procedure as a table function.|||hi,
it's definitely not scope disaster!!! By default, the cursors opened in a procedure could not be accessed from another procedure. If one wants this kind of feature, the cursor has to be specially declared with the option "WITH RETURN TO CALLER/CLIENT". It's like Sequel's local & global cursor concept. In global cursor, the cursor can be accessed from outside where it is declared.
Jake
Originally posted by blindman
"In DB2, one procedure can access the cursors opened by another procedure, after calling it."
Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||thanks for your suggestion. as of now, i don't know about table function. I will try it out...
but i have another way of achieving this. the procedure that i attached in the starting mail is working fine, of course after including close & disallocate stmts at the end of the second procedure, globalCursorTest.
previously i used SQL Query Analyser GUI which will compile & execute the procedure at one shot. Thus the globalCursor procedure executed twice, which caused the 'cursor already opened' error.
As I mentioned in my earlier mails, i search for a mechanism which will only compile & create the procedure into the db without executing it. I find isql command line tool creates the procedure into the db without executing it.
After creating both the procedures, i executed second procedure, globalCursorTest. It works fine.
friends, Thanks for your time.
Jake
Originally posted by blindman
I guess my best suggestion would be to rewrite your cursor procedure as a table function.
Accessing FTP site from sql server
How to create DTD compatible XML file from result set returned from a query.
Can we access FTP site and upload this xml ?
Is it possible to do from Tsql?
I suggest that you might use CLR stored procedures to accomplish what you desire. You can write C# code to extend the functionality of SQL Server and have the client call a custom stored procedure to execute that C# code. This should enable you to populate a document in any format from data stored in the server and connect over FTP to upload the file.
Hope this helps,
John
|||http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx
Maybe this site can help you with witing the extended store procedure to accomplish what you want to do.
Please that DTD is deprecated , you may want to look into using XSD.
Accessing FTP site from sql server
How to create DTD compatible XML file from result set returned from a query.
Can we access FTP site and upload this xml ?
Is it possible to do from Tsql?
I suggest that you might use CLR stored procedures to accomplish what you desire. You can write C# code to extend the functionality of SQL Server and have the client call a custom stored procedure to execute that C# code. This should enable you to populate a document in any format from data stored in the server and connect over FTP to upload the file.
Hope this helps,
John
|||http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx
Maybe this site can help you with witing the extended store procedure to accomplish what you want to do.
Please that DTD is deprecated , you may want to look into using XSD.
accessing from T-SQL a database on another DBMS
tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what you want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-think the approach if
possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA. :)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>> tHi
>> I want to build a trigger to modify data in a table in a database that is
>> running on another Database Engine in may LAN. I don' t know if that is
>> possible. If it is, how will I make the connection to that database?
>> Thanks in advance - Waldo
>
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what you want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-think the approach if
possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA. :)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>> tHi
>> I want to build a trigger to modify data in a table in a database that is
>> running on another Database Engine in may LAN. I don' t know if that is
>> possible. If it is, how will I make the connection to that database?
>> Thanks in advance - Waldo
>
accessing from T-SQL a database on another DBMS
tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - Waldo
I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - Waldo
I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>
accessing from T-SQL a database on another DBMS
tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what y
ou want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-t
hink the approach if
possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what y
ou want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-t
hink the approach if
possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>
Accessing from linux
Hi All,
Can I connect to the MS SQL database from the Redhat Linux 9.
Is there MS SQL client or database driver on linux available. I wanted to use the data from the MS SQL database in the C++ program running on Linux.
Thanks in advance,
torpedoYou can use ODBC driver. Just find and download the correct one.
I had quick serch on google and found this:
http://www.datadirect.com/download/index.ssp|||On Linux, you probably want Free TDS (http://www.freetds.org/).
-PatP
Can I connect to the MS SQL database from the Redhat Linux 9.
Is there MS SQL client or database driver on linux available. I wanted to use the data from the MS SQL database in the C++ program running on Linux.
Thanks in advance,
torpedoYou can use ODBC driver. Just find and download the correct one.
I had quick serch on google and found this:
http://www.datadirect.com/download/index.ssp|||On Linux, you probably want Free TDS (http://www.freetds.org/).
-PatP
Accessing from desktop - path not found.
Hi,
I am trying to write a desktop application using C# and .NET that can edit an SQL Server Mobile database stored on a device. The problem is no matter how I specify the data source, I get an error the path was not found. Heres the bit of the code:
Thanks again for your kind reply.
I receive This error message :
I am trying to write a desktop application using C# and .NET that can edit an SQL Server Mobile database stored on a device. The problem is no matter how I specify the data source, I get an error the path was not found. Heres the bit of the code:
string strConn = "Data Source=" + "Mobile Device\\My Documents\\Nutricom.sdf";
SqlCeConnection connDB = new SqlCeConnection(strConn);
connDB.Open();
And the error i get is..
The path is not valid. Check the directory for the database. [ Path = Mobile Device\My Documents\Nutricom.sdf ]
Everytime it thorws an error saying the path is invalid and to check the locaiton of the database. Ive tried it without the "Mobile Device" bit of the string and still nothing. The database is definately in this location.
Any help? It's driving me insane.
Lewis
You can not do that as remote SQL CE provider you have to use to accomplish that is undocumented and to be used by VS only.
Local provider you’re probably using is incapable of accessing database on device as device file system is not accessible from desktop using standard APIs.
Due to licensing restrictions you can use local desktop provider only if one of the following is true:
1. You have VS 2005 installed on this PC.
2. You have SQL Server 2005 installed on this PC.
3. Your PC is running Windows XP Tablet edition.
Assuming licensing conditions are met, you can copy data base from device using RAPI, open it with local provider, change it and copy it back to the device.
|||Hi,Thank you for your reply.
I am using Visual Studio 2005. Just to clarify are you saying there is no way to access the database on the device WITHOUT coping it over using RAPI? or just that it is un-documented?
I have considered using RAPI and will look into it. However I have fears it may cause syncronisation issues so I would prefer to do it the first way if possible.
|||
Using remote SQL Mobile provider is undocumented and probably would violate your license.
However, there's always a way (complexity on scale from 1 to 10):
1. Map device storage to desktop drive (8).
2. Make sure application which uses database is stopped on device while desktop is changing copy of the data base (1).
3. Add remote data access capabilities to your application (3).
4. Use 3rd party tools if available (1).
I'd go with #3 as it's simple enough and has no licensing issues.
|||Hi,Thanks again for your kind reply.
I'm afraid one again I don't fully understand 
sorry 
. I am new to programming on the. Net platform.
Number 3 sounds good to me but I don't understand what you mean by "remote data access capabilities". Is there anywhere I can find further information on this?
For anyone else wanting to do something similar I found an excellent set of libraries from OpenNETCF.org that simplify the RAPI method of coping the database tot he device and back again. I'll use them if I can't get one of the methods above working.
Lewis
|||
Basically that means what somewhere in your code you need to listen on the TCP/IP port, accepting command and data from desktop and sending requested data back. It's not related to .Net platform, concept is known for 40 years or so.
There are some 3rd part products like that:
http://www.gui-innovations.com/html/remotesqlce.html
|||
Ah thank you very very much.
At least I understand my options now.
Thanks again for your patience and help.
Lewis
|||I Still Have ProblemI receive This error message :
"The path is not valid. Check the directory for the database."
if any one solve this problem Tell me.
Thank You
mahyar
|||
I have developed some tools that may help you:
http://www.primeworks-mobile.com
Accessing from desktop - path not found.
Hi,
I am trying to write a desktop application using C# and .NET that can edit an SQL Server Mobile database stored on a device. The problem is no matter how I specify the data source, I get an error the path was not found. Heres the bit of the code:
Thanks again for your kind reply.
I receive This error message :
I am trying to write a desktop application using C# and .NET that can edit an SQL Server Mobile database stored on a device. The problem is no matter how I specify the data source, I get an error the path was not found. Heres the bit of the code:
string strConn = "Data Source=" + "Mobile Device\\My Documents\\Nutricom.sdf";
SqlCeConnection connDB = new SqlCeConnection(strConn);
connDB.Open();
And the error i get is..
The path is not valid. Check the directory for the database. [ Path = Mobile Device\My Documents\Nutricom.sdf ]
Everytime it thorws an error saying the path is invalid and to check the locaiton of the database. Ive tried it without the "Mobile Device" bit of the string and still nothing. The database is definately in this location.
Any help? It's driving me insane.
Lewis
You can not do that as remote SQL CE provider you have to use to accomplish that is undocumented and to be used by VS only.
Local provider you’re probably using is incapable of accessing database on device as device file system is not accessible from desktop using standard APIs.
Due to licensing restrictions you can use local desktop provider only if one of the following is true:
1. You have VS 2005 installed on this PC.
2. You have SQL Server 2005 installed on this PC.
3. Your PC is running Windows XP Tablet edition.
Assuming licensing conditions are met, you can copy data base from device using RAPI, open it with local provider, change it and copy it back to the device.
|||Hi,Thank you for your reply.
I am using Visual Studio 2005. Just to clarify are you saying there is no way to access the database on the device WITHOUT coping it over using RAPI? or just that it is un-documented?
I have considered using RAPI and will look into it. However I have fears it may cause syncronisation issues so I would prefer to do it the first way if possible.
|||
Using remote SQL Mobile provider is undocumented and probably would violate your license.
However, there's always a way (complexity on scale from 1 to 10):
1. Map device storage to desktop drive (8).
2. Make sure application which uses database is stopped on device while desktop is changing copy of the data base (1).
3. Add remote data access capabilities to your application (3).
4. Use 3rd party tools if available (1).
I'd go with #3 as it's simple enough and has no licensing issues.
|||Hi,Thanks again for your kind reply.
I'm afraid one again I don't fully understand sorry . I am new to programming on the. Net platform.
Number 3 sounds good to me but I don't understand what you mean by "remote data access capabilities". Is there anywhere I can find further information on this?
For anyone else wanting to do something similar I found an excellent set of libraries from OpenNETCF.org that simplify the RAPI method of coping the database tot he device and back again. I'll use them if I can't get one of the methods above working.
Lewis
|||
Basically that means what somewhere in your code you need to listen on the TCP/IP port, accepting command and data from desktop and sending requested data back. It's not related to .Net platform, concept is known for 40 years or so.
There are some 3rd part products like that:
http://www.gui-innovations.com/html/remotesqlce.html
|||
Ah thank you very very much.
At least I understand my options now.
Thanks again for your patience and help.
Lewis
|||I Still Have ProblemI receive This error message :
"The path is not valid. Check the directory for the database."
if any one solve this problem Tell me.
Thank You
mahyar
|||
I have developed some tools that may help you:
http://www.primeworks-mobile.com
Accessing Foxpro database over network
I have a report that needs to access a foxpro database that is located on
another server on the network. There are no issues in accessing the report
when building it but once it is deployed to the reportserver the database
cannot be accessed using either an OLE or ODBC connection.
The initial error message for ODBC is:
Cannot create a connection to data source 'FoxPro'.
(rsErrorOpeningConnection) ERROR [S1000] [Microsoft][ODBC Visual FoxPro
Driver]Cannot open file \\server\data\sapphire.dbc. ERROR [IM006]
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR
[01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the
version of ODBC behavior that the application requested (see SQLSetEnvAttr).
If I specify user credentials in the datasource properties, I receive the
following error:
Query execution failed for data set 'FoxPro'. (rsErrorExecutingCommand)
ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
\\server\data\\spjobs.dbf.
This is the table the query is being executed on. NTFileMon shows that the
process w3wp.exe has successfully read the sapphire.dbc file that failed in
the first instance but cannot open the spjobs.dbf file.
Similar errors occur using OLE.
I have tried giving full permissions for the files and network share for
everyone but this makes no difference. How can I get this report to work?
regards,
ChrisI am having similar problems with a link server (W2K to a VFP 6 dbc). The
error is
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro
Driver]Cannot open file \\myserver\myshare\folder\subfolder\mydbc.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
This is strangely intermittent. Some attempts work and some throw this
error. I repeat: this problem is intermittent. I'm beginning to suspect a
network problem, but don't have the faintest idea where to start
troubleshooting.
There are a number of posts in many different newsgroups concerning this
error, but no resolution. Would anyone from MS care to weigh in on this?
This issue is seriously hobbling my Biztalk EDI implementation, and the boss
wants a fix NOW.
TIA for your time and expertise.
--
Phil Hegedusich
Senior P/A
IIMAK
--
A skeleton walks into a bar and says, "I need a beer and a mop."
"Chris Diery" wrote:
> I have a report that needs to access a foxpro database that is located on
> another server on the network. There are no issues in accessing the report
> when building it but once it is deployed to the reportserver the database
> cannot be accessed using either an OLE or ODBC connection.
> The initial error message for ODBC is:
> Cannot create a connection to data source 'FoxPro'.
> (rsErrorOpeningConnection) ERROR [S1000] [Microsoft][ODBC Visual FoxPro
> Driver]Cannot open file \\server\data\sapphire.dbc. ERROR [IM006]
> [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR
> [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the
> version of ODBC behavior that the application requested (see SQLSetEnvAttr).
> If I specify user credentials in the datasource properties, I receive the
> following error:
> Query execution failed for data set 'FoxPro'. (rsErrorExecutingCommand)
> ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
> \\server\data\\spjobs.dbf.
> This is the table the query is being executed on. NTFileMon shows that the
> process w3wp.exe has successfully read the sapphire.dbc file that failed in
> the first instance but cannot open the spjobs.dbf file.
> Similar errors occur using OLE.
> I have tried giving full permissions for the files and network share for
> everyone but this makes no difference. How can I get this report to work?
> regards,
> Chris
another server on the network. There are no issues in accessing the report
when building it but once it is deployed to the reportserver the database
cannot be accessed using either an OLE or ODBC connection.
The initial error message for ODBC is:
Cannot create a connection to data source 'FoxPro'.
(rsErrorOpeningConnection) ERROR [S1000] [Microsoft][ODBC Visual FoxPro
Driver]Cannot open file \\server\data\sapphire.dbc. ERROR [IM006]
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR
[01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the
version of ODBC behavior that the application requested (see SQLSetEnvAttr).
If I specify user credentials in the datasource properties, I receive the
following error:
Query execution failed for data set 'FoxPro'. (rsErrorExecutingCommand)
ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
\\server\data\\spjobs.dbf.
This is the table the query is being executed on. NTFileMon shows that the
process w3wp.exe has successfully read the sapphire.dbc file that failed in
the first instance but cannot open the spjobs.dbf file.
Similar errors occur using OLE.
I have tried giving full permissions for the files and network share for
everyone but this makes no difference. How can I get this report to work?
regards,
ChrisI am having similar problems with a link server (W2K to a VFP 6 dbc). The
error is
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro
Driver]Cannot open file \\myserver\myshare\folder\subfolder\mydbc.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
This is strangely intermittent. Some attempts work and some throw this
error. I repeat: this problem is intermittent. I'm beginning to suspect a
network problem, but don't have the faintest idea where to start
troubleshooting.
There are a number of posts in many different newsgroups concerning this
error, but no resolution. Would anyone from MS care to weigh in on this?
This issue is seriously hobbling my Biztalk EDI implementation, and the boss
wants a fix NOW.
TIA for your time and expertise.
--
Phil Hegedusich
Senior P/A
IIMAK
--
A skeleton walks into a bar and says, "I need a beer and a mop."
"Chris Diery" wrote:
> I have a report that needs to access a foxpro database that is located on
> another server on the network. There are no issues in accessing the report
> when building it but once it is deployed to the reportserver the database
> cannot be accessed using either an OLE or ODBC connection.
> The initial error message for ODBC is:
> Cannot create a connection to data source 'FoxPro'.
> (rsErrorOpeningConnection) ERROR [S1000] [Microsoft][ODBC Visual FoxPro
> Driver]Cannot open file \\server\data\sapphire.dbc. ERROR [IM006]
> [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR
> [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the
> version of ODBC behavior that the application requested (see SQLSetEnvAttr).
> If I specify user credentials in the datasource properties, I receive the
> following error:
> Query execution failed for data set 'FoxPro'. (rsErrorExecutingCommand)
> ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
> \\server\data\\spjobs.dbf.
> This is the table the query is being executed on. NTFileMon shows that the
> process w3wp.exe has successfully read the sapphire.dbc file that failed in
> the first instance but cannot open the spjobs.dbf file.
> Similar errors occur using OLE.
> I have tried giving full permissions for the files and network share for
> everyone but this makes no difference. How can I get this report to work?
> regards,
> Chris
Sunday, March 25, 2012
accessing fake data inside an INSTEAD OF trigger
Hello,
Is it possible to access inserted data through an UPDATE INSTEAD OF
trigger if the condition of the query does not match any row in a
table?
For example :
UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
If there is no row inside myTable matching the condition "WHERE
myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
error but the inserted table remains empty.
Is there a way to access the value 'abc' in such a situation?
thank you
sNo, you do not have access to the originating command, nor any
parameters of that command. What you have access to are the DELETED
and INSERTED tables, and the table that was updated.
Roy Harvey
Beacon Falls, CT
On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>Hello,
>Is it possible to access inserted data through an UPDATE INSTEAD OF
>trigger if the condition of the query does not match any row in a
>table?
>For example :
>UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
>If there is no row inside myTable matching the condition "WHERE
>myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
>error but the inserted table remains empty.
>Is there a way to access the value 'abc' in such a situation?
>thank you
>s|||OK ... but this data should exist somewhere.
I mean if the server returns an empty cursor when I do a SELECT * FROM
INSERTED within the trigger, it is because it has access to myColumn2 =
'xyz'. Any API to access the query content, even if it does not match
anything in the table?
thank you
Roy Harvey wrote:
> No, you do not have access to the originating command, nor any
> parameters of that command. What you have access to are the DELETED
> and INSERTED tables, and the table that was updated.
> Roy Harvey
> Beacon Falls, CT
>
> On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>|||On 3 Mar 2006 07:30:03 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>OK ... but this data should exist somewhere.
>I mean if the server returns an empty cursor when I do a SELECT * FROM
>INSERTED within the trigger, it is because it has access to myColumn2 =
>'xyz'. Any API to access the query content, even if it does not match
>anything in the table?
>thank you
Nothing like that I've ever heard of. Or imagined, for that matter.
Roy Harvey
Beacon Falls, CT
Is it possible to access inserted data through an UPDATE INSTEAD OF
trigger if the condition of the query does not match any row in a
table?
For example :
UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
If there is no row inside myTable matching the condition "WHERE
myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
error but the inserted table remains empty.
Is there a way to access the value 'abc' in such a situation?
thank you
sNo, you do not have access to the originating command, nor any
parameters of that command. What you have access to are the DELETED
and INSERTED tables, and the table that was updated.
Roy Harvey
Beacon Falls, CT
On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>Hello,
>Is it possible to access inserted data through an UPDATE INSTEAD OF
>trigger if the condition of the query does not match any row in a
>table?
>For example :
>UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
>If there is no row inside myTable matching the condition "WHERE
>myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
>error but the inserted table remains empty.
>Is there a way to access the value 'abc' in such a situation?
>thank you
>s|||OK ... but this data should exist somewhere.
I mean if the server returns an empty cursor when I do a SELECT * FROM
INSERTED within the trigger, it is because it has access to myColumn2 =
'xyz'. Any API to access the query content, even if it does not match
anything in the table?
thank you
Roy Harvey wrote:
> No, you do not have access to the originating command, nor any
> parameters of that command. What you have access to are the DELETED
> and INSERTED tables, and the table that was updated.
> Roy Harvey
> Beacon Falls, CT
>
> On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>|||On 3 Mar 2006 07:30:03 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>OK ... but this data should exist somewhere.
>I mean if the server returns an empty cursor when I do a SELECT * FROM
>INSERTED within the trigger, it is because it has access to myColumn2 =
>'xyz'. Any API to access the query content, even if it does not match
>anything in the table?
>thank you
Nothing like that I've ever heard of. Or imagined, for that matter.
Roy Harvey
Beacon Falls, CT
accessing external webservice or webpage from SQL 2005
Is it possible to access a webservice from within SQL 2005?
What I am looking to do is place a trigger on a specific table, and detect if a specific column is being updated. If it is, I want to launch a robust process that does x, y and z. Something like this:
1) record is updated indicating that an account is closed.
2) SQL launched a webservice/ aspx page that builds an HTML email template and then mail it.
thanks!
SQL Server 2005 does have XML web service support. Seehttp://msdn2.microsoft.com/en-us/library/ms191274.aspx.|||Wow, thanks for the speedy reply. The documentation behind the link you sent seems to refer to SQL server fielding SOAP requests and "listening" to and delivering data, where the SQL server is processing the request.
But is the opposite possible? SQL calling another webservice?
Accessing external data from SQL Server
Hi,
I have a Sybase dataserver containing lots of (huge) tables that I
would like to access from SQL Server (two different applications,
using two different datasources, now want to talk to each other). MS
Access has a feature for 'linking' external data. Can I do similar
stuff in SQL Server?
Basically, I am trying to avoid having to duplicate the data in both
the dataservers and worry about keeping them in sync.
Yes, look up linked servers in Books-Online. I haven't done this with
Sybase but would be surprised if you can't.
>
I already have the Sybase SQL drivers installed. Thanks a bunch for
your help.
I have a Sybase dataserver containing lots of (huge) tables that I
would like to access from SQL Server (two different applications,
using two different datasources, now want to talk to each other). MS
Access has a feature for 'linking' external data. Can I do similar
stuff in SQL Server?
Basically, I am trying to avoid having to duplicate the data in both
the dataservers and worry about keeping them in sync.
Regards,
Tanveer<arshad.tanveer@.gmail.comwrote in message
news:1174488978.618140.16840@.e65g2000hsc.googlegro ups.com...
Quote:
Originally Posted by
Hi,
I have a Sybase dataserver containing lots of (huge) tables that I
would like to access from SQL Server (two different applications,
using two different datasources, now want to talk to each other). MS
Access has a feature for 'linking' external data. Can I do similar
stuff in SQL Server?
Basically, I am trying to avoid having to duplicate the data in both
the dataservers and worry about keeping them in sync.
>
Regards,
Tanveer
>
Yes, look up linked servers in Books-Online. I haven't done this with
Sybase but would be surprised if you can't.
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||You can , but I think that MS SQL 2000 doesn't come with the drivers , you
have to install the SYBASE odbc drivers
--
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
<arshad.tanveer@.gmail.comwrote in message
news:1174488978.618140.16840@.e65g2000hsc.googlegro ups.com...
Quote:
Originally Posted by
Hi,
I have a Sybase dataserver containing lots of (huge) tables that I
would like to access from SQL Server (two different applications,
using two different datasources, now want to talk to each other). MS
Access has a feature for 'linking' external data. Can I do similar
stuff in SQL Server?
Basically, I am trying to avoid having to duplicate the data in both
the dataservers and worry about keeping them in sync.
>
Regards,
Tanveer
>
Quote:
Originally Posted by
You can , but I think that MS SQL 2000 doesn't come with the drivers , you
have to install the SYBASE odbc drivers
>
--
>
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at -http://www.ITjobfeed.com
>
<arshad.tanv...@.gmail.comwrote in message
>
news:1174488978.618140.16840@.e65g2000hsc.googlegro ups.com...
>
Quote:
Originally Posted by
Hi,
I have a Sybase dataserver containing lots of (huge) tables that I
would like to access from SQL Server (two different applications,
using two different datasources, now want to talk to each other). MS
Access has a feature for 'linking' external data. Can I do similar
stuff in SQL Server?
Basically, I am trying to avoid having to duplicate the data in both
the dataservers and worry about keeping them in sync.
>
Quote:
Originally Posted by
Regards,
Tanveer
I already have the Sybase SQL drivers installed. Thanks a bunch for
your help.
Accessing Excel functions in SQL SP
Hi,
I am trying to access an Excel function in a stored procedure. First of all,
is it possible, if it is, can someone give me an example.
Thank you.
--
RamIt may be possible with sp_OAMethod, but why do it to yourself? Even if you
can get to work without blowing up your computer, performance is going to be
rubbish!
SQL has loads of functions, and you can roll your own, do you know about
user-defined functions? They're great!
Anyway, tell us which function you want to emulate and we'll see what we can
do.
Plus, also bear in mind, SQL might have the function you need, but you just
don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there's
a
ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
some conditional logic, there's the CASE statement instead of Excel's IF.
Remember to post some DDL, sample data, expected results etc.
Damien
First of all, there are few things you can't do in SQL
"ram4tech" wrote:
> Hi,
> I am trying to access an Excel function in a stored procedure. First of al
l,
> is it possible, if it is, can someone give me an example.
> Thank you.
> --
> Ram|||Hi Damien:
The looked in BOL and on the net, but didn't had much luck. I wasn't aware
that there might be some performance issues. The excel function I am plannin
g
on using is IRR().
Thank you.
--
Ram
"Damien" wrote:
> It may be possible with sp_OAMethod, but why do it to yourself? Even if y
ou
> can get to work without blowing up your computer, performance is going to
be
> rubbish!
> SQL has loads of functions, and you can roll your own, do you know about
> user-defined functions? They're great!
> Anyway, tell us which function you want to emulate and we'll see what we c
an
> do.
> Plus, also bear in mind, SQL might have the function you need, but you jus
t
> don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there'
s a
> ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
> some conditional logic, there's the CASE statement instead of Excel's IF.
> Remember to post some DDL, sample data, expected results etc.
> Damien
> First of all, there are few things you can't do in SQL
> "ram4tech" wrote:
>|||Hello Ram !
http://groups.google.de/group/micro...5d4e46703ec82cd
HTH, jens Suessmeyer.|||Right, well you'd need to know the formula that sits behind IRR to recreate
it, but I guess it boils down to addition, subtraction, maybe an average or
two? T-SQL can do all that, but Excel is better at sums, I'll give it that.
So maybe you should play to the strengths, ie SQL for holding data,
concurrent access, raw power, Excel for sums.
Have you considered linking in to your server, eg a pivot table or external
query?
"ram4tech" wrote:
> Hi Damien:
> The looked in BOL and on the net, but didn't had much luck. I wasn't aware
> that there might be some performance issues. The excel function I am plann
ing
> on using is IRR().
> Thank you.
> --
> Ram
>
> "Damien" wrote:
>|||Here is a T-SQL implementation of IRR I posted a while back.
It is probably less robust than the Excel version, but it may still
work for you:
http://groups.google.co.uk/groups?q...eam+kass+newton
Steve Kass
Drew University
ram4tech wrote:
>Hi Damien:
>The looked in BOL and on the net, but didn't had much luck. I wasn't aware
>that there might be some performance issues. The excel function I am planni
ng
>on using is IRR().
>Thank you.
>
I am trying to access an Excel function in a stored procedure. First of all,
is it possible, if it is, can someone give me an example.
Thank you.
--
RamIt may be possible with sp_OAMethod, but why do it to yourself? Even if you
can get to work without blowing up your computer, performance is going to be
rubbish!
SQL has loads of functions, and you can roll your own, do you know about
user-defined functions? They're great!
Anyway, tell us which function you want to emulate and we'll see what we can
do.
Plus, also bear in mind, SQL might have the function you need, but you just
don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there's
a
ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
some conditional logic, there's the CASE statement instead of Excel's IF.
Remember to post some DDL, sample data, expected results etc.
Damien
First of all, there are few things you can't do in SQL
"ram4tech" wrote:
> Hi,
> I am trying to access an Excel function in a stored procedure. First of al
l,
> is it possible, if it is, can someone give me an example.
> Thank you.
> --
> Ram|||Hi Damien:
The looked in BOL and on the net, but didn't had much luck. I wasn't aware
that there might be some performance issues. The excel function I am plannin
g
on using is IRR().
Thank you.
--
Ram
"Damien" wrote:
> It may be possible with sp_OAMethod, but why do it to yourself? Even if y
ou
> can get to work without blowing up your computer, performance is going to
be
> rubbish!
> SQL has loads of functions, and you can roll your own, do you know about
> user-defined functions? They're great!
> Anyway, tell us which function you want to emulate and we'll see what we c
an
> do.
> Plus, also bear in mind, SQL might have the function you need, but you jus
t
> don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there'
s a
> ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
> some conditional logic, there's the CASE statement instead of Excel's IF.
> Remember to post some DDL, sample data, expected results etc.
> Damien
> First of all, there are few things you can't do in SQL
> "ram4tech" wrote:
>|||Hello Ram !
http://groups.google.de/group/micro...5d4e46703ec82cd
HTH, jens Suessmeyer.|||Right, well you'd need to know the formula that sits behind IRR to recreate
it, but I guess it boils down to addition, subtraction, maybe an average or
two? T-SQL can do all that, but Excel is better at sums, I'll give it that.
So maybe you should play to the strengths, ie SQL for holding data,
concurrent access, raw power, Excel for sums.
Have you considered linking in to your server, eg a pivot table or external
query?
"ram4tech" wrote:
> Hi Damien:
> The looked in BOL and on the net, but didn't had much luck. I wasn't aware
> that there might be some performance issues. The excel function I am plann
ing
> on using is IRR().
> Thank you.
> --
> Ram
>
> "Damien" wrote:
>|||Here is a T-SQL implementation of IRR I posted a while back.
It is probably less robust than the Excel version, but it may still
work for you:
http://groups.google.co.uk/groups?q...eam+kass+newton
Steve Kass
Drew University
ram4tech wrote:
>Hi Damien:
>The looked in BOL and on the net, but didn't had much luck. I wasn't aware
>that there might be some performance issues. The excel function I am planni
ng
>on using is IRR().
>Thank you.
>
Accessing Estimated Query Execution Plan (QEP) statisitics
Hi,
I have a question about estimated query execution plans that are
generated in QA of MSSQL.
If I point at an icon/physical operator in the estimated QEP, it shows
me
some statistics about the operator.
Is there a way to retrieve these statistics through a query, i.e., can
these statistics be available to the user?
Also, is there a way to generate these statistics on my own?
thanks in advance
-TC.T Chaudhary (replysoon_04@.yahoo.com) writes:
> I have a question about estimated query execution plans that are
> generated in QA of MSSQL.
> If I point at an icon/physical operator in the estimated QEP, it shows
> me
> some statistics about the operator.
> Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?
Yes. It's all done through SQL statements. Rather than saying the exact
statements to use(*), I will tell you how find out: Use the SQL Server
Profiler to see what commands that Query Analyzer submits to SQL Server
when you have enabled these options.
(*) Since to tell which they are, *I* would have to run Profiler.
> Also, is there a way to generate these statistics on my own?
I'm not sure that I understand the questions, but if I'm guessing right
the answer is: no, you can't.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"T Chaudhary" :
> Hi,
> I have a question about estimated query execution plans that are
> generated in QA of MSSQL.
> If I point at an icon/physical operator in the estimated QEP, it shows
> me
> some statistics about the operator.
> Is there a way to retrieve these statistics through a query, i.e., can
> these statistics be available to the user?
> Also, is there a way to generate these statistics on my own?
> thanks in advance
> -TC.
TC,
See SET SHOWPLAN_ALL IN BOL for getting the data you're asking about. To
get the results of your query *and* the plan, see SET STATISTICS PROFILE.
Craig
P.S. Playing with these for a while will give you some real appreciation for
QA! :)
Accessing domain user name as a report parameter
In our legacy reports (ASP.NET/Crystal Reports) we use the logged in user account to filter the report.
Basically you are restricted to seeing your 'own' data when running the report based upon your user ID.
I am looking for suggestions as how to restrict report results based on something like domain user ID in Reporting Services.
There are many cases (like in the sales profession) where users of the same report do not want other users to access their information.
Thanks for any suggestions.use the global variable User!UserID
be aware that you may not create subscriptions for a report that contains user-defined parameter values.
Basically you are restricted to seeing your 'own' data when running the report based upon your user ID.
I am looking for suggestions as how to restrict report results based on something like domain user ID in Reporting Services.
There are many cases (like in the sales profession) where users of the same report do not want other users to access their information.
Thanks for any suggestions.use the global variable User!UserID
be aware that you may not create subscriptions for a report that contains user-defined parameter values.
Accessing directly to a Full Index Catalog
Hi,
Suppose that I have a Full Text catalog that indexes 3 tables. I want to search from an asp page for some text in the tables. How can I perform this search directly in the catalog ? Right now I must perform 3 queries, one for each table, but then I must
store the parcial results and in the end order by rank the retrieved results. This doesn′t seem to be a good solution. Does any one know if I can do this with the ixsso object (Query and Util)?. Any ideas ?
Thanks in advance.
Bart.
Bartolomeu,
Unfortunately, direct access to the FT Catalog files is not supported in SQL
Server 2000. However it has been reported publicly by Microsoft that it will
be supported via a command line utility in SQL Server 2005 (Yukon).
For SQL Server 2000, you must rely on using the FTS predicates of CONTAINS*
or FREETEXT*.
Regards,
John
"Bartolomeu" <Bartolomeu@.discussions.microsoft.com> wrote in message
news:90467F8E-3CFD-430A-A155-4BA09806F7CB@.microsoft.com...
> Hi,
> Suppose that I have a Full Text catalog that indexes 3 tables. I want to
search from an asp page for some text in the tables. How can I perform this
search directly in the catalog ? Right now I must perform 3 queries, one for
each table, but then I must store the parcial results and in the end order
by rank the retrieved results. This doesnt seem to be a good solution.
Does any one know if I can do this with the ixsso object (Query and Util)?.
Any ideas ?
> Thanks in advance.
> Bart.
Suppose that I have a Full Text catalog that indexes 3 tables. I want to search from an asp page for some text in the tables. How can I perform this search directly in the catalog ? Right now I must perform 3 queries, one for each table, but then I must
store the parcial results and in the end order by rank the retrieved results. This doesn′t seem to be a good solution. Does any one know if I can do this with the ixsso object (Query and Util)?. Any ideas ?
Thanks in advance.
Bart.
Bartolomeu,
Unfortunately, direct access to the FT Catalog files is not supported in SQL
Server 2000. However it has been reported publicly by Microsoft that it will
be supported via a command line utility in SQL Server 2005 (Yukon).
For SQL Server 2000, you must rely on using the FTS predicates of CONTAINS*
or FREETEXT*.
Regards,
John
"Bartolomeu" <Bartolomeu@.discussions.microsoft.com> wrote in message
news:90467F8E-3CFD-430A-A155-4BA09806F7CB@.microsoft.com...
> Hi,
> Suppose that I have a Full Text catalog that indexes 3 tables. I want to
search from an asp page for some text in the tables. How can I perform this
search directly in the catalog ? Right now I must perform 3 queries, one for
each table, but then I must store the parcial results and in the end order
by rank the retrieved results. This doesnt seem to be a good solution.
Does any one know if I can do this with the ixsso object (Query and Util)?.
Any ideas ?
> Thanks in advance.
> Bart.
Accessing Different Servers
If I have 2 registered servers, what syntax allows me to access both servers
from the same query window? For example, if I say "use mydb1" and mydb1
resides on server1, I can execute sql against it.
I'd like to execute another statement after finishing with mydb1 that
accesses mydb1 that resides on server2. Any help with proper syntax?Look at the :connect command in SQLCMD mode in the query editor (assuming
this is SSMS).
The help topic which should give some help is "Editing SQLCMD scripts with
Query Editor"
> If I have 2 registered servers, what syntax allows me to access both
> servers from the same query window? For example, if I say "use mydb1"
> and mydb1 resides on server1, I can execute sql against it.
> I'd like to execute another statement after finishing with mydb1 that
> accesses mydb1 that resides on server2. Any help with proper syntax?
>|||i'm just trying to find syntax like "use mydb" that would take the name of
the server.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||can you point me a little better to the help article? i don't know what
SQLCMD mode is and don't know what SSMS is.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||Hi Scott
Bruce is assuming you are working with SQL Server 2005, since you didn't
state the version. SQLCMD is the text interface, replacing osql. SSMS is SQL
Server Management Studio, replacing both Query Analyzer and Enterprise
Manager. Please always state what version you are using at the beginning of
every request for help.
There is no equivalent to USE to connect to another server.
You can access tables on another server by setting up a linked server and
using a 4-part name to access the tables:
SELECT ...
FROM servername.dbname.object_owner.object
WHERE ...
You can read about linked servers in BOL (Books Online, in both SQL 2000 and
SQL 2005).
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
> can you point me a little better to the help article? i don't know what
> SQLCMD mode is and don't know what SSMS is.
>
> "Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
> news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
>
>|||I am using 2005 and I found the help topic, but would appreciate a little
extra info ...
1. Can I use SQLCMD commands within a SPROC?
2. Is it possible to detach/re-attach databases on different servers from 1
SPROC or SQL commands from a single query window?
Do you have any good books to recommend on SQLCMD?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi Scott
> Bruce is assuming you are working with SQL Server 2005, since you didn't
> state the version. SQLCMD is the text interface, replacing osql. SSMS is
> SQL Server Management Studio, replacing both Query Analyzer and Enterprise
> Manager. Please always state what version you are using at the beginning
> of every request for help.
> There is no equivalent to USE to connect to another server.
> You can access tables on another server by setting up a linked server and
> using a 4-part name to access the tables:
> SELECT ...
> FROM servername.dbname.object_owner.object
> WHERE ...
> You can read about linked servers in BOL (Books Online, in both SQL 2000
> and SQL 2005).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
>|||Scott
SQLCMD is an operating system utility, not a TSQL command, so the only way
to use it within a sproc is to use xp_cmdshell which allow you to invoke an
OS command.
You can run stored procedures or other commands on other servers if you have
set the other servers up as linked servers. As I already suggested, please
read about linked servers in the BOL.
While in SSMS, you can change the server you are connected to without using
linked servers. So you could run one command against server1, then click the
button to change your connection, and connect to server 2, where you could
run a different command without changing your query window. I'm sorry, I
don't have a SQL 2005 active right now to tell you where the button to
change connections is.
I'm not familiar with any of the SQL 2005 that are out already, but I
wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
best place to look.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
> I am using 2005 and I found the help topic, but would appreciate a little
> extra info ...
> 1. Can I use SQLCMD commands within a SPROC?
> 2. Is it possible to detach/re-attach databases on different servers from
> 1 SPROC or SQL commands from a single query window?
> Do you have any good books to recommend on SQLCMD?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||I get the idea. Do you think that SQLCMD is the only way to detach/atach
databases on different servers from 1 query window? I just though there must
be a way to do this without going into SQLCMD.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUVVM$xOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Scott
> SQLCMD is an operating system utility, not a TSQL command, so the only way
> to use it within a sproc is to use xp_cmdshell which allow you to invoke
> an OS command.
> You can run stored procedures or other commands on other servers if you
> have set the other servers up as linked servers. As I already suggested,
> please read about linked servers in the BOL.
> While in SSMS, you can change the server you are connected to without
> using linked servers. So you could run one command against server1, then
> click the button to change your connection, and connect to server 2, where
> you could run a different command without changing your query window. I'm
> sorry, I don't have a SQL 2005 active right now to tell you where the
> button to change connections is.
> I'm not familiar with any of the SQL 2005 that are out already, but I
> wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
> best place to look.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
>
>
from the same query window? For example, if I say "use mydb1" and mydb1
resides on server1, I can execute sql against it.
I'd like to execute another statement after finishing with mydb1 that
accesses mydb1 that resides on server2. Any help with proper syntax?Look at the :connect command in SQLCMD mode in the query editor (assuming
this is SSMS).
The help topic which should give some help is "Editing SQLCMD scripts with
Query Editor"
> If I have 2 registered servers, what syntax allows me to access both
> servers from the same query window? For example, if I say "use mydb1"
> and mydb1 resides on server1, I can execute sql against it.
> I'd like to execute another statement after finishing with mydb1 that
> accesses mydb1 that resides on server2. Any help with proper syntax?
>|||i'm just trying to find syntax like "use mydb" that would take the name of
the server.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||can you point me a little better to the help article? i don't know what
SQLCMD mode is and don't know what SSMS is.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||Hi Scott
Bruce is assuming you are working with SQL Server 2005, since you didn't
state the version. SQLCMD is the text interface, replacing osql. SSMS is SQL
Server Management Studio, replacing both Query Analyzer and Enterprise
Manager. Please always state what version you are using at the beginning of
every request for help.
There is no equivalent to USE to connect to another server.
You can access tables on another server by setting up a linked server and
using a 4-part name to access the tables:
SELECT ...
FROM servername.dbname.object_owner.object
WHERE ...
You can read about linked servers in BOL (Books Online, in both SQL 2000 and
SQL 2005).
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
> can you point me a little better to the help article? i don't know what
> SQLCMD mode is and don't know what SSMS is.
>
> "Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
> news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
>
>|||I am using 2005 and I found the help topic, but would appreciate a little
extra info ...
1. Can I use SQLCMD commands within a SPROC?
2. Is it possible to detach/re-attach databases on different servers from 1
SPROC or SQL commands from a single query window?
Do you have any good books to recommend on SQLCMD?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi Scott
> Bruce is assuming you are working with SQL Server 2005, since you didn't
> state the version. SQLCMD is the text interface, replacing osql. SSMS is
> SQL Server Management Studio, replacing both Query Analyzer and Enterprise
> Manager. Please always state what version you are using at the beginning
> of every request for help.
> There is no equivalent to USE to connect to another server.
> You can access tables on another server by setting up a linked server and
> using a 4-part name to access the tables:
> SELECT ...
> FROM servername.dbname.object_owner.object
> WHERE ...
> You can read about linked servers in BOL (Books Online, in both SQL 2000
> and SQL 2005).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
>|||Scott
SQLCMD is an operating system utility, not a TSQL command, so the only way
to use it within a sproc is to use xp_cmdshell which allow you to invoke an
OS command.
You can run stored procedures or other commands on other servers if you have
set the other servers up as linked servers. As I already suggested, please
read about linked servers in the BOL.
While in SSMS, you can change the server you are connected to without using
linked servers. So you could run one command against server1, then click the
button to change your connection, and connect to server 2, where you could
run a different command without changing your query window. I'm sorry, I
don't have a SQL 2005 active right now to tell you where the button to
change connections is.
I'm not familiar with any of the SQL 2005 that are out already, but I
wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
best place to look.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
> I am using 2005 and I found the help topic, but would appreciate a little
> extra info ...
> 1. Can I use SQLCMD commands within a SPROC?
> 2. Is it possible to detach/re-attach databases on different servers from
> 1 SPROC or SQL commands from a single query window?
> Do you have any good books to recommend on SQLCMD?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||I get the idea. Do you think that SQLCMD is the only way to detach/atach
databases on different servers from 1 query window? I just though there must
be a way to do this without going into SQLCMD.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUVVM$xOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Scott
> SQLCMD is an operating system utility, not a TSQL command, so the only way
> to use it within a sproc is to use xp_cmdshell which allow you to invoke
> an OS command.
> You can run stored procedures or other commands on other servers if you
> have set the other servers up as linked servers. As I already suggested,
> please read about linked servers in the BOL.
> While in SSMS, you can change the server you are connected to without
> using linked servers. So you could run one command against server1, then
> click the button to change your connection, and connect to server 2, where
> you could run a different command without changing your query window. I'm
> sorry, I don't have a SQL 2005 active right now to tell you where the
> button to change connections is.
> I'm not familiar with any of the SQL 2005 that are out already, but I
> wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
> best place to look.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
>
>
Accessing Different Databases on Same Server with Dot Notation.
This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C58C65.624CE800
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_000C_01C58C65.624CE800"
--=_NextPart_001_000C_01C58C65.624CE800
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on = another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on = a different server?
Thanks for any help,
Matt
--=_NextPart_001_000C_01C58C65.624CE800
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; = FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; = FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; = FONT-FAMILY: Helvetica, "Times New Roman"
}
Hello,
I'm using Query Analyzer and I'm trying to select from a = table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect = syntax near 'DB1.'.
This query worked on my local machine... Could it be a = set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000C_01C58C65.624CE800--
--=_NextPart_000_000B_01C58C65.624CE800
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <000601c58c86$e95b7ac0$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==
--=_NextPart_000_000B_01C58C65.624CE800--This is a multi-part message in MIME format.
--=_NextPart_000_0047_01C58C61.410F5F50
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0048_01C58C61.410F5F50"
--=_NextPart_001_0048_01C58C61.410F5F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue =on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0048_01C58C61.410F5F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" wrote in =message news:%23IGqqbIjFHA.=3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a =table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks = like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect =syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0048_01C58C61.410F5F50--
--=_NextPart_000_0047_01C58C61.410F5F50
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <004201c58c8b$29df4cd0$be0a050a@.newbridgeis.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0047_01C58C61.410F5F50--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C58C6B.0831D500
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_000E_01C58C6B.0831D500"
--=_NextPart_001_000E_01C58C6B.0831D500
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the =insight, I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000E_01C58C6B.0831D500
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
Thanks for the help Kevin,
I actually tried that with no luck whatsoever..... =Thanks for the insight, I appreciate the feedback.
Matt
"Kevin3NF" wrote in message news:%23r5pqsIjFHA.=3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" wrote in =message news:%23IGqqbIjFHA.=3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a =table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query =looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect =syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000E_01C58C6B.0831D500--
--=_NextPart_000_000D_01C58C6B.0831D500
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <000801c58c8c$8c800f40$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_000D_01C58C6B.0831D500--|||This is a multi-part message in MIME format.
--=_NextPart_000_0031_01C58C6C.45DE4B80
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0032_01C58C6C.45DE4B80"
--=_NextPart_001_0032_01C58C6C.45DE4B80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0032_01C58C6C.45DE4B80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
Any Other Suggestions?......
"Kevin3NF" wrote in message news:%23r5pqsIjFHA.=3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" wrote in =message news:%23IGqqbIjFHA.=3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a =table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query =looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect =syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0032_01C58C6C.45DE4B80--
--=_NextPart_000_0031_01C58C6C.45DE4B80
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <002c01c58c8d$cc2f81b0$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0031_01C58C6C.45DE4B80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0092_01C58C65.02A65D50
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0093_01C58C65.02A65D50"
--=_NextPart_001_0093_01C58C65.02A65D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankNone. I pasted your code into my QA and it parses correctly (as I =assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table =on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0093_01C58C65.02A65D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
None. I pasted your code into my QA and it parses correctly =(as I assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'DB1..Test1'.
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" wrote in =message news:OgBOO3IjFHA.1148=@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" wrote in message news:%23r5pqsIjFHA.=3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" wrote =in message news:%23IGqqbIjFHA.=3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from =a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query =looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: =Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
=Matt
--=_NextPart_001_0093_01C58C65.02A65D50--
--=_NextPart_000_0092_01C58C65.02A65D50
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <008d01c58c8e$eb764ad0$be0a050a@.newbridgeis.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0092_01C58C65.02A65D50--|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.|||Now you post a different error message than you posted earlier. "Invalid object name" simply states
that there is no table or view named "Test1" inside the DB1 database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>|||That was me, not the OP...;-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your questions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>> Now you post a different error message than you posted earlier. "Invalid object name" simply
>> states that there is no table or view named "Test1" inside the DB1 database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
>> BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it,
>> I got the expected:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'DB1..Test1'.
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your questions
>>
>> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
>> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Any Other Suggestions?......
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Try specifying the object owner:
>> Select *
>> FROM DB1.dbo.Test1
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your questions
>>
>> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
>> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> I'm using Query Analyzer and I'm trying to select from a table on another database...
>> i.e..I'm on DB2 in query analyzer and this is what my query looks like
>> Select *
>> FROM DB1..Test1
>> But I'm getting an error
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near 'DB1.'.
>> This query worked on my local machine... Could it be a set-up issue on a different server?
>> Thanks for any help,
>> Matt
>
--=_NextPart_000_000B_01C58C65.624CE800
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_000C_01C58C65.624CE800"
--=_NextPart_001_000C_01C58C65.624CE800
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on = another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on = a different server?
Thanks for any help,
Matt
--=_NextPart_001_000C_01C58C65.624CE800
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; = FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; = FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; = FONT-FAMILY: Helvetica, "Times New Roman"
}
Hello,
I'm using Query Analyzer and I'm trying to select from a = table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect = syntax near 'DB1.'.
This query worked on my local machine... Could it be a = set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000C_01C58C65.624CE800--
--=_NextPart_000_000B_01C58C65.624CE800
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <000601c58c86$e95b7ac0$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==
--=_NextPart_000_000B_01C58C65.624CE800--This is a multi-part message in MIME format.
--=_NextPart_000_0047_01C58C61.410F5F50
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0048_01C58C61.410F5F50"
--=_NextPart_001_0048_01C58C61.410F5F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue =on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0048_01C58C61.410F5F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn"
Hello,
I'm using Query Analyzer and I'm trying to select from a =table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks = like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect =syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0048_01C58C61.410F5F50--
--=_NextPart_000_0047_01C58C61.410F5F50
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <004201c58c8b$29df4cd0$be0a050a@.newbridgeis.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0047_01C58C61.410F5F50--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C58C6B.0831D500
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_000E_01C58C6B.0831D500"
--=_NextPart_001_000E_01C58C6B.0831D500
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the =insight, I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000E_01C58C6B.0831D500
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
Thanks for the help Kevin,
I actually tried that with no luck whatsoever..... =Thanks for the insight, I appreciate the feedback.
Matt
"Kevin3NF"
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn"
Hello,
I'm using Query Analyzer and I'm trying to select from a =table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query =looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect =syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000E_01C58C6B.0831D500--
--=_NextPart_000_000D_01C58C6B.0831D500
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <000801c58c8c$8c800f40$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_000D_01C58C6B.0831D500--|||This is a multi-part message in MIME format.
--=_NextPart_000_0031_01C58C6C.45DE4B80
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0032_01C58C6C.45DE4B80"
--=_NextPart_001_0032_01C58C6C.45DE4B80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0032_01C58C6C.45DE4B80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
Any Other Suggestions?......
"Kevin3NF"
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn"
Hello,
I'm using Query Analyzer and I'm trying to select from a =table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query =looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect =syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0032_01C58C6C.45DE4B80--
--=_NextPart_000_0031_01C58C6C.45DE4B80
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <002c01c58c8d$cc2f81b0$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0031_01C58C6C.45DE4B80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0092_01C58C65.02A65D50
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0093_01C58C65.02A65D50"
--=_NextPart_001_0093_01C58C65.02A65D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankNone. I pasted your code into my QA and it parses correctly (as I =assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table =on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0093_01C58C65.02A65D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
None. I pasted your code into my QA and it parses correctly =(as I assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'DB1..Test1'.
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn"
Any Other Suggestions?......
"Kevin3NF"
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn"
Hello,
I'm using Query Analyzer and I'm trying to select from =a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query =looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2Line 2: =Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it =be a set-up issue on a different server?
Thanks for any help,
=Matt
--=_NextPart_001_0093_01C58C65.02A65D50--
--=_NextPart_000_0092_01C58C65.02A65D50
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <008d01c58c8e$eb764ad0$be0a050a@.newbridgeis.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0092_01C58C65.02A65D50--|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.|||Now you post a different error message than you posted earlier. "Invalid object name" simply states
that there is no table or view named "Test1" inside the DB1 database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>|||That was me, not the OP...;-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your questions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>> Now you post a different error message than you posted earlier. "Invalid object name" simply
>> states that there is no table or view named "Test1" inside the DB1 database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
>> BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it,
>> I got the expected:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'DB1..Test1'.
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your questions
>>
>> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
>> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Any Other Suggestions?......
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Try specifying the object owner:
>> Select *
>> FROM DB1.dbo.Test1
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your questions
>>
>> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
>> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> I'm using Query Analyzer and I'm trying to select from a table on another database...
>> i.e..I'm on DB2 in query analyzer and this is what my query looks like
>> Select *
>> FROM DB1..Test1
>> But I'm getting an error
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near 'DB1.'.
>> This query worked on my local machine... Could it be a set-up issue on a different server?
>> Thanks for any help,
>> Matt
>
Subscribe to:
Posts (Atom)