Query Syntax: February 2012

Saturday, February 25, 2012

Query time out while inserting data

Hi guys,
when inserting data in a table i am getting the following
error:
Error : -2147217871
Source : Microsoft OLE DB Provider for SQL Server
Descripion : Timeout expired
Help Context : 1000440
The data is added through a stored procedure which is
called under MTS from a dll which in turn is accessed
through a ASP page.
The table has arround 150,000 records. This is not a bluk
insert. Why am i getting this error, is this a locking
problem?
Please reply ASAP
Thanking you.Could be a locking problem. Use EM, Current Activity, Profiler, sp_who, sp_who2 and sp_lock to
troubleshoot.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Saleem Subhi" <saleem_subhi@.hotmail.com> wrote in message
news:39d201c37614$137a6e00$a301280a@.phx.gbl...
> Hi guys,
> when inserting data in a table i am getting the following
> error:
> Error : -2147217871
> Source : Microsoft OLE DB Provider for SQL Server
> Descripion : Timeout expired
> Help Context : 1000440
> The data is added through a stored procedure which is
> called under MTS from a dll which in turn is accessed
> through a ASP page.
> The table has arround 150,000 records. This is not a bluk
> insert. Why am i getting this error, is this a locking
> problem?
> Please reply ASAP
> Thanking you.
>

Query Time Out Expired

I am using a School Management software with MSSql Server 2005 software is working well on the server but it is not accessible on network. we using wareless network....Open [Start> All Programs> Microsoft SQL Server 2005> Configuration Tools> SQL Server Surface Area Configuration], select [MSSQLSERVER> Database Engine> Remote Connections], make sure that "Local and remote connections" option is selected.

Query Time Out

Hi,

I have a report with query timeout set to 1 sec (I want this for producing timeout exception). When I view it from Business Intelligence Dev. studio's preview tab, it gives me "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" exception.

This is fine..but when i deploy it on report server it doesn't give this exception and opens the report without giving any exception.

in DatabaseQueryTimeout in rsreportserver.config is set to 900sec.

Can someone help me out if I am doing anything wrong or missing something?

And I'will be thankfull to you in advance.

-Thanks

Sounds like more a reporting services issue. Try that group.

query time out

What is best setting for query time out 600 or 0 , unlimited . and why?
On Sat, 12 Jan 2008 15:21:44 -0800, "OA" <omrana@.verizon.net> wrote:

>What is best setting for query time out 600 or 0 , unlimited . and why?
The best setting for query timeout is whatever is best for what you
are doing, because each circumstance is different.
I tend toward zero (unlimited) myself, but there are many applications
where that might be a mistake. Obviously an application that has
queries that take three minutes needs a timeout greater than that.
Likewise for an application that should respond in less than a second
it might make more sense to set a limit that is less than a delay that
causes the user to scream.
Roy Harvey
Beacon Falls, CT

query time out

What is best setting for query time out 600 or 0 , unlimited . and why?On Sat, 12 Jan 2008 15:21:44 -0800, "OA" <omrana@.verizon.net> wrote:
>What is best setting for query time out 600 or 0 , unlimited . and why?
The best setting for query timeout is whatever is best for what you
are doing, because each circumstance is different.
I tend toward zero (unlimited) myself, but there are many applications
where that might be a mistake. Obviously an application that has
queries that take three minutes needs a timeout greater than that.
Likewise for an application that should respond in less than a second
it might make more sense to set a limit that is less than a delay that
causes the user to scream.
Roy Harvey
Beacon Falls, CT

Query time massively different between App and QA

We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or...
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants :rolleyes: )

The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984

It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.Mmmmmmm, looks to me like your application is likely to be the root cause. How does Siebel access the DB? Named Pipes, TCPIP?|||I'm told Siebel uses Names Pipes.|||Mmmmm, seen named pipes cause an issue before, I think the app ends up scanning through several ports before finding the correct one?

Will do some head scratching|||There are a number of tools that use an older TDS library (some even using DB-Library) that get lousy performance from queries that run well in Query Analyzer. It seems to me that Siebel can be fixed by simply upgrading the client machine's MDAC (http://msdn.microsoft.com/data/mdac/default.aspx), but I don't regularly run Siebel so I'm not certain of that.

-PatP|||Now that is a smart idea. JamesB, you may want to check which version of MDAC the client machines have. I think the current version of MDAC is 2,7. 2.8 has apparently just been released but judging from all the woes on the forum I doubt that would be the one to use at the moment.

Ideally, the client and server MDACs should be the same version.

Query Time Limit?

I know I can use SET QUERY_GOVERNOR_COST_LIMIT to prevent a query from executing if its estimated execution time exceeds the value of the specified time in seconds, but is there some way to have a query stop executing (and raise an error) if the time exceeds a specified time? For example, if the query takes longer than 20 seconds, have it stop executing and raise an error?

Thanks,

-Dave

It prevents query from executing based on the cost rather than controlling resources.

BOL

'If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely.

"Query cost" refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

|||

If you are referring to having the ability to control any one specific query -that is not possible.

QUERY_GOVERNOR_COST_LIMIT effects ALL queries executed on the server. It prevents a query from executing IF the estimated time exceeds the limit. Say the QUERY_GOVERNOR_COST_LIMIT was 120 seconds, the estimated plan was 119 seconds -the query would then execute EVEN if the resulting time required exceeded 120 seconds. As far as I am aware, there is no way to have an executing query abort at some predetemined elapsed time value.

Of course, from an application, it might be possible to spawn a thread that executed a query against SQL Server, and then the parent thread could abort the executing thread after a predetermined time. But that could be messy...

