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.

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.

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 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
>

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...
>

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...
>

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 Different Databases on Same Server with Dot Notation.

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
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
|||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
|||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
|||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
|||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...
>

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
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 d
ifferent server?
Thanks for any help,
MattBlankTry 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 questio
ns
"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 da
tabase...
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 d
ifferent server?
Thanks for any help,
Matt|||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:%23r
5pqsIjFHA.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 questio
ns
"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 da
tabase...
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 d
ifferent server?
Thanks for any help,
Matt|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.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 questio
ns
"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 da
tabase...
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 d
ifferent server?
Thanks for any help,
Matt|||BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). 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 questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.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 questio
ns
"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 da
tabase...
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 d
ifferent server?
Thanks for any help,
Matt|||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 obj
ect 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 assu
med). 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 questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.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 questio
ns
"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 da
tabase...
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 d
ifferent 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 quest
ions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

accessing DB2 table from sql server and asp.net page

Hi ...
I have successfully installed Db2 client configuration tools in sql server and then created a link server to db2 server . I am correctly selecting IBM db2 provider through provider name dropdown in create link server dialog box (general tab) . However , I am unable to fill the data for 3 requirements ...1)product name (Is this db2 database name ?) 2) Data source (What is this ) 3)provider string ...(I tried various combinations ...But didn't succeed)(The msdn has given example of creating linked server like <code>

This example creates a linked server named DB2 that uses the Microsoft OLE DB Provider for DB2.

EXEC sp_addlinkedserver @.server='DB2', @.srvproduct='Microsoft OLE DB Provider for DB2', @.catalog='DB2', @.provider='DB2OLEDB', @.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

</code>

Then in Security tab I gave remote login and password

However I am unable to get tables view for the db2 database in sql server . It is showing errors like initialisation ....Provider DB2OLEDB etc... Can anyone pl help .
Is there any other way ?

Bhat

Rather thanNetwork Address=XYZ I believe you need to substitute an IP address for theXYZ.
|||Ya that's obvious ... I had given Ip Address of DB2 server ...|||Resolved ,
Courtesy...follwing link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49610

Accessing dataset with custom code

Hi,

Need a little help on the code here. How can I import values from an RSS dataset? I will store them in a hash table and then call the function to check for dups. An example or pointer or article reference would be appreciated.

Thanks.

Since RSS is xml, you can use the XML data processing extension that comes with SQL Server 2005. This link has documentation on this feature:

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

Basically, you create a data source in report designer with type xml and specify the connection string to be the url of RSS feed. The query language it uses is similar to XPath. The doc for the query language is here:

http://msdn2.microsoft.com/en-US/library/ms365158.aspx

|||Here is an example of using the Xml Data Processing extension using this forum's RSS feed as the source.

Connection String:
http://forums.microsoft.com/MSDN/rss.aspx?ForumID=82&Mode=0&SiteID=1

Query:
<Query>
<ElementPath IgnoreNamespaces="True">rss{}/channel{title,link, description}/item{title, date, link, description, comments}</ElementPath>
</Query>

Ian|||

Thanks for all the help. This stuff is pretty much new to me. I'm mostly familiar with VBA, Classic VB, and T-SQL but we'll see where this leads.

Accessing Dataset values

I have 2 datasets. One is used to give the values to a parameter and the
second one is used to populated the main table in my report.
The first one has a code and a Name like:
Code Name
c cat
d dog
I would like to display â'catâ' in the main table, but I just have the code
â'câ'. Is there a way to do it ?Soan,
I'm not sure I fully understand your scenario.
If you're after the 'label' from the chosen parameter, you can get it
using;
=Parameters!paramname.Label
Chris
Soan wrote:
> I have 2 datasets. One is used to give the values to a parameter and
> the second one is used to populated the main table in my report.
> The first one has a code and a Name like:
> Code Name
> c cat
> d dog
> I would like to display â'catâ' in the main table, but I just have
> the code â'câ'. Is there a way to do it ?|||I am looking for a function that returns the second value of the dataset when
i give the first one.
Something like Parameters!paramname.Label the difference is that the
parameter does not have the valued assigned (I want to access different
values in the dataset).
> I'm not sure I fully understand your scenario.
> If you're after the 'label' from the chosen parameter, you can get it
> using;
> =Parameters!paramname.Label
> Chris
>
> Soan wrote:
> > I have 2 datasets. One is used to give the values to a parameter and
> > the second one is used to populated the main table in my report.
> >
> > The first one has a code and a Name like:
> > Code Name
> > c cat
> > d dog
> >
> > I would like to display â'catâ' in the main table, but I just have
> > the code â'câ'. Is there a way to do it ?
>|||Soan, I think you just need a join in the second dataset on the table
from the 1st dataset that gives you the description. You should make
the dataset contain all the data you want to show in the data region.
eg dataset 1 gives salesperson list;
Select SalesId, FirstName from tblSalesperson
this is used in the parameter. The value is SalesId, the label is
FirstName.
the second dataset would return the orders for that salesperson, but
you want the sales persons fullname on it from the salespersons table;
Select O.OrderId, O.SalesId, S.FullName, O.Date, O.Value
From tblOrder O
Join tblSalesperson S On O.SalesId = S.SalesId
Where O.SalesId = @.parametername
You can achieve this in the Graphical query designer, but I always use
the Generic designer so can't really explain it that well.
Hope that helps.
Chris
Soan wrote:
> I am looking for a function that returns the second value of the
> dataset when i give the first one.
> Something like Parameters!paramname.Label the difference is that the
> parameter does not have the valued assigned (I want to access
> different values in the dataset).
>
> > I'm not sure I fully understand your scenario.
> > If you're after the 'label' from the chosen parameter, you can get
> > it using;
> > =Parameters!paramname.Label
> >
> > Chris
> >
> >
> > Soan wrote:
> >
> > > I have 2 datasets. One is used to give the values to a parameter
> > > and the second one is used to populated the main table in my
> > > report.
> > >
> > > The first one has a code and a Name like:
> > > Code Name
> > > c cat
> > > d dog
> > >
> > > I would like to display â'catâ' in the main table, but I just
> > > have the code â'câ'. Is there a way to do it ?
> >
> >

accessing data from a web page

Hi all,
I need to create a web application.
I will probably need to web pages in this application. On the first
page, I would like to query a Sql table and list the rows returned by the
query in a List.
When user will click on any of the listed rows and click on Edit, I
would like to display the second page which will allow the user to edit the
selected record and save it back into the Sql table.
I know, I can easily create this app using ASP.Net. But the problem is
that the web pages will need to be accessed locally from laptops. IIS will
not be available to serve the pages. Each laptop will have Sql server
installed locally. So my app probably would have to use only
HTML,javascript/vbscript. It can't use ASP/ASP.Net.
I will appreciate it if you can provide me suggestions or links to
tutorials/samples that can help me with this application.
Thanks.Nikhil Patel wrote:
> Hi all,
> I need to create a web application.
> I will probably need to web pages in this application. On the first
> page, I would like to query a Sql table and list the rows returned by
> the query in a List.
> When user will click on any of the listed rows and click on Edit, I
> would like to display the second page which will allow the user to
> edit the selected record and save it back into the Sql table.
> I know, I can easily create this app using ASP.Net. But the
> problem is that the web pages will need to be accessed locally from
> laptops. IIS will not be available to serve the pages. Each laptop
> will have Sql server installed locally. So my app probably would have
> to use only HTML,javascript/vbscript. It can't use ASP/ASP.Net.
> I will appreciate it if you can provide me suggestions or links to
> tutorials/samples that can help me with this application.
> Thanks.
How about writing a fat client app to do this, instead of a web app.
David Gugick
Imceda Software
www.imceda.com|||Nikhil,
If you can't use IIS, then it kills the idea of creating a web solution.
Do you have access to any other tools such as VB/.Net or VC++/.Net? Are the
local laptops on the same domain as the web server? If so, you could add a
header for the website in IIS and build/serve the pages as you normally woul
d.
"Nikhil Patel" wrote:

> Hi all,
> I need to create a web application.
> I will probably need to web pages in this application. On the first
> page, I would like to query a Sql table and list the rows returned by the
> query in a List.
> When user will click on any of the listed rows and click on Edit, I
> would like to display the second page which will allow the user to edit th
e
> selected record and save it back into the Sql table.
> I know, I can easily create this app using ASP.Net. But the problem is
> that the web pages will need to be accessed locally from laptops. IIS will
> not be available to serve the pages. Each laptop will have Sql server
> installed locally. So my app probably would have to use only
> HTML,javascript/vbscript. It can't use ASP/ASP.Net.
> I will appreciate it if you can provide me suggestions or links to
> tutorials/samples that can help me with this application.
> Thanks.
>
>|||Thanks. But this application has to be a web app. because it is inteded to
be used in a browser of a third party app.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eLjje1gCFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Nikhil Patel wrote:
> How about writing a fat client app to do this, instead of a web app.
> --
> David Gugick
> Imceda Software
> www.imceda.com

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?
Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:

> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?
|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/de..._adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?
|||Hey thanks - its working great
"Jerry Spivey" wrote:

> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/de..._adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
>
>

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible
?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:

> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
to
> execute a query on SERVER_A referencing a table on SERVER_B is that possib
le?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/d... />
a_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:

> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/d...>
dda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
>
>

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:
> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> > If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> > to
> > execute a query on SERVER_A referencing a table on SERVER_B is that
> > possible?
> >
> > Example
> >
> > First Server: SERVER_A, Database: Nuts, Table: Goodness
> >
> > Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> >
> > From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> > where I need to get some data from SERVER_A , database 'Nuts', table
> > 'Goodness'.
> >
> > Is that possible? How can I do it? Would I use a linked server? If so can
> > anyone show me a good resource on how to set up a linked server?
>
>

Accessing columns without the coulmn names

Hi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- PradeepCan you explain how you want to use this information? There are probably ways to get you what you'd like, but there are at least as many really bad answers as really good ones.

-PatP|||Pat, I need to get the value of columns 1 and 2 from the table EMP, while not being bothered as to the names of the columns.|||I'd suggest something like:SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE 'table1' = c.TABLE_NAME
AND 1 = c.ORDINAL_POSITIONSee the BOL regarding INFORMATION_SCHEMA.COLUMNS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_87w3.asp) for more details.

