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

Access Update Query Problem

Are you comparing apples to apples? In your VBA code, are you
executing the same join syntax that you are trying to excute through
the Access GUI. I have a suspicion that you are not, and the reason
the query is bombing out on you is that the join is forcing your
workstation to bring over all of the SQL Server data and working on the
update data client side. However, without code to examine, no freaking
clue :)
StuOn 7/28/2005 5:13 PM, Stu wrote:
> Are you comparing apples to apples? In your VBA code, are you
> executing the same join syntax that you are trying to excute through
> the Access GUI. I have a suspicion that you are not, and the reason
> the query is bombing out on you is that the join is forcing your
> workstation to bring over all of the SQL Server data and working on the
> update data client side. However, without code to examine, no freaking
> clue :)
> Stu
>
Access Query def:
UPDATE dbo_Order_Line_Invoice INNER JOIN tblOrderTypeValues ON
(dbo_Order_Line_Invoice.Cono = tblOrderTypeValues.cono) AND
(dbo_Order_Line_Invoice.LineNum = tblOrderTypeValues.lineno) AND
(dbo_Order_Line_Invoice.OrderNumber = tblOrderTypeValues.OrderNum) SET
dbo_Order_Line_Invoice.SXUser10 = tblOrderTypeValues.ordertype;
dbo_Order_Line_Invoice is the SQL server table
VBA code that works:
Sub UpdateOrdertype()
Dim rsOrderType As Recordset
Dim strSQL As String
Dim intCounter As Long
Set rsOrderType = CurrentDb.OpenRecordset("tblOrderTypeValues")
Do While Not rsOrderType.EOF
intCounter = intCounter + 1
Debug.Print intCounter: DoEvents
strSQL = "UPDATE dbo_Order_Line_Invoice SET
dbo_Order_Line_Invoice.SxUser10 = '" & rsOrderType.OrderType &
"' WHERE (dbo_Order_Line_Invoice.Cono = 1) AND
(dbo_Order_Line_Invoice.OrderNumber = '" & rsOrderType.OrderNum
& "' ) AND (dbo_Order_Line_Invoice.LineNum =
" & rsOrderType.Lineno & ");"
CurrentDb.Execute (strSQL)
rsOrderType.MoveNext
Loop
rsOrderType.Close
MsgBox "Done updating ordertype."
End Sub

Access to SQL Server 7 syntax help

This runs in Access, but SQL Server 7 complains that BETWEEN is unrecognized. Can anyone help me? thanks

SELECT yearId, IIf(Date() BETWEEN [qrtOneStart] AND [qrtOneEnd],1, IIf(Date() BETWEEN [qrtTwoStart] AND [qrtTwoEnd], 2, IIf(Date() BETWEEN [qrtThreeStart] AND [qrtThreeEnd], 3, 4))) AS CurrentQrt, yearName
FROM tblYearSELECT
yearId,
CASE
WHEN GETDATE() BETWEEN qrtOneStart AND qrtOneEnd THEN 1
WHEN GETDATE() BETWEEN qrtTwoStart AND qrtTwoEnd THEN 2
ELSE 0
END AS Quarter
FROM
tblYear

--SQL Server does not support IIF. That's an Access-only command.|||I'd like to address something since someone else brought it up.

I do BETWEEN @.date(s) all the time. However, if it is a datetime field, you have to do it like this:

BETWEEN '05/07/2004 0:00' AND '05/07/2004 23:59'

Right? You have to specify the times if you are doing between on a datetime field. As far as I know, there isn't just a Date datatype, correct? Unless you stored the value as varchar(10) i.e. 05/07/2004.

So, what I've resolved to doing is CONVERT'ng the datetime to just the date, and excluding the time. Does this sound proper? Just Curious...|||Using Convert() to strip the time from a DATETIME is easy, but it causes awful performance problems. Using BETWEEN is usually much better if an index exists, and slightly better even if no index exists.

The reason for this is that BETWEEN can "ride" an index, zeroing in quickly on the rows of interest. Using Convert() forces SQL Server to examine every row in the table (which it would if there were no index), and also to pass every date through the Convert() function (which is pure overhead).

-PatP|||I understand what you are saying for sure, but let's say I've got a dateteime field, and I am running a DTS package daily to get...say daily sales. I use GETDATE to figure out what the date is. So I HAVE to use CONVERT() to strip out the time because I'm not using a BETWEEN statement if I'm using CONVERT(). Right?

UNLESS! (and I'm sure you, pat, will know this one) I can concatenate(sp?) the 0:00 and 23:59 to the GETDATE() function. LIke:

TodaysDate = GETDATE()& 0:00 ??|||Something like:DECLARE @.dBegin DATETIME
, @.dEnd DATETIME
, @.dNow DATETIME

SELECT @.dNow = GetDate()

SELECT @.dBegin = Convert(CHAR(10), @.dNow, 121)
, @.dEnd = Convert(CHAR(10), @.dNow, 121) + ' 23:59:59.997'

SELECT foo, bar, dit, dash
FROM dbo.morse
WHERE date_processed BETWEEN @.dBegin AND @.eEnd...should probably do what you want.

-PatP|||but...why do you use SELECT @.Foo = Bar? I'm in the habit of using SET @.Foo = Bar. What gives?|||I'm a crusty old phart. The ability to use SET is new, and I'm not always good about using it, especially because SELECT is really more convenient in many ways (and old habits die hard).

I used a list of arbitrary column names to keep the SELECT * "police" at bay. While I have no problem with using SELECT * in an example, there are folks here on the forum that get really hyper about it. ;)