Query Time in SQL Server

I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.

My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..

Thanks
SathyaI do not know of a way to determine the approximate execution time of a stored procedure before it runs.

Perhaps you should use the worst-case execution time as your estimate for each?

And also, 2-4 minutes for a query to run is not reasonable. You should strongly consider spending some time trying to optimize these queries.

Terri|||I agree with Terri on this one. 2-4 minutes for a query to run especially as a stored procedure is saying a tremendous amount to the inefficiencies you may have in your design or execution.

Initially, I'd take the time to repair that before continuing further to make your future tasks with your application even more complicated.

Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER12 40 KB8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWERdbouser table2004-11-12 17:31:05.130
ID intno4
NAME varcharno50
URL varcharno1024
PARTNERNAMEvarcharno50
CACHEPERIODintno4
BEINGUPDATEDbitno1
ADDITIONALURLvarcharno512
UPDATEBEGINDATEdatetimeno8
OSPRK_osusr_131_PREVIEWERclustered, unique, primary key located on PRIMARYID
PRIMARY KEY (clustered)OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
Jorge
Hi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>
|||Is the only index you have the index created by the primary key? If so, you probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any non-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>
|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of a
permanent resolution by understanding what is causing so many locks in such a
small table.
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:

> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER12 40 KB8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWERdbouser table2004-11-12 17:31:05.130
> ID intno4
> NAME varcharno50
> URL varcharno1024
> PARTNERNAMEvarcharno50
> CACHEPERIODintno4
> BEINGUPDATEDbitno1
> ADDITIONALURLvarcharno512
> UPDATEBEGINDATEdatetimeno8
> OSPRK_osusr_131_PREVIEWERclustered, unique, primary key located on PRIMARYID
> PRIMARY KEY (clustered)OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>
|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
[vbcol=seagreen]
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on the
> current table. While this can be a temporary solution, you need to think of a
> permanent resolution by understanding what is causing so many locks in such a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:

Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
ID int no 4
NAME varchar no 50
URL varchar no 1024
PARTNERNAME varchar no 50
CACHEPERIOD int no 4
BEINGUPDATED bit no 1
ADDITIONALURL varchar no 512
UPDATEBEGINDATE datetime no 8
OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY
ID
PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
JorgeHi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Is the only index you have the index created by the primary key? If so, you
probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any n
on-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one tab
le
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem
.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMAR
Y ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of
a
permanent resolution by understanding what is causing so many locks in such
a
small table.
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:

> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one tab
le
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem
.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMAR
Y ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
[vbcol=seagreen]
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on th
e
> current table. While this can be a temporary solution, you need to think o
f a
> permanent resolution by understanding what is causing so many locks in suc
h a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:
>

Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
ID int no 4
NAME varchar no 50
URL varchar no 1024
PARTNERNAME varchar no 50
CACHEPERIOD int no 4
BEINGUPDATED bit no 1
ADDITIONALURL varchar no 512
UPDATEBEGINDATE datetime no 8
OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
JorgeHi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Is the only index you have the index created by the primary key? If so, you probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any non-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of a
permanent resolution by understanding what is causing so many locks in such a
small table.
--
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on the
> current table. While this can be a temporary solution, you need to think of a
> permanent resolution by understanding what is causing so many locks in such a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:
> > Hello,
> >
> > I've got a very dificult or strange troubleshoot in my hands. I've one table
> > that is very small in my database.
> >
> > sp_spaceused 'osusr_131_PREVIEWER'
> > Name Rows Reserved Data
> > Index_size Unused
> > osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> >
> > When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> > query execution time to return the final result its 2 minutes. I know that
> > this table its locked a lot of times, but i cant turn arround this problem.
> >
> > I send a description of my table:
> >
> > osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> >
> > ID int no 4
> > NAME varchar no 50
> > URL varchar no 1024
> > PARTNERNAME varchar no 50
> > CACHEPERIOD int no 4
> > BEINGUPDATED bit no 1
> > ADDITIONALURL varchar no 512
> > UPDATEBEGINDATE datetime no 8
> >
> > OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> >
> > PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> >
> > Table is referenced by foreign key.
> > OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> > OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> > OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> > OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> > OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> > OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> >
> > Have you any idea to resolve this situation?
> >
> > Thanks and best regards,
> > Jorge
> >
> >
> >

Query Time and Network Traffic

I've got a pretty large db (150 gig -- 150mil records) and I've been struggling to write queries that run in a reasonable amount of time. Well, after doing everything I know to do (analyzing the queries, properly indexing fields, etc.) I've managed to get some pretty fast, efficient queries done. When I first took responsibility of the db, the queries I was writing were running in like 30 minutes. So, I've managed to cut them down to under a minute on average. That's all great, but I was messing around last night (when nobody else is at work...we have quite a large network) and all my apps using this db were running queries INSTANTLY. I mean REALLY fast! So, do you think it's safe to say that I've just done a really good job, and there isn't really any more I can do as far as query time when there is a lot of traffic on the network?

It never really occured to me that network traffic might slow down your queries. (Not that much anyway) Is this something anyone else has experienced before, or am I just crazy?As far as optimization of queries by applying indexing strategies, you probably exhausted your options. The next step will be to see if some denormalization can be introduced to minimize number of joins per query. Of course all this is only if users are not returning the world back. You're absolutely right when suspecting the network traffic. It's especially true when large resultsets are floating around.|||Network traffic won't slow down SQL Server's processing of your queries, but it will definitley slow down the return of large result sets.

