I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
Thanks and sorry if it's too remedial a question.you will need to set up a linked server. there is a great product documentation tool called Books Online under Start\Programs\Micorsoft SQL Server. Use the search feature for "linked server". You can then reference your the linked server using openquery or you can create a view that created with openquery. again see books online about openquery and views.|||You can use OpenDataSource. Check that from BOL.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
Query about Ansi-Syntax
I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.
Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?
2) If I write a query (shown below), it tries to join table A and B returning large number of rows.
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'
Why does it try to join the table B with A though there is no join specified.As far as the second query is concerned this is the same as writing
Select A.Col1, A.Col2
from A join B
where A.Col3 = 'xyz'
Basically, don't put the table in the from clause if you are not going to use it because it makes the query REALLY inefficient. This is a cross join and will probably give you heaps of extra records.|||actually, ejustuss, your query generates an error
if we want a cross join, we have to say CROSS JOIN, not just JOIN
and yes, there are many situations where we want to join one table to another, and yet select columns only from one|||This old school syntax can cause a lot of heartburn when you get into multiple outer joins. I do not think MS SQL 2K supports *=
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'|||I do not think MS SQL 2K supports *=yes, it (still) does
create table Oldschool1
( id tinyint not null primary key identity
, foo varchar(9)
)
insert into Oldschool1 (foo) values ('Curly')
insert into Oldschool1 (foo) values ('Larry')
insert into Oldschool1 (foo) values ('Moe')
insert into Oldschool1 (foo) values ('Shemp')
insert into Oldschool1 (foo) values ('Joe')
insert into Oldschool1 (foo) values ('Curly Joe')
create table Oldschool2
( id tinyint not null primary key identity
, bar varchar(9)
)
insert into Oldschool2 (bar) values ('Curly')
insert into Oldschool2 (bar) values ('Larry')
insert into Oldschool2 (bar) values ('Moe')
select t1.id,t1.foo,t2.id,t2,bar
from Oldschool1 as t1
, Oldschool2 as t2
where t1.foo *= t2.bar
order by 1 :)|||As far as your first question,
The difference between ANSI (inner join) syntax and non-ANSI is that the optimizer will not try to derive joins that are ANSI compliant. That's because you've explicitly defined the joins. The optimizer will have to derive non-ANSI compliant joins. In many cases the query plans will be the same. However there's a greater risk the optimizer won't use the optimal plan if it has to derive the joins.
There's probably some people here that can explain this a lot better than me. Hope this helps.|||As far as your second question,
The results you're getting from that query are called a cartesian product. Since a join hasn't been specified it will match each record from on table to each record from the other. Thus if you have two tables with 100 rows each 10,000 rows will be returned.|||What I was trying to say was that in example 2 of the original post the join was implicit. Sorry about the error in syntax. Where would you want a query of the form
Select A.Col1, A.Col2
from A cross join B
where A.Col3 = 'xyz'
?|||where would you want a cross join?
1. to generate test data, e.g. a large range of dates from integers
2. with a left outer join to find missing many-to-many rows
3. to join a one-row table with application constants such as today's interest rate|||I was actually refering to the query as originally put with no reference to any column in the second table AT ALL. The generation of test data seems like a legitimate use though the other 2 surely require a reference to a column from the 2nd table in the the query somewhere even if only in the join condition.
Its just that I have seen queries with additional tables in the from clause which were unnecessary and unused and actually increased the amount of time taken to run the query substanitially.|||oh, that question
okay, well, the join would be used to ensure existence of a related row, even if you didn't need to actually return any data from the second table
for example, assume car owners are in one table, and parking tickets are in another, you might say "give me the names of every owner who had a parking ticket" and not return any row from the parking ticket table at all|||Well in this case would you not say something along the lines of
select car.owner
from car,ticket
where car.owner = ticket.reciever
?
my point being that you are actually referencing a column from the ticket table in the where clause (or join condition depending on how you write the query) - which is not what is happening in the original example.|||...which is not what is happening in the original example.that's right, it's a cross join
Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?
2) If I write a query (shown below), it tries to join table A and B returning large number of rows.
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'
Why does it try to join the table B with A though there is no join specified.As far as the second query is concerned this is the same as writing
Select A.Col1, A.Col2
from A join B
where A.Col3 = 'xyz'
Basically, don't put the table in the from clause if you are not going to use it because it makes the query REALLY inefficient. This is a cross join and will probably give you heaps of extra records.|||actually, ejustuss, your query generates an error
if we want a cross join, we have to say CROSS JOIN, not just JOIN
and yes, there are many situations where we want to join one table to another, and yet select columns only from one|||This old school syntax can cause a lot of heartburn when you get into multiple outer joins. I do not think MS SQL 2K supports *=
Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'|||I do not think MS SQL 2K supports *=yes, it (still) does
create table Oldschool1
( id tinyint not null primary key identity
, foo varchar(9)
)
insert into Oldschool1 (foo) values ('Curly')
insert into Oldschool1 (foo) values ('Larry')
insert into Oldschool1 (foo) values ('Moe')
insert into Oldschool1 (foo) values ('Shemp')
insert into Oldschool1 (foo) values ('Joe')
insert into Oldschool1 (foo) values ('Curly Joe')
create table Oldschool2
( id tinyint not null primary key identity
, bar varchar(9)
)
insert into Oldschool2 (bar) values ('Curly')
insert into Oldschool2 (bar) values ('Larry')
insert into Oldschool2 (bar) values ('Moe')
select t1.id,t1.foo,t2.id,t2,bar
from Oldschool1 as t1
, Oldschool2 as t2
where t1.foo *= t2.bar
order by 1 :)|||As far as your first question,
The difference between ANSI (inner join) syntax and non-ANSI is that the optimizer will not try to derive joins that are ANSI compliant. That's because you've explicitly defined the joins. The optimizer will have to derive non-ANSI compliant joins. In many cases the query plans will be the same. However there's a greater risk the optimizer won't use the optimal plan if it has to derive the joins.
There's probably some people here that can explain this a lot better than me. Hope this helps.|||As far as your second question,
The results you're getting from that query are called a cartesian product. Since a join hasn't been specified it will match each record from on table to each record from the other. Thus if you have two tables with 100 rows each 10,000 rows will be returned.|||What I was trying to say was that in example 2 of the original post the join was implicit. Sorry about the error in syntax. Where would you want a query of the form
Select A.Col1, A.Col2
from A cross join B
where A.Col3 = 'xyz'
?|||where would you want a cross join?
1. to generate test data, e.g. a large range of dates from integers
2. with a left outer join to find missing many-to-many rows
3. to join a one-row table with application constants such as today's interest rate|||I was actually refering to the query as originally put with no reference to any column in the second table AT ALL. The generation of test data seems like a legitimate use though the other 2 surely require a reference to a column from the 2nd table in the the query somewhere even if only in the join condition.
Its just that I have seen queries with additional tables in the from clause which were unnecessary and unused and actually increased the amount of time taken to run the query substanitially.|||oh, that question
okay, well, the join would be used to ensure existence of a related row, even if you didn't need to actually return any data from the second table
for example, assume car owners are in one table, and parking tickets are in another, you might say "give me the names of every owner who had a parking ticket" and not return any row from the parking ticket table at all|||Well in this case would you not say something along the lines of
select car.owner
from car,ticket
where car.owner = ticket.reciever
?
my point being that you are actually referencing a column from the ticket table in the where clause (or join condition depending on how you write the query) - which is not what is happening in the original example.|||...which is not what is happening in the original example.that's right, it's a cross join
Query a Table to return Non-Date values
Hi
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>
query a table on different server instance
I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!
First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!
First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!
query a table on different server instance
I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!sql
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!sql
Wednesday, March 28, 2012
query "For XML" problem?...or not!
hi guys
i'm developing a multi-tier web application which at a given point calls a stored procedure that returns data formated as XML ( 'select .... for xml auto' basically).
That SP returns a list of tasks allocated to 1 or more resources.
The problem is that from time to time (and that is totally random, anything from 30seconds to 10mins) whenever the code calls that SP, the XML structure isn't there, so no data is listed.
Question is: is this a problem within SQL Server XML support or anything related to .NET?
I'm using .NET 2.0.50727 with VS2005 8.0.50727.42, and SQL Server 2005 and IE 6.0.2900
Also, if i refresh the browser and go to the Task listing page, the list comes back again!
Note that i'm NOT using SESSIONs in ANY point, so this has nothing to do with sessions.
TIA
Sérgio Charrua
www.pdmfc.com
Portugal
Hi Sergio,
SQL Server XML support is very useful for some issues. But It isn't scalable solution.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||It's diffcult to figure out what's causing the problem. From SQL side, if a SP returns XML data, it just returns a binary stream to the client as other returned data. I suggest you open SQL Profiler to trace the SQL Server when the prolbem repros, so that you can clarify whehter SQL returns expected result set or empty (none) result, or maybe some network issue.sql
query - stored procedure
when we should use query and when should we use stored proceduse?
Thanks
PaulHi,
Please have look into the below link, it says the reason why procedure is
better than a SQL .
http://www.sql-server-performance.c..._procedures.asp
Thanks
Hari
MCDBA
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:#izHeGm4DHA.504@.TK2MSFTNGP11.phx.gbl...
Thanks
PaulHi,
Please have look into the below link, it says the reason why procedure is
better than a SQL .
http://www.sql-server-performance.c..._procedures.asp
Thanks
Hari
MCDBA
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:#izHeGm4DHA.504@.TK2MSFTNGP11.phx.gbl...
quote:sql
> when we should use query and when should we use stored proceduse?
> Thanks
> Paul
>
query - stored procedure
when we should use query and when should we use stored proceduse?
Thanks
PaulHi,
Please have look into the below link, it says the reason why procedure is
better than a SQL .
http://www.sql-server-performance.com/stored_procedures.asp
Thanks
Hari
MCDBA
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:#izHeGm4DHA.504@.TK2MSFTNGP11.phx.gbl...
> when we should use query and when should we use stored proceduse?
> Thanks
> Paul
>
Thanks
PaulHi,
Please have look into the below link, it says the reason why procedure is
better than a SQL .
http://www.sql-server-performance.com/stored_procedures.asp
Thanks
Hari
MCDBA
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:#izHeGm4DHA.504@.TK2MSFTNGP11.phx.gbl...
> when we should use query and when should we use stored proceduse?
> Thanks
> Paul
>
Query - check for a string in stored procedure
Hi,
I would like to check if a string value exist in a string in sql
server stored procedure, e.g.
set @.testString = 'this is my test document.'
if (@.testString contains 'test')
begin
....
end
How do I do this in sql server stored procedure?
Thanks,
June...try this
Declare @.testString varchar(100)
set @.testString = 'this is my test document.'
if charindex('test',@.testString)>0
begin
....
end
Madhivanan
Query
Hi!
I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3
table 1
| id | Col1 | Col2 | maxValues |
| 1 | aa | bb | 10 |
| 2 | cc | dd | 10 |
| 3 | ee | ff | 10 |
| 4 | gg | hh | 10 |
| 5 | jj | kk | 10 |
table 2
| id | Col1 | Col2 | CurrentValue |
| 1 | cc | dd | 7 |
| 2 | gg | hh | 3 |
| 3 | jj | kk | 5 |
table 3
| id | Col1 | Col2 | AvailableEntries |
| 1 | aa | bb | 10 |
| 2 | cc | dd | 7 |
| 3 | ee | ff | 10 |
| 4 | gg | hh | 3 |
| 5 | jj | kk | 5 |
Thanks for your help.
If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_twU8pc_0nf-rzs4IdcX5hHTRbQeo6A-465Z0g-RBmtEzKrBLaw2_Od7ZlmCjl9Yf4TN9tPx2kHI_gbT-zNkcf1o28aCOtatpMqLvIhg09VJ51-qAE1OQ=s0-d)
Query
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
Try:
use master
go
sp_helptext sp_databases
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
Try:
use master
go
sp_helptext sp_databases
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Monday, March 26, 2012
Query
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
Try:
use master
go
sp_helptext sp_databases
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
Try:
use master
go
sp_helptext sp_databases
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...
|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegr oups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Friday, March 23, 2012
Query
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...Try:
use master
go
sp_helptext sp_databases
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...Try:
use master
go
sp_helptext sp_databases
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Query
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...Try:
use master
go
sp_helptext sp_databases
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...Try:
use master
go
sp_helptext sp_databases
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...
Wednesday, March 21, 2012
Queries or SP
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the codeIt is better to use SP, as it has got many advantages|||thanx for answering
but can u list some of them|||Stored Procedure Advantages
Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries, including
but can u list some of them|||Stored Procedure Advantages
Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries, including
Execution plan retention and reuse
Query autoparameterization
Encapsulation of business rules and policies
Application modularization
Sharing of application logic between applications
Access to database objects that is both secure and uniform
Consistent, safe data modification
Network bandwidth conservation
Support for automatic execution at system start-up
http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=3
Try seeking information. google's your best friend.
Queries in SQL compact Edition
If my research is correct SQL CE as of this date does not support STORED PROCEDURES (queries).
If this is true, how can it hope to compete with MS ACCESS or MS Foxpro as a Desktop database.
This seems to be a lot of hype for something that appears to be totally in-adequate.
I thought this was a way to wean off of MS ACCESS.
Can someone tell me what I am missing ?
Thanks
John
SQL CE is not a replacment for MS Access, but can be used as small footprint, in-process database engine together with yur custom application. Queries (meaning select statements in your code) is supported. The storage of your query strings must be handled by your application, not by SQL CE.|||Access is a front-end database application tool--not a DBMS. Access by default uses JET as its DBMS engine. SQLCe is not an Access replacement but it can be a faster, lighter, more secure alternative to JET.
AFA stored procedures, these can be implemented in your code but they are not stored in the database or exectuted by the DBMS engine. This gives you the ability to create your own common code routines to replace SP functionality--something JET does not have. While JET supports canned queries, these do not support the logic afforeded by a real SP as supported by TSQL and SQL Server.
For many situations even JET is overkill--SQLCe is designed for these situations--especially when security of the stored data is an issue. JET databases cannot be encrypted--SQLCe databases can.
See my EBook FMI: www.hitchhikerguides.net.
Queries in productions AND history tables
I've got a large and growing database in SQL Server 7.0. I'd like to utilize a monthly stored procedure that will search various tables for records that are older than 3 months and copy this data out to corresponding history tables. Typically, most production queries are run only on the new data, but occasionally (like at year-end, for example), we will need to run some queries on data extending back 12 months -- that is, on data in the production tables AND in the history tables. Notwithstanding the fact that I've never done stored procedures*, I would like to know if it is possible to run a query that can search for data in both production and history tables at the same time. I know this sounds like a stupid question, but I read somewhere that doing this qould require some kind of Joining function that is complex...?
Thanks,
Whill96205
* Also, any good resources I could use for developing stored procedures?Well that would be a VIEW with a union
And here's a fundamental question...do you have an AD_DT column in all your tables
How big is the database?|||UNION queries can be a little bit tricky, but are not too difficult if both the tables you are pulling from have the same structure. Don't be intimidated.
Select [Column1], [Column2], [Column3]...from CurrentTable
UNION
Select [Column1], [Column2], [Column3]...from HistoryTable
Not so tough, eh?|||Thanks Brett and Blindman. I guess there's no big mystery for unioning then. :)
Well that would be a VIEW with a union
And here's a fundamental question...do you have an AD_DT column in all your tables
How big is the database?
Um... No. What is an AD_DT column?
--whill
P.S. btw, I'm using SQL Server 2000, not 7.0, if that makes any difference.|||Oh - almost forgot. :rolleyes: Regarding database size... The database currently has approximately 43000 records (transactions) in it, increasing by about 100 per day. My task is to incorporate a bunch of related data which the client is currently tracking in several huge spreadsheets. This may nearly double the amount of data, but not necessarily the number of transactions. So I am redesigning the db table structure to better accomdate and utilize the new data that will be imported from the spreadsheets.
Thanks,
Whill96205
* Also, any good resources I could use for developing stored procedures?Well that would be a VIEW with a union
And here's a fundamental question...do you have an AD_DT column in all your tables
How big is the database?|||UNION queries can be a little bit tricky, but are not too difficult if both the tables you are pulling from have the same structure. Don't be intimidated.
Select [Column1], [Column2], [Column3]...from CurrentTable
UNION
Select [Column1], [Column2], [Column3]...from HistoryTable
Not so tough, eh?|||Thanks Brett and Blindman. I guess there's no big mystery for unioning then. :)
Well that would be a VIEW with a union
And here's a fundamental question...do you have an AD_DT column in all your tables
How big is the database?
Um... No. What is an AD_DT column?
--whill
P.S. btw, I'm using SQL Server 2000, not 7.0, if that makes any difference.|||Oh - almost forgot. :rolleyes: Regarding database size... The database currently has approximately 43000 records (transactions) in it, increasing by about 100 per day. My task is to incorporate a bunch of related data which the client is currently tracking in several huge spreadsheets. This may nearly double the amount of data, but not necessarily the number of transactions. So I am redesigning the db table structure to better accomdate and utilize the new data that will be imported from the spreadsheets.
Queries going to Suspend state with wait type ASYNC_NETWORK_IO
Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.
When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.
The query makes any sence or is there any way to write it in better way
'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'
'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'
We have been fighting it out for so many days.
Can anybody help
Thanks
knvdssr
ASYNC_NETWORK_IO means SQL server is waiting for network...
How may rows your select statement for INSERT it is returning?
You are using ltrim and rtrim functions against column which result a table scan and it can't use index...
Try to change the query or just comment the query and test it to make it is the issue.
|||It appears you are using a LINKED SERVER to access another server?If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.
The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.
|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.
You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||
We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.
We changed the the query with our using the NOT IN in that query.
Now first time it is taking only 10 minutes for more than 100,000 records.
But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.
All that we see is the process in suspend state or comes out as timed out.
If I am getting a dead lock on the linked server is there any way to solve it?
Could any body help please
|||Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.
Tuesday, March 20, 2012
Queries are slow when accessed from remote machine
Hi,
I have succesfully created a Stored Procedure which runs under 2 seconds locally.
However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.
My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.
Any Ideas why this would be happening?
Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.
How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).
|||Thanks for the reply.
It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines
|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||Hi, me too have the same problem:
I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.
We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.
We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.
Both server are in the same LAN, switched to 1Gb
Some help?
Regards
vito
Queries are slow when accessed from remote machine
Hi,
I have succesfully created a Stored Procedure which runs under 2 seconds locally.
However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.
My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.
Any Ideas why this would be happening?
Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.
How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).
|||Thanks for the reply.
It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines
|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||Hi, me too have the same problem:
I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.
We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.
We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.
Both server are in the same LAN, switched to 1Gb
Some help?
Regards
vito
Subscribe to:
Posts (Atom)