|
Is it possible to query the table of a remote data server (MS SQL) in "query analyzer" without using a Linked Server? What are the items that I need to setup/know in order to do that?
For example:
Select
local.*,
Rmt.*
From
LocalServer.LocalDatabase.dbo.Table1 local,
RemoteServer.RemoteDatabase.dbo.Table2 Rmt
NOTE: Query analyzer current server is “Local”
-- modified at 11:52 Wednesday 13th December, 2006
|
|
|
|
|
Lookup OpenRowset or OpenDataSource in Sql Books online:
Here's and example
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
Using the GridView is like trying to explain to someone else how to move a third person's hands in order to tie your shoelaces for you.
-Chris Maunder
|
|
|
|
|
I spent 4 hours searching Google for this answer, you’re the man.
Thanks Andy, to your time and effort to answer my question.
-- modified at 20:57 Wednesday 13th December, 2006
|
|
|
|
|
If it is already a linked server it is a bit simplier.
select * from
[server].[database].[owner].[table]
In pratice this works out as
Select * from [OrderServer].[orderdb]..[orders]
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I use sql server 7.0
I have 2 tables A and B.Both tables have 200000-300000 records.B has a column named changedate which has an index.I do a query.First I use Query Analyzer(QA)
declare @p_begin datetime,@p_end datetime
select @p_begin='2006-10-01 00:00:00.000',@p_end='2006-10-31 23:59:59.000'
select * from A inner join B on A.fno=B.fno WHERE B.changedate between @p_begin and @p_end
It uses more than 2 minutes .If I write
select * from A inner join B on A.fno=B.fno WHERE B.changedate
between '2006-10-01 00:00:00.000' and '2006-10-31 23:59:59.000'
It uses only 6 seconds .
Then I create a stored procedure to test
create procedure myproc1
AS
declare @p_begin datetime,@p_end datetime
select @p_begin='2006-10-01 00:00:00.000',@p_end='2006-10-31 23:59:59.000'
select * from A inner join B on A.fno=B.fno WHERE B.changedate between @p_begin and @p_end
go
create procedure myproc2 @p_begin datetime,@p_end datetime
AS
select * from A inner join B on A.fno=B.fno WHERE B.changedate between @p_begin and @p_end
go
When I exec myproc1, I use more than 2 minutes.
when I exec myproc2
declare @p_begin datetime,@p_end datetime
select @p_begin='2006-10-01 00:00:00.000',@p_end='2006-10-31 23:59:59.000'
exec myproc2 @p_begin,@p_end
It use only 6 seconds.
What is the difference behind them?
If I don't want to use stored procedure and want to query directly in QA,Can I use variable to achieve good performance?
please explain for me,thank you
|
|
|
|
|
You really need to run up Sql Profiler and profile the two to see what the difference is, and what is really going on. Theoretically, there should be no difference if all things are equal, but I would take a look at what Profiler thinks is going on.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,I find a url to explaint it.But I don't understand very much.
http://www.microsoft.com/technet/technetmag/issues/2005/05/SQLQA/
You may see this and explain for me better,thanks
|
|
|
|
|
It is possible that on the second run the SQL Server will have cached much of the data in memory that it needed to pull off the disk the first time round. But I'll reiterate the other piece of advice. Have a look to see what the SQL Server is actually doing with these queries.
|
|
|
|
|
I have a three columns in which one column is auto generated by increment 1. if i delete one record from the table (example:4 record i deleted)it should be updated as 5th record as 4th record and so on?
|
|
|
|
|
Ravi Shankar43 wrote: I have a three columns in which one column is auto generated by increment 1. if i delete one record from the table (example:4 record i deleted)it should be updated as 5th record as 4th record and so on?
No. If you delete rows the number that was autogenerated for that row will not be reused.
|
|
|
|
|
You can reseed the indentity if you are trying to hide something, but it could mess up your db schema
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
how to cancatenate two columns of data table in one to display as one column
please help me
diya
|
|
|
|
|
diya_dev wrote: how to cancatenate two columns of data table in one to display as one column
You can concatenate string based columns (e.g. varchar) like this:
SELECT Column1 + Column2
FROM MyTable
|
|
|
|
|
<br />
SELECT Column1 + " " + Column2<br />
FROM MyTable<br />
If you want to be "Really Fancy"
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Frank Kerrigan wrote: SELECT Column1 + " " + Column2
FROM MyTable
If you want to be "Really Fancy"
Strictly speaking that will attempt to concatenate 3 columns, the middle column having a name consisting of a single space character. I think you meant to use an apostrophe:
SELECT Column1 + ' ' + Column2
FROM MyTable
|
|
|
|
|
you will also getting concate column Name like "IDRATE"
select (id + rate) as IDRATE from test
cheeer
Pavan Pareta
|
|
|
|
|
Hello,
When I try and execute some code in the if block it give an errror.
<br />
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'supportContract_selByClientID') <br />
CREATE PROCEDURE [dbo].[supportContract_selByClientID] @ClientID int AS SELECT * FROM SupportContracts WHERE ClientID = @ClientID<br />
Error message is:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@ClientID".
However, the strange thing is, that the line 1 and 2 execute without errors, but then you put them together I only get this error.
This works fine:
<br />
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'supportContract_selByClientID') <br />
ALTER TABLE TestTable ADD LastName nvarchar(50)<br />
Any ideas why this is,
Many thanks in advance,
Steve
-- modified at 2:02 Wednesday 13th December, 2006
|
|
|
|
|
I have a vague recollection of CREATE PROCEDURE having to be the only command in a batch.
My general advice is to check for the existance of the stored procedure, drop it, then create the new version. (Just like the examples in the SQL Server Books online)
|
|
|
|
|
i did a function to replace invalid characters. i used replace(@description, chr(180), N'')
but it is replacing number 3 with empty string as well.
why it does this? is there a setting for text comparison in SQL related to this problem?
I am Entry level person
|
|
|
|
|
Hi,
Is there a way to change the Subreport source, based on a parameter?
Example:
If MyParam == 1
Subreport = Clients
Else if MyParam == 2
Subreport = Employees
...
Thanks.
|
|
|
|
|
I have a pre-existing database and tables. I am trying to make the VB Code to copy one of the tables into a totally different table with a dynamic name. I want the program to copy the table every monday into another location Ex. BackupTable1,backuptable2,3,4,5 and so on.
any ideas?
Karma
|
|
|
|
|
Using vs2005, from the data explorer window, I can add a new connection and bring my data in. Using the datagrid view, I can attach a table to view my data.
I can also use sql statement to view the data table to the grid. Although I haven't tried both of them on the same application. It looks like it is fater to display the data to the grid from the data explorer than using sql statement.
What I mean, I mean that an application that use the database explorer to bring data in should be faster than one that use sql statement.
I tried both of them in different application, I have seen that it always take more time for the sql statement to run than fill the grid. The way I look at it, by using the databse explorer, when the application is loaded or run, the connection and the data tables are also initializeed. Therefore, it is faster, which is opposed to runing sql statement. When I use sql statement here, I mean I mean something like oledbconnection.
Let me know if I am right.
|
|
|
|
|
انا اريد ان اعمل select in disconnected mode for table have forign key
|
|
|
|
|
|
Colin Angus Mackay wrote: 
Same thing here :->
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