But low network traffic is probably not the only factor affecting the speed of your queries after-hours. It is also likely that there were few if any other users accessing your database tables and locking resources for inserts, updates, and deletes. If your database where you are running these queries (they sound like OLAP queries) is also used for transaction processing, then these two functionalities my frequently fight for resources. Consider creating a copy of your database (use replication to keep it synchronized) and thus split the processing load between two servers.

blindman

Query Through Internet

I have a security setup question: I have a group of employees that must
be able to make ad hoc queries against data stored on the SQL Server
computer via the Internet. I have one SQL computer and one IIS computer.
Many of these employees do not have computers that use a Microsoft
operating system or Web browser. I know I must use IIS virtual
directory, which is configured for Basic Authentication right now. But,
in addition to these known users, how would I also configure for
anonymous users? Would I then use the IIS Intenet Guest account?
Help apprecaited. Thanks
Frank
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Yes, if you wish anonymous users, you must add the login IUSR_servername as
a valid SQL login and give it appropriate permissions...
"Frank Py" <fpy@.proactnet.com> wrote in message
news:uiJ1mGnuDHA.1224@.TK2MSFTNGP09.phx.gbl...
> I have a security setup question: I have a group of employees that must
> be able to make ad hoc queries against data stored on the SQL Server
> computer via the Internet. I have one SQL computer and one IIS computer.
> Many of these employees do not have computers that use a Microsoft
> operating system or Web browser. I know I must use IIS virtual
> directory, which is configured for Basic Authentication right now. But,
> in addition to these known users, how would I also configure for
> anonymous users? Would I then use the IIS Intenet Guest account?
> Help apprecaited. Thanks
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Query through a firewall

We have a SQL 2000 database sitting behind a CheckPoint firewall. The server
host our HelpSTAR helpdesk
database. Clients connect to the database from their workstation with the
HelpSTAR client. All the proper ports
are open to allow communication through the firewall between client and
workstation. We have several reports
that will not run. When I execute the following query I get the response
desired.
"SELECT * from tblrptfiles where id = 11"
When I run the same query but for a different id # it fails after about 30
seconds with the listed error message.
"SELECT * from tblrptfiles where id = 24"
Error message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I run the same query that failed from a machine in the same subnet
without the firewall between them it works. Keep in mind that my query works
with the firewall inplace if I ask for a different id. If I run the query
with an ID that is not in the table it works, also the ID I am asking for
does exist in the table. Any ideas on what would be causing this?
Thanks.
This is a weird one. Are you sure it's not just different machines
requesting a different set of ID's? Have you made sure all the clients and
SQL Server are using the latest MDAC?
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Tubbaguts" wrote:

> We have a SQL 2000 database sitting behind a CheckPoint firewall. The server
> host our HelpSTAR helpdesk
> database. Clients connect to the database from their workstation with the
> HelpSTAR client. All the proper ports
> are open to allow communication through the firewall between client and
> workstation. We have several reports
> that will not run. When I execute the following query I get the response
> desired.
> "SELECT * from tblrptfiles where id = 11"
> When I run the same query but for a different id # it fails after about 30
> seconds with the listed error message.
> "SELECT * from tblrptfiles where id = 24"
> Error message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I run the same query that failed from a machine in the same subnet
> without the firewall between them it works. Keep in mind that my query works
> with the firewall inplace if I ask for a different id. If I run the query
> with an ID that is not in the table it works, also the ID I am asking for
> does exist in the table. Any ideas on what would be causing this?
> Thanks.
|||We discovered our issue with this query through the firewall late yesterday.
Our Check Point firewall also has an intrusion prevention running and the
firewall was seeing this query and one other as being a SQL exploit. Once we
allowed this query through everything worked again.
"MeanOldDBA" wrote:
[vbcol=seagreen]
> This is a weird one. Are you sure it's not just different machines
> requesting a different set of ID's? Have you made sure all the clients and
> SQL Server are using the latest MDAC?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Tubbaguts" wrote:

Query through a firewall

We have a SQL 2000 database sitting behind a CheckPoint firewall. The server
host our HelpSTAR helpdesk
database. Clients connect to the database from their workstation with the
HelpSTAR client. All the proper ports
are open to allow communication through the firewall between client and
workstation. We have several reports
that will not run. When I execute the following query I get the response
desired.
"SELECT * from tblrptfiles where id = 11"
When I run the same query but for a different id # it fails after about 30
seconds with the listed error message.
"SELECT * from tblrptfiles where id = 24"
Error message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForD
ata
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I run the same query that failed from a machine in the same subnet
without the firewall between them it works. Keep in mind that my query works
with the firewall inplace if I ask for a different id. If I run the query
with an ID that is not in the table it works, also the ID I am asking for
does exist in the table. Any ideas on what would be causing this?
Thanks.This is a weird one. Are you sure it's not just different machines
requesting a different set of ID's? Have you made sure all the clients and
SQL Server are using the latest MDAC?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Tubbaguts" wrote:

> We have a SQL 2000 database sitting behind a CheckPoint firewall. The serv
er
> host our HelpSTAR helpdesk
> database. Clients connect to the database from their workstation with the
> HelpSTAR client. All the proper ports
> are open to allow communication through the firewall between client and
> workstation. We have several reports
> that will not run. When I execute the following query I get the response
> desired.
> "SELECT * from tblrptfiles where id = 11"
> When I run the same query but for a different id # it fails after about 30
> seconds with the listed error message.
> "SELECT * from tblrptfiles where id = 24"
> Error message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckFo
rData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I run the same query that failed from a machine in the same subnet
> without the firewall between them it works. Keep in mind that my query wor
ks
> with the firewall inplace if I ask for a different id. If I run the query
> with an ID that is not in the table it works, also the ID I am asking for
> does exist in the table. Any ideas on what would be causing this?
> Thanks.|||We discovered our issue with this query through the firewall late yesterday.
Our Check Point firewall also has an intrusion prevention running and the
firewall was seeing this query and one other as being a SQL exploit. Once we
allowed this query through everything worked again.
"MeanOldDBA" wrote:
[vbcol=seagreen]
> This is a weird one. Are you sure it's not just different machines
> requesting a different set of ID's? Have you made sure all the clients an
d
> SQL Server are using the latest MDAC?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Tubbaguts" wrote:
>

Query Three Tables - Please Help

Hi

I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)

To get the data I need - I need to query three tables.

Stockitem - This has the column "itemid"
Stockitemmemo - This has the column "itemid" and "timeanddatecreated"
Binitem - This has the column "itemid" and "binname"

My results must be based on the following criteria.....

All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.

I do have two questions based on the above...

1. Does it make sense what I need?
2. Is it possible

:confused:

Any help would be gratefully received.

Thanks

SimbaOK, help us out.

Read the hint sticky at the top of the forum and post what it asks for|||Possibly a straight Forward

SELECT Stockitem.itemid
,Stockitemmemo.Memo
,Binitem.binname
FROM Stockitem
INNER JOIN Stockitemmemo ON
Stockitem.itemid = Stockitemmemo.itemid
INNER JOIN Binitem ON
Stockitem.itemid = Binitem.itemid
WHERE Binitem.binname LIKE'%T%'
AND Stockitemmemo.timeanddatecreated <'01/02/2007'

Everyones gotta start somewhere :shocked:

Beware If there is NO matching record in either Stockitemmemo or Binitem table then the Stockitem row will NOT be returned - You would need to Consider a LEFT Join to deal with that.

GW

GW|||Your an absolute legend thanks very much! Appreciate your help.

Thanks again.:beer:|||You're Welcome :)|||What's in an absolut legend anyway? Sounds interesting, and my banana Kamikaze's are starting to get me in trouble, so I need a change.|||LOL Tall
What's in an absolut legend anyway? in the UK we call it "Horses for courses"

I'll gladly leave the difficult one's for the u true legends.

Not sure what a banana Kamikaze is but sounds like you need a TallBoy http://www.dbforums.com/showpost.php?p=6287499&postcount=30 - LOL

GW

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK)ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK)ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username =user_name()

execsp_helpuser @.username

Query the Security Login or Security User of the current person logged in

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK) ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK) ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username = user_name()

exec sp_helpuser @.username

Query the results of another query

I'm new to the database world - I know what I want to do, but not sure if or how to do it...

Is there a way to run a query and then build on it to query the results of that query?

My example is as follows:

Query the number of distinct machines by ID in Week 1 = Results

Query the distinct number of machines by ID in Week 2 minus the Results from Week 1

- NOT EXISTS
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')

- Use NOT IN
SELECT pub_name
FROM publishers
WHERE pub_id NOT IN
(SELECT pub_id
FROM titles
WHERE type = 'business')

- Perform a LEFT OUTER JOIN and check for a NULL condition
SELECT pub_name
FROM publishers A
LEFT OUTER JOIN titles B
ON A.pub_id = B.pub_id
WHERE A.pub_id IS NULL

|||

Thank you - I used the NOT IN and it seems to be doing exactly what I wanted.

Much Appreciated!!

Query the latest info

Hi All,

I have thsi list of record

Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004

sp i want to list out cars that status solid has been sold

so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

Regards,
ShaffiqYou want to list only the cars that have been sold?

Select * from table where status = 'sold'|||You want to list only the cars that have been sold?

Select * from table where status = 'sold'

hi,
its easy, but sometime cars that has been sold were return back , so this not considered sold. I want to display cars that solid has been sold...sometimes got status sold, return and sold back... but sometimes only sold..for this case only Pl3 and PL4.. any idea? thanks|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')

Thx,
I' d tried before but it will not display cars that have tree status( sold, return, sold) for this case you can see at PL4|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'

Hi r937,

Thx for the solution it works well thank also to pshisbey

Query the last access data/time

Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JBHi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <jbngspam@.yahoo.com> wrote in message
news:42bf9544$0$18637$14726298@.news.sunsite.dk...
> Hi all,
> Is there any way to query the last date/time when a database(preferable)
> or object was accessed?
> SQL 2k sp4
> i.e. database blah was last accessed on 2005-01-05 23:20:20.
> (insert/update/create/delete/etc...)
>
> Cheers
> JB|||This is a multi-part message in MIME format.
--040107090904080005040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Or alternately, you could create a profiler trace (with sp_trace_create
and the other trace procs) and set it to run when SQL server starts
(with sp_procoption). Then you could query the output of that trace (if
you wanted to query it with T-SQL you'd import the trace output file
(open it in Profiler and then SaveAs... a Trace Table...) into a table
and query that table). This is a little kludgey and fairly expensive
(the running profiler trace that is) in terms of resources on the SQL
box but it would work as long as you set up your trace appropriately (it
would take a bit of fine tuning).
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher.
HTH.
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Hari Prasad wrote:
>Hi,
>SQL Server will not store these information. But for new object creation you
>can see the CRDATE column in sysobjects table.
>But for Insert/update and delete you need to write trigger to populate a
>audit table. Later you could use the audit table.
>Thanks
>Hari
>SQL Server MVP
>"John B" <jbngspam@.yahoo.com> wrote in message
>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>
>
--040107090904080005040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Or alternately, you could create a profiler trace (with
sp_trace_create and the other trace procs) and set it to run when SQL
server starts (with sp_procoption). Then you could query the output of
that trace (if you wanted to query it with T-SQL you'd import the trace
output file (open it in Profiler and then SaveAs... a Trace Table...)
into a table and query that table). This is a little kludgey and
fairly expensive (the running profiler trace that is) in terms of
resources on the SQL box but it would work as long as you set up your
trace appropriately (it would take a bit of fine tuning). 
 