-PatP|||Thanks a million Pat, this is what I was looking for.
Are you a SQL Server DBA?

cheers/- Pradeep|||Heck, I've been called even worse than than SQL DBA!

I answer to just about anything from "Hey you" to "Data Modeler", and everything in between. I tend to refer to myself as a Resident Curmudgeon, which seems about as good a description as any to me!

On a (very slightly) more serious note, yes, SQL DBA is one of the hats that I wear nearly every day.

-PatP|||SQL DataBase Analist|||SQL DataBase AnalistI'm a what-ist ?!?!

-PatP|||i think i can translate ...

ahem.

NEH-HERD.

Scott Davis
"If you are not part of the solution, you are probably 1/5 of the suspension" RDJabarov.|||Now Scott, what the heck does that mean? And when are you planning to have your Margarita? At my funeral?

accessing blank values in a table

Hi,
I am trying to format a column of varchar which has decimal values..Since it
is varchar I was not able to format it directlyu, so had to mulitply and
divide each value by 100 and then apply formatting..Not all the rows of the
table have data, some are blank..and these show up as "#Error" on the
report.I try not to do any operations on them by using "Isnothing" or
comparing each value with "" in the IIf statment..But neither works...Can
someone tell me how I can access the blanks in the table...
ThanksTo see if the column is empty in the IIF statement, use the following syntax
IIF ("" & MyColumn <> "", the rest ...)
It takes an empty string and concatenates the column value to the empty
string, if the column is empty, it results in an empty string.
"Dolly" <Dolly@.discussions.microsoft.com> wrote in message
news:FD0B22DD-BA90-4B2C-9495-A5E783B0467B@.microsoft.com...
> Hi,
> I am trying to format a column of varchar which has decimal values..Since
it
> is varchar I was not able to format it directlyu, so had to mulitply and
> divide each value by 100 and then apply formatting..Not all the rows of
the
> table have data, some are blank..and these show up as "#Error" on the
> report.I try not to do any operations on them by using "Isnothing" or
> comparing each value with "" in the IIf statment..But neither works...Can
> someone tell me how I can access the blanks in the table...
> Thanks