Just pretend that I used SELECT * to reduce the confusion, and you'll be just fine! I agree that you shouldn't use SELECT * in production, but I also think that it helps keep things clearer in a code sample like this.

-PatP

Access Linked Server system function

Could anyone shed some light on the syntax of accessing system function on a linked server?

I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.

e.g.

select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases

I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases

which does not work.

Thanks.

You cannot call a function directly from a linked server using the four part naming convention. You have to use a stored procedure to get the results.

Access Format Equivalent

Hello:
What would the equivalent syntax be to format the
following in SQL as can be done in Access.
Format("000000",employee) to return 012345 from 12345
CREATE TABLE #Temp
(
Employee CHAR(6)
)
INSERT INTO #Temp VALUES ('1')
INSERT INTO #Temp VALUES ('12')
INSERT INTO #Temp VALUES ('123')
INSERT INTO #Temp VALUES ('1234')
INSERT INTO #Temp VALUES ('12345')
SELECT * FROM #Temp
SELECT REVERSE(LEFT(LTRIM(REVERSE('000000'+ Employee)),6))
FROM #Temp
Rohtash Kapoor
http://www.sqlmantra.com
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:1c02d01c45186$9f7810d0$a001280a@.phx.gbl...
> Hello:
> What would the equivalent syntax be to format the
> following in SQL as can be done in Access.
> Format("000000",employee) to return 012345 from 12345
>
|||Thanks for the reply, but I think I found a simpler way.
select right('000000' + convert(varchar,
employee_number), 6)

>--Original Message--
>CREATE TABLE #Temp
>(
> Employee CHAR(6)
>)
>INSERT INTO #Temp VALUES ('1')
>INSERT INTO #Temp VALUES ('12')
>INSERT INTO #Temp VALUES ('123')
>INSERT INTO #Temp VALUES ('1234')
>INSERT INTO #Temp VALUES ('12345')
>SELECT * FROM #Temp
>SELECT REVERSE(LEFT(LTRIM(REVERSE('000000'+
Employee)),6))
>FROM #Temp
>--
>Rohtash Kapoor
>http://www.sqlmantra.com
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c02d01c45186$9f7810d0$a001280a@.phx.gbl...
>
>.
>

Access Form/Combo Box not liking sql syntax: Need sql server syntax

This query is supposed to call a form 'f_test' combo box 'combo2'. However,
I have found that the syntax is used for access sql and not sql server 2000.
(The combo box holds a value that will return in f_test subform a resultset
according to what is selected in the combo box, f_view1 is a query view
created that is returns to the subform records according to Area column).
Please hellp me get the syntax right. If you have any suggestions on books
for reference that would be wonderful. Thanks, Mitchell
PARAMETERS [Forms]![f_test]![Combo2] Text ( 255 );
SELECT f_view1.Area
FROM f_view1
WHERE (f_view1.Area Like [Forms]![f_test]![Combo2])
Hi Mitchell
"Mitchell_Collen" wrote:

> This query is supposed to call a form 'f_test' combo box 'combo2'. However,
> I have found that the syntax is used for access sql and not sql server 2000.
> (The combo box holds a value that will return in f_test subform a resultset
> according to what is selected in the combo box, f_view1 is a query view
> created that is returns to the subform records according to Area column).
> Please hellp me get the syntax right. If you have any suggestions on books
> for reference that would be wonderful. Thanks, Mitchell
> PARAMETERS [Forms]![f_test]![Combo2] Text ( 255 );
> SELECT f_view1.Area
> FROM f_view1
> WHERE (f_view1.Area Like [Forms]![f_test]![Combo2])
>
The access newsgroup may be a better place to get an answer to this!
John
|||John Bell wrote:
>Hi Mitchell
>[quoted text clipped - 8 lines]
>The access newsgroup may be a better place to get an answer to this!
>John
I will try them. Thanks.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200704/1

Access Form/Combo Box not liking sql syntax: Need sql server syntax

This query is supposed to call a form 'f_test' combo box 'combo2'. However,
I have found that the syntax is used for access sql and not sql server 2000.
(The combo box holds a value that will return in f_test subform a resultset
according to what is selected in the combo box, f_view1 is a query view
created that is returns to the subform records according to Area column).
Please hellp me get the syntax right. If you have any suggestions on books
for reference that would be wonderful. Thanks, Mitchell
PARAMETERS [Forms]![f_test]![Combo2] Text ( 255 );
SELECT f_view1.Area
FROM f_view1
WHERE (f_view1.Area Like [Forms]![f_test]![Combo2])Hi Mitchell
"Mitchell_Collen" wrote:
> This query is supposed to call a form 'f_test' combo box 'combo2'. However,
> I have found that the syntax is used for access sql and not sql server 2000.
> (The combo box holds a value that will return in f_test subform a resultset
> according to what is selected in the combo box, f_view1 is a query view
> created that is returns to the subform records according to Area column).
> Please hellp me get the syntax right. If you have any suggestions on books
> for reference that would be wonderful. Thanks, Mitchell
> PARAMETERS [Forms]![f_test]![Combo2] Text ( 255 );
> SELECT f_view1.Area
> FROM f_view1
> WHERE (f_view1.Area Like [Forms]![f_test]![Combo2])
>
The access newsgroup may be a better place to get an answer to this!
John|||John Bell wrote:
>Hi Mitchell
>> This query is supposed to call a form 'f_test' combo box 'combo2'. However,
>> I have found that the syntax is used for access sql and not sql server 2000.
>[quoted text clipped - 8 lines]
>> FROM f_view1
>> WHERE (f_view1.Area Like [Forms]![f_test]![Combo2])
>The access newsgroup may be a better place to get an answer to this!
>John
I will try them. Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1