Another option, if you're just interested in data modification
(including DDL) activity, is read the transaction log with a 3rd party
tool like a Lumigent tool (Log Explorer, for example), or even the
undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
null)) but the output is undocumented and hard to decipher. 
 
HTH. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
 mallesons stephen jaques 
blog: <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a> 
</div>
 
 
Hari Prasad wrote:
<blockquote cite="mid%23LLkY7teFHA.900@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">Hi,
SQL Server will not store these information. But for new object creation you
can see the CRDATE column in sysobjects table.
But for Insert/update and delete you need to write trigger to populate a
audit table. Later you could use the audit table.
Thanks
Hari
SQL Server MVP
"John B" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:jbngspam@.yahoo.com"><jbngspam@.yahoo.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:42bf9544$0$18637$14726298@.news.sunsite.dk">news:42bf9544$0$18637$14726298@.news.sunsite.dk</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi all,
Is there any way to query the last date/time when a database(preferable)
or object was accessed?
SQL 2k sp4
i.e. database blah was last accessed on 2005-01-05 23:20:20.
(insert/update/create/delete/etc...)
Cheers
JB
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040107090904080005040400--|||Mike Hodgson wrote:
Thanks for the reply's guys.
Cheers
JB
> Or alternately, you could create a profiler trace (with sp_trace_create
> and the other trace procs) and set it to run when SQL server starts
> (with sp_procoption). Then you could query the output of that trace (if
> you wanted to query it with T-SQL you'd import the trace output file
> (open it in Profiler and then SaveAs... a Trace Table...) into a table
> and query that table). This is a little kludgey and fairly expensive
> (the running profiler trace that is) in terms of resources on the SQL
> box but it would work as long as you set up your trace appropriately (it
> would take a bit of fine tuning).
> Another option, if you're just interested in data modification
> (including DDL) activity, is read the transaction log with a 3rd party
> tool like a Lumigent tool (Log Explorer, for example), or even the
> undocumented ::fn_dblog function (eg. select * from ::fn_dblog(null,
> null)) but the output is undocumented and hard to decipher.
> HTH.
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> Hari Prasad wrote:
>>Hi,
>>SQL Server will not store these information. But for new object creation you
>>can see the CRDATE column in sysobjects table.
>>But for Insert/update and delete you need to write trigger to populate a
>>audit table. Later you could use the audit table.
>>Thanks
>>Hari
>>SQL Server MVP
>>"John B" <jbngspam@.yahoo.com> wrote in message
>>news:42bf9544$0$18637$14726298@.news.sunsite.dk...
>>
>>Hi all,
>>Is there any way to query the last date/time when a database(preferable)
>>or object was accessed?
>>SQL 2k sp4
>>i.e. database blah was last accessed on 2005-01-05 23:20:20.
>>(insert/update/create/delete/etc...)
>>
>>Cheers
>>JB
>>
>>
>>

Query the last access data/time

Query the Full-Text Words List?

Hi Guys. I’m doing searches in a DB and am using the Full-Text Indexing.

CONTAINS() will only process strings (words!) three letters or more in length. Even though it can do substring searching, it will only do this if it recognises the parameter as a word.

So take this code for example:

Dim mySQLStatement As String = "SELECT TOP 100 Description, Price, Stock FROM Products WHERE "

x = Split(strString, " ")

For i = 0 To x.GetUpperBound(0)

If x(i).Length > 2 Then

mySQLStatement &= " CONTAINS(Description, '*" & x(i) & "*') AND "

ElseIf x(i).Length = 1 Or x(i).Length = 2 Then

mySQLStatement &= " Description LIKE '%" & x(i) & "%' AND "

End If

If mySQLStatement.EndsWith(" AND ") Then mySQLStatement = Left(mySQLStatement, Len(mySQLStatement) - 5)

mySQLStatement &= " ORDER BY PRICE DESC"

dolog(mySQLStatement)

mySQLCommand = New SqlCommand(mySQLStatement, mySQLConnection)

mySQLAdapter = New SqlDataAdapter(mySQLCommand)

myDataSet = New DataSet : mySQLAdapter.Fill(myDataSet)

That is code I’m using in a small proof-of-concept application I’m writing – so I’m aware I can use StringBuilders and should be using SPs and all that jazz.

It will process the queries people type in, so if the person were to type in “sql server”, it would perform all of the following queries: (this is by design by the way)

SELECT TOP 100 Description, Price, Stock FROM Products WHERE Description LIKE '%s%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE Description LIKE '%sq%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND Description LIKE '%s%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND Description LIKE '%se%' ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*ser*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*serv*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*serve*') ORDER BY PRICE DESC

SELECT TOP 100 Description, Price, Stock FROM Products WHERE CONTAINS(Description, '*sql*') AND CONTAINS(Description, '*server*') ORDER BY PRICE DESC

I figured that if CONTAINS() ignores anything under 3 letters, then I’ll use LIKE for things under 3 letters. Problem is though, only the lines in bold work.