accessing another database in a stored procedure

hi

i have stored procedure and i need to access another database in my stored procedure

I'm going to query a table which is located in another database

as you know it is impossible to use the USE database keyword in stored procedures

so what should I do?

thanks.

create myProc
as

select someStuff from otherDb.dbo.otherTable
.....

just qualify the table in the other db fully with the databasename.

/Kenneth

|||Not to mention permissions...the user executing the stored procedure should have permissions to the other database|||Hi

You specify a different database on the same sql server within a stored procedure using the format:
[databasename].dbo.TableName

for example

select [Users].dbo.Addresses

Accessing a remote SQL Server

Currently, I access a database and table that is not the current
database using Admin..CASES
INSERT INTO Admin..CASES
(CASE_CODE,CASE_NAME,CASE_CLASS_PERIOD_BEG_DATE,CA SE_CLASS_PERIOD_END_DATE,
CASE_DISTRIBUTION_DATE,CASE_URL) SELECT M.MailCode AS
CaseCode,C.CaseName, C.ClassPeriodBegDate, C.ClassPeriodEndDate,
C.DistributionDate, M.WebsiteAddr FROM CaseList C INNER JOIN
MailCode M ON M.CaseRef = C.CaseRef WHERE Admin='Gilardi & Co., LLC'
Admin is the DB and CASES is the table.
I now have a situation where Admin is on a remote Server at the
IP of 192.168.100.5.
What syntax should I use for the above query?
Todd
If you are able to register the remote server as a 'Linked Server', it will
be relatively simple. See Books Online for sp_AddLinkedServer.
Then, using a 'four-part' object name, with the Server name first (you are
currently using a three-part object name), try something like this:
INSERT INTO MyServer.Admin..CASES
etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Todd Cary" <todd@.aristesoftware.com> wrote in message
news:45257B52.9040208@.aristesoftware.com...
> Currently, I access a database and table that is not the current database
> using Admin..CASES
> INSERT INTO Admin..CASES
> (CASE_CODE,CASE_NAME,CASE_CLASS_PERIOD_BEG_DATE,CA SE_CLASS_PERIOD_END_DATE,
> CASE_DISTRIBUTION_DATE,CASE_URL) SELECT M.MailCode AS CaseCode,C.CaseName,
> C.ClassPeriodBegDate, C.ClassPeriodEndDate, C.DistributionDate,
> M.WebsiteAddr FROM CaseList C INNER JOIN MailCode M ON M.CaseRef =
> C.CaseRef WHERE Admin='Gilardi & Co., LLC'
> Admin is the DB and CASES is the table.
> I now have a situation where Admin is on a remote Server at the IP of
> 192.168.100.5.
> What syntax should I use for the above query?
> Todd
|||Arnie Rowland wrote:
> If you are able to register the remote server as a 'Linked Server', it will
> be relatively simple. See Books Online for sp_AddLinkedServer.
> Then, using a 'four-part' object name, with the Server name first (you are
> currently using a three-part object name), try something like this:
> INSERT INTO MyServer.Admin..CASES
> etc.
>
When I bring up the Registered SQL Server Properties in
Enterprise manager, I am not sure how to "Register" the server;
what to put in the Server edit box. If I put the IP in the
Server edit box, it does create an entry.
The docs speak of @.server= and @.datasrc=
Todd
|||Arnie Rowland wrote:
> If you are able to register the remote server as a 'Linked Server', it will
> be relatively simple. See Books Online for sp_AddLinkedServer.
> Then, using a 'four-part' object name, with the Server name first (you are
> currently using a three-part object name), try something like this:
> INSERT INTO MyServer.Admin..CASES
> etc.
>
I have tried
sp_AddLinkedServer @.server='GilardiDEV',
@.srvproduct='SQL Server',
@.datasrc='192.168.100.5'
But I get the error
"You must specify a provider name with this set of properties."
yet the docs say that for "SQL Server", a provider is not require.
Hmmmm....
Todd
|||Not sure about the docs but you need to add
@.provider='SQLOLEDB'
to create the linked server.
However...the bigger issue is that it looks like you have
linked server named ADMIN already created. So wouldn't your
question really be how do I change the linked server ADMIN
to point to a new IP address?
You can change the data source through Enterprise Manager
for the linked server. It will update the system tables
(sysservers) where the information is stored.
-Sue
On Thu, 05 Oct 2006 17:53:25 -0700, Todd Cary
<todd@.aristesoftware.com> wrote:

>Arnie Rowland wrote:
>I have tried
>sp_AddLinkedServer @.server='GilardiDEV',
>@.srvproduct='SQL Server',
>@.datasrc='192.168.100.5'
>But I get the error
>"You must specify a provider name with this set of properties."
>yet the docs say that for "SQL Server", a provider is not require.
>Hmmmm....
>Todd

Accessing a remote SQL Server

Currently, I access a database and table that is not the current
database using Admin..CASES
INSERT INTO Admin..CASES
(CASE_CODE,CASE_NAME,CASE_CLASS_PERIOD_B
EG_DATE,CASE_CLASS_PERIOD_END_DATE,
CASE_DISTRIBUTION_DATE,CASE_URL) SELECT M.MailCode AS
CaseCode,C.CaseName, C.ClassPeriodBegDate, C.ClassPeriodEndDate,
C.DistributionDate, M.WebsiteAddr FROM CaseList C INNER JOIN
MailCode M ON M.CaseRef = C.CaseRef WHERE Admin='Gilardi & Co., LLC'
Admin is the DB and CASES is the table.
I now have a situation where Admin is on a remote Server at the
IP of 192.168.100.5.
What syntax should I use for the above query?
ToddIf you are able to register the remote server as a 'Linked Server', it will
be relatively simple. See Books Online for sp_AddLinkedServer.
Then, using a 'four-part' object name, with the Server name first (you are
currently using a three-part object name), try something like this:
INSERT INTO MyServer.Admin..CASES
etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Todd Cary" <todd@.aristesoftware.com> wrote in message
news:45257B52.9040208@.aristesoftware.com...
> Currently, I access a database and table that is not the current database
> using Admin..CASES
> INSERT INTO Admin..CASES
> (CASE_CODE,CASE_NAME,CASE_CLASS_PERIOD_B
EG_DATE,CASE_CLASS_PERIOD_END_DATE
,
> CASE_DISTRIBUTION_DATE,CASE_URL) SELECT M.MailCode AS CaseCode,C.CaseName,
> C.ClassPeriodBegDate, C.ClassPeriodEndDate, C.DistributionDate,
> M.WebsiteAddr FROM CaseList C INNER JOIN MailCode M ON M.CaseRef =
> C.CaseRef WHERE Admin='Gilardi & Co., LLC'
> Admin is the DB and CASES is the table.
> I now have a situation where Admin is on a remote Server at the IP of
> 192.168.100.5.
> What syntax should I use for the above query?
> Todd|||Arnie Rowland wrote:
> If you are able to register the remote server as a 'Linked Server', it wil
l
> be relatively simple. See Books Online for sp_AddLinkedServer.
> Then, using a 'four-part' object name, with the Server name first (you are
> currently using a three-part object name), try something like this:
> INSERT INTO MyServer.Admin..CASES
> etc.
>
When I bring up the Registered SQL Server Properties in
Enterprise manager, I am not sure how to "Register" the server;
what to put in the Server edit box. If I put the IP in the
Server edit box, it does create an entry.
The docs speak of @.server= and @.datasrc=
Todd|||Arnie Rowland wrote:
> If you are able to register the remote server as a 'Linked Server', it wil
l
> be relatively simple. See Books Online for sp_AddLinkedServer.
> Then, using a 'four-part' object name, with the Server name first (you are
> currently using a three-part object name), try something like this:
> INSERT INTO MyServer.Admin..CASES
> etc.
>
I have tried
sp_AddLinkedServer @.server='GilardiDEV',
@.srvproduct='SQL Server',
@.datasrc='192.168.100.5'
But I get the error
"You must specify a provider name with this set of properties."
yet the docs say that for "SQL Server", a provider is not require.
Hmmmm....
Todd|||Not sure about the docs but you need to add
@.provider='SQLOLEDB'
to create the linked server.
However...the bigger issue is that it looks like you have
linked server named ADMIN already created. So wouldn't your
question really be how do I change the linked server ADMIN
to point to a new IP address?
You can change the data source through Enterprise Manager
for the linked server. It will update the system tables
(sysservers) where the information is stored.
-Sue
On Thu, 05 Oct 2006 17:53:25 -0700, Todd Cary
<todd@.aristesoftware.com> wrote:

>Arnie Rowland wrote:
>I have tried
>sp_AddLinkedServer @.server='GilardiDEV',
>@.srvproduct='SQL Server',
>@.datasrc='192.168.100.5'
>But I get the error
>"You must specify a provider name with this set of properties."
>yet the docs say that for "SQL Server", a provider is not require.
>Hmmmm....
>Todd