This is because CONTAINS() does not bother searching (as far as I can tell) on the parameters: '*ser*','*serv*' and '*serve*'.

So. My question is. How can I find out what strings CONTAINS() does and does not consider searchable words. Because I’d like to check if the query should be executed via LIKE or CONTAINS and build the SQL Statement accordingly. Having said that, using LIKE on its own is running quickly enough, but I would really like to use CONTAINS() instead.

I may of course be making so big logical error here or not understand something in particular, so any help would be appreciated…

Jamie, I need some more information to help answer your question:

What exactly do you mean when you say only the queries in bold "work"? Do you get an error or no results?
What is your sample data like?
What is your intention behind putting '*' before and after a string?

--
Sara Tahir
Program Manager
Microsoft SQL Server

|||

plenderj wrote:
CONTAINS() will only process strings (words!) three letters or more in length. Even though it can do substring searching, it will only do this if it recognises the parameter as a word.
...
I figured that if CONTAINS() ignores anything under 3 letters, then I’ll use LIKE for things under 3 letters. Problem is though, only the lines in bold work.
This is because CONTAINS() does not bother searching (as far as I can tell) on the parameters: '*ser*','*serv*' and '*serve*'.

My question is. How can I find out what strings CONTAINS() does and does not consider searchable words.

Full-Text Search results for your queries depend on multiple things including the wordbreaker, noise word list and whether the intention is to prefix, etc. Without that information, it’s hard to recommend which approach is better.

Here is some information that may help:

Full-Text is token (word) based search and not a substring search. Therefore it will not find arbitrary 3 char string patterns in middle of strings.

Tokenizing is dependent on the wordbreaker and that depends on the language of the column. Some tokenizers could consider the * as punctuation and strip it out - others might leave it intact. In any case Full-Text will be consistent in query and indexing, if the intent was to search the exact string with * and so will be able to match.

Full-Text does do prefix search - meaning if the user provides the leading part of a token, it can find all the tokens that have that prefix. The syntax for such a query is ' "foo*" ' where foo is the prefix and both the prefix and the * are enclosed in double-quotes. So if the intent below of trailing * was to do prefix match, you would need to put it in double-quotes.

There is no support for equivalent suffix match. So if the intent of leading * was that, then Full-Text does not support it.

Full-Text does not have any restrictions on the size of tokes - except 64 characters is max and by default single character token are considered noise.

The only things Full-Text does not consider searchable are the noise words – you can find that list in the noise word file for the specific language.

|||

Hello, everyone! I started to use full-text search and found out this not nice limitation in length of words and noise words. Firstly, I'm not sure that this limit exist, because I can search for two letters long words. If we look in noise file we can find all letters typed in, so one letter long words are noise words and this is not lenght limitation (must try to remove those letters and try search for them).
Again, if I have noise words in query I get error message "Server: Msg 7619, Level 16, State 1, Line 1" which doesn't tell me anything about noise word, so I can't determine it and do proper search using "like" for noise words and "contains" for all other.
Finaly, question. What is a best practice to go around this problem. Import all noise words into application or remove all lines from noise words file.

|||Strange that contains doesn't allow one letter words (just letter) but in noise word files I can see letters. If I remove them, doesn't help.|||Erik,
Firstly, The above reference limit of 3 characters does not exist (otherwise, what would of been the point in adding FTS to SQL 7.0, 2000 and 2005 in the first place?)

Secondly, what exactly was the noise word file you modified? Was it noise.enu (US English) or some other noise.* file under \FTDATA\SQLServer\Config where you have SQL Server 2000 installed? Assuming that you modified the correct file for the "Language for Word Breaker" for your FT-enabled table, did you run a Full Population? If not, then re-run a Full Population as this is required after making changes to the noise word files.

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||I removed data from all of noise word files, but limit for one letter is stil exist. I also done full population.
It's not realy a problem now, problem is that if I want to install my application I also need different instance of SQL server, right? Also, I can run application on same instance with others but then I need to determine those noise words, but I'm not sure that there is a safe way to do it.|||Hi Erik,
So, you left only one letter? What really needs to be done is to leave a single space character in your language specific noise.* file. If you're using US English, then the noise word file is noise.enu. You can determine this by running the following code in your FT-enabled database:

sp_help_fulltext_columns
-- FULLTEXT_LANGUAGE value of 1033 = US English

There was no need to remove all text from all noise word files. If you leave a single space in the nosie word file (noise.enu) and then run a Full Population it will work for you.

No, you don't need to install a different instance (unless you want to), as you can use the default install of SQL Server 2000. Note, I'm assuming that you're using SQL Server 2000 (let me know if you're using SQL Server 2005), there is one MSSearch service for SQL 2000, but the noise word files are installed into each named instance folder, specificly under:

\MSSQL<$Instance_Name>\FTDATA\SQLServer<$Instance_Name>\Config

for example: for Instance name "SQL80":
\MSSQL$SQL2K\FTData\SQLServer$SQL2K\Config\noise.enu

On safe way to determine where are the instance noise word files, is to store the above path in a table along with the instance name. The path is also recorded in the registry under an instance specific registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override\SQLServer$SQL2K\English (United States)
Locale value= 1033
NoiseFile value= d:\mssql80\MSSQL$SQL2K\FTData\SQLServer$SQL2K\Config\noise.enu

Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||Hi Jamie,
Drop me a line re: this an S205 noise lists and how to develop your own/circumvent.
Do remember they are localised.
fb|||I've since resolved/worked-around by using LIKE:

Shared Function GetMatchingDSForProducts(ByVal strString As String, ByRef mySQLStatement As String) As DataSet
Dim mySQLConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConn").ToString)
mySQLStatement = "SELECT TOP 100 Description, Price, Stock, [SAP Code] FROM Products WHERE "
mySQLConnection.Open()
Dim x() As String, i As Integer
x = Split(strString, " ")
For i = 0 To x.GetUpperBound(0)
If Not x(i) = "instock" Then mySQLStatement &= " Description LIKE '%" & x(i) & "%' AND "
Next
If Not InStr(strString, "instock") = 0 Then mySQLStatement &= " STOCK > 0 "
If mySQLStatement.EndsWith(" AND ") Then mySQLStatement = Left(mySQLStatement, Len(mySQLStatement) - 4)
mySQLStatement &= " ORDER BY PRICE DESC"
Dim mySQLCommand As New SqlCommand(mySQLStatement, mySQLConnection)
Dim mySQLAdapter As New SqlDataAdapter(mySQLCommand)
mySQLAdapter.SelectCommand = mySQLCommand
Dim myDataSet As New DataSet : mySQLAdapter.Fill(myDataSet)
mySQLConnection.Close()
Return myDataSet
End Function

query the domain

Hello,
Can anyone tell me how to query the domain we work with.
Domain is a standard W2K domain, SQL server is V7.
Thanks in advance.
Grtz.
E.
You will have to use a programming language and ADO
http://support.microsoft.com/kb/q187529/
Nik Marshall-Blank MCSD/MCDBA
"Erik vd Groef" <ErikvdGroef@.discussions.microsoft.com> wrote in message
news:FEC8A3ED-2F02-4054-9D4A-7867B53C2B93@.microsoft.com...
> Hello,
> Can anyone tell me how to query the domain we work with.
> Domain is a standard W2K domain, SQL server is V7.
> Thanks in advance.
> --
> Grtz.
> E.
>

query the domain

Hello,
Can anyone tell me how to query the domain we work with.
Domain is a standard W2K domain, SQL server is V7.
Thanks in advance.
--
Grtz.
E.You will have to use a programming language and ADO
http://support.microsoft.com/kb/q187529/
Nik Marshall-Blank MCSD/MCDBA
"Erik vd Groef" <ErikvdGroef@.discussions.microsoft.com> wrote in message
news:FEC8A3ED-2F02-4054-9D4A-7867B53C2B93@.microsoft.com...
> Hello,
> Can anyone tell me how to query the domain we work with.
> Domain is a standard W2K domain, SQL server is V7.
> Thanks in advance.
> --
> Grtz.
> E.
>

query the domain

Hello,
Can anyone tell me how to query the domain we work with.
Domain is a standard W2K domain, SQL server is V7.
Thanks in advance.
--
Grtz.
E.You will have to use a programming language and ADO
http://support.microsoft.com/kb/q187529/
--
Nik Marshall-Blank MCSD/MCDBA
"Erik vd Groef" <ErikvdGroef@.discussions.microsoft.com> wrote in message
news:FEC8A3ED-2F02-4054-9D4A-7867B53C2B93@.microsoft.com...
> Hello,
> Can anyone tell me how to query the domain we work with.
> Domain is a standard W2K domain, SQL server is V7.
> Thanks in advance.
> --
> Grtz.
> E.
>

query the amount of transactions in a period of time with sql server 2000

Is there a native tool (profile,trace,performance) feature I can use
to determine the amount of transactions that occur throughout the day?
Or is there a system table that keeps track of this(would be
preferable .. less strain on the system)?
I assume figuring out the transaction in a certain period will enable
me to calculate the busiest periods...I need to know the busiest
period of the day...how do I do this without putting an additional
strain on the server (can I use a different machine other than the
server to save a trace) ...I need to determine strain on
(processor,memory, and disk).
I also need to get a count on the largest number of users (running
transactions) on the server simultaneously.
Any help/advice would be deeply appreciatedFor this to be really meaingful, you should first define what you mean by
transactions. Your definition of transactions can impact your count of
transactions per second.
But if you just want to get a rough idea and the number of SQL requests from
non-apps (e.g. your Enterprise Manager, your monitoring tools, your cluster
service, etc)is relatively small compared to the SQL requests from your apps,
the perfmon counter batch Requests/sec under SQLServer:SQL Statistics can
give you pretty good idea as to how busy your SQL instance is and when. And
collecting the values of this counter is inexpensive.
Linchi
"tom booster" wrote:
> Is there a native tool (profile,trace,performance) feature I can use
> to determine the amount of transactions that occur throughout the day?
> Or is there a system table that keeps track of this(would be
> preferable .. less strain on the system)?
> I assume figuring out the transaction in a certain period will enable
> me to calculate the busiest periods...I need to know the busiest
> period of the day...how do I do this without putting an additional
> strain on the server (can I use a different machine other than the
> server to save a trace) ...I need to determine strain on
> (processor,memory, and disk).
> I also need to get a count on the largest number of users (running
> transactions) on the server simultaneously.
>
> Any help/advice would be deeply appreciated
>

Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.

ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476

BUTTONS:

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!

query that select extended procs

Hi
If I register my own proc via exec
sp_addextendedproc 'xp_myproc', 'xp_mylib.dll'.
How then I can look all my own procs and dll's?
In which tables are this data saved?

Thx.Not sure what you are after?

you can find what objects are extended proc from sysobejects where type =
'X' and you can get dll name out of syscomment text column but there is not
real automated way to get the source as it is not stored in dbms.

Hope this helps.
MarcM
"Indrek Mgi" <polemeili@.hotmail.com> wrote in message
news:d2dn01dlmi8srm3mg4l3nginduo5661h5h@.4ax.com...
> Hi
> If I register my own proc via exec
> sp_addextendedproc 'xp_myproc', 'xp_mylib.dll'.
> How then I can look all my own procs and dll's?
> In which tables are this data saved?
> Thx.

Query that automatically deletes records that are older than 365 d

Hi all,
I'm trying to build a self running query that deletes all records that are
older than 365 days. the code i've used is -
DELETE RECadvertId
RECadRecruiterId
RECadreference
RECadjobtype
RECadLocation
RECadJobTitle
RECadSalary
RECadcategory
RECadduration
RECadstartdate
RECadDatePosted
RECadCompanyID
RECadDescription
RECadDescriptionshort
RECaddayspostedfor
RECadBenefits
FROM [dbo].[RECadvert]
WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
It isn't working, any ideas?
thanksLeave out the column names:
DELETE FROM [dbo].[RECadvert]
WHERE RECadDatePosted < DATEADD(dd,-365,getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:84131850-B81C-4878-84CD-BC8C0116AD56@.microsoft.com...
Hi all,
I'm trying to build a self running query that deletes all records that are
older than 365 days. the code i've used is -
DELETE RECadvertId
RECadRecruiterId
RECadreference
RECadjobtype
RECadLocation
RECadJobTitle
RECadSalary
RECadcategory
RECadduration
RECadstartdate
RECadDatePosted
RECadCompanyID
RECadDescription
RECadDescriptionshort
RECaddayspostedfor
RECadBenefits
FROM [dbo].[RECadvert]
WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
It isn't working, any ideas?
thanks|||On Sun, 29 Jan 2006 12:55:28 -0800, GTN170777 wrote:

>Hi all,
>I'm trying to build a self running query that deletes all records that are
>older than 365 days. the code i've used is -
>DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
>It isn't working, any ideas?
>thanks
Hi GTN170777,
You've got the syntax of the DELETE statement wrong (you don't include a
column list - a DELETE will always rempve the entire row). And the WHERE
clause is incorrect too - this checks if RECadDatePosted is 365 days
older than itself (which it of course never is). You should compare
RECadDatePosted with the date that was 365 days before "now".
Something like this:
DELETE FROM dbo.RECadvert
WHERE RECadDatePosted < DATEADD(d, -365, CURRENT_TIMESTAMP)
(Note: this query is untested. Please test it on a test database first.
Also, enclose it in a transaction and check results before issuing a
COMMIT or a ROLLBACK command).
Hugo Kornelis, SQL Server MVP|||The WHERE clause is the problem. You cannot find one row where the value in
a column is not the same
as the value in that same column, i.e. the same value (take a long look at y
our WHERE clause and you
will understand). You probably want something like:
WHERE RECadDatePosted < DATEADD(d,-365,CURRENT_TIMESTAMP)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:84131850-B81C-4878-84CD-BC8C0116AD56@.microsoft.com...
> Hi all,
> I'm trying to build a self running query that deletes all records that are
> older than 365 days. the code i've used is -
> DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
> It isn't working, any ideas?
> thanks|||DELETE FROM [dbo].[RECadvert]
WHERE DATEDIFF(dd, RECadDatePosted, GETDATE()) > 365
You need the GETDATE() function to return the current date and time. The
DATEDIFF function, as used here, calculates the number of days that have
passed between a value for RECadDatePosted and today. If that number is more
than 365, that row will be deleted.
You don't need to specify column names when deleting.
"GTN170777" wrote:

> Hi all,
> I'm trying to build a self running query that deletes all records that are
> older than 365 days. the code i've used is -
> DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
> It isn't working, any ideas?
> thanks|||>> t isn't working, any ideas? <<
You might try writing SQL instead of whatever this language was. The
unit of work in SQL is a row; but you do not know that row is nothing
whatsoever like a record!! Please read a book or your error messages
before you post again.|||What hospitality. This will learn him to be curious about a subject. I
will bet he ran out and bought an entire catalog of books :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138582546.129921.115400@.g49g2000cwa.googlegroups.com...
> You might try writing SQL instead of whatever this language was. The
> unit of work in SQL is a row; but you do not know that row is nothing
> whatsoever like a record!! Please read a book or your error messages
> before you post again.
>|||Why is it that 5 other people knew what he was talking about and gave the
correct solution and you didn't?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138582546.129921.115400@.g49g2000cwa.googlegroups.com...
> You might try writing SQL instead of whatever this language was. The
> unit of work in SQL is a row; but you do not know that row is nothing
> whatsoever like a record!! Please read a book or your error messages
> before you post again.
>

Saturday, February 25, 2012

Query time out while inserting data

Query Time Out Expired

Query Time Out

query time out

query time out

Query time massively different between App and QA

Query Time Limit?

Query Time in SQL Server

Query time execution to long

Query time execution to long

Query time execution to long

Query Time and Network Traffic

Query Through Internet

Query through a firewall

Query through a firewall

Query Three Tables - Please Help

Query the Security Login or Security User of the current person logged in

Query the Security Login or Security User of the current person logged in

Query the results of another query

Query the latest info

Query the last access data/time

Query the last access data/time

Query the last access data/time

Query the Full-Text Words List?

query the domain

query the domain

query the domain

query the amount of transactions in a period of time with sql server 2000

Query that works in Management Studio, fails in Reporting Services

query that select extended procs

Query that automatically deletes records that are older than 365 d

Query Syntax

Blog Archive

About Me