I am trying to build my first report that is more than a simple columnar
report and wonder if you guys can help. I need to query the same sets of
fields but with different criteria and compare them over time as follows:
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2003
-show order NetAmount, Gross Amount, %NetAmount for each sales person for
2004
-show % change
Might look something like:
SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt 04NetAmt%
| GrsChg% NetChg%
Not sure if this needs subqueries/subreports or some othe report feature.
I am struggling with the documentation so many thanks for any helpTake a look at [Company Sales.rdl] sample report that ships with the
product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23gFwvgDrEHA.3868@.TK2MSFTNGP15.phx.gbl...
> I am trying to build my first report that is more than a simple columnar
> report and wonder if you guys can help. I need to query the same sets of
> fields but with different criteria and compare them over time as follows:
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2003
> -show order NetAmount, Gross Amount, %NetAmount for each sales person for
> 2004
> -show % change
> Might look something like:
> SalesPersId 03GrsAmt 03NetAmt 03NetAmt% | 04GrsAmt 04NetAmt
04NetAmt%
> | GrsChg% NetChg%
> Not sure if this needs subqueries/subreports or some othe report feature.
> I am struggling with the documentation so many thanks for any help
>
>|||Thanks for the resonse. I don't have the sampe data base loaded that is
needed for the sampler reports and not sure why it wasn't created during the
install.
-How would I go about installing that DB?
-If I were using straigt SQL for this, what functions would I need;
subqueries, group by ..?
Thanks again!sql
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
How Query Question
I have a form where users pick multiple values. I then want to run a query against the Sql Server backend and limit the result set based on the multiple picks the user just made.
IF those picks were in a 2nd table - it would look like:
Select * from firsttable join secondtable on firsttable_pk=secondtable_fk
The problem is that this hypothetical 2nd table would be local to the application, not on the back end.
I could store those pick in an Array, but not sure if that can be passed to the back end for use in a query.
Any suggestions?
Thanks,I've solved this problem in the past by inserting the selected values into a SQL Server #Temp table, and then joining to that table in my select statement. This works as long as each user has their own connection.|||Thanks blindman. I'll check BOL for the #TEMP table and give it a try. Each user will have their own connection.|||I've also used a User Defined Function for a similar purpose. I pass in a list of values separated by commas and then a UDF to parse them into a table. Then you can join to that.
Here is a link to one possible version of the ParseString Function: http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
IF those picks were in a 2nd table - it would look like:
Select * from firsttable join secondtable on firsttable_pk=secondtable_fk
The problem is that this hypothetical 2nd table would be local to the application, not on the back end.
I could store those pick in an Array, but not sure if that can be passed to the back end for use in a query.
Any suggestions?
Thanks,I've solved this problem in the past by inserting the selected values into a SQL Server #Temp table, and then joining to that table in my select statement. This works as long as each user has their own connection.|||Thanks blindman. I'll check BOL for the #TEMP table and give it a try. Each user will have their own connection.|||I've also used a User Defined Function for a similar purpose. I pass in a list of values separated by commas and then a UDF to parse them into a table. Then you can join to that.
Here is a link to one possible version of the ParseString Function: http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
How Query Analyzer works in Text Mode option ?
Hi,
I want to write a application works in same manner as SQL Query Analyzer
works in Text mode option. I studied the behavior of query analyzer and find
out that in text mode option query analyzer does not wait for the query to
get finished, for displaying the result.
It diplays the result in Text form as and when it gets the result. It works
in some different manner than Query Analyzer working in GRID option.
I also want to write a application which does not wait for all the results
of the query, just give me the records which are available right now and
then give the remaining ones also in similar chunks.
Query Analyzer must be using some API calls through which this is possible.
Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
Any help is appreciated.
Thanks
PushkarTo the best of my knowledge, you cannot use ADO or ADO.NET for this. QA uses
ODBC, and I have a
feeling that it is possible using OLEDB as well. So pick up a programmers ma
nual for one of those
APIs and check for asynchronous execution. But you should carefully consider
the extra work to use
these low level APIs compared to the productivity of a higher level API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eSsFbyTMGHA.2704@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer w
orks in Text mode
> option. I studied the behavior of query analyzer and find out that in text
mode option query
> analyzer does not wait for the query to get finished, for displaying the r
esult.
> It diplays the result in Text form as and when it gets the result. It work
s in some different
> manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
of the query, just give
> me the records which are available right now and then give the remaining o
nes also in similar
> chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
>
>|||Pushkar wrote:
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer
> works in Text mode option. I studied the behavior of query analyzer and fi
nd
> out that in text mode option query analyzer does not wait for the query to
> get finished, for displaying the result.
> It diplays the result in Text form as and when it gets the result. It work
s
> in some different manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
> of the query, just give me the records which are available right now and
> then give the remaining ones also in similar chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
> text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
It may help you to read:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Good thinking, David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139910046.905427.137570@.g43g2000cwa.googlegroups.com...
> Pushkar wrote:
> It may help you to read:
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
I want to write a application works in same manner as SQL Query Analyzer
works in Text mode option. I studied the behavior of query analyzer and find
out that in text mode option query analyzer does not wait for the query to
get finished, for displaying the result.
It diplays the result in Text form as and when it gets the result. It works
in some different manner than Query Analyzer working in GRID option.
I also want to write a application which does not wait for all the results
of the query, just give me the records which are available right now and
then give the remaining ones also in similar chunks.
Query Analyzer must be using some API calls through which this is possible.
Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
Any help is appreciated.
Thanks
PushkarTo the best of my knowledge, you cannot use ADO or ADO.NET for this. QA uses
ODBC, and I have a
feeling that it is possible using OLEDB as well. So pick up a programmers ma
nual for one of those
APIs and check for asynchronous execution. But you should carefully consider
the extra work to use
these low level APIs compared to the productivity of a higher level API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:eSsFbyTMGHA.2704@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer w
orks in Text mode
> option. I studied the behavior of query analyzer and find out that in text
mode option query
> analyzer does not wait for the query to get finished, for displaying the r
esult.
> It diplays the result in Text form as and when it gets the result. It work
s in some different
> manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
of the query, just give
> me the records which are available right now and then give the remaining o
nes also in similar
> chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
>
>|||Pushkar wrote:
> Hi,
> I want to write a application works in same manner as SQL Query Analyzer
> works in Text mode option. I studied the behavior of query analyzer and fi
nd
> out that in text mode option query analyzer does not wait for the query to
> get finished, for displaying the result.
> It diplays the result in Text form as and when it gets the result. It work
s
> in some different manner than Query Analyzer working in GRID option.
> I also want to write a application which does not wait for all the results
> of the query, just give me the records which are available right now and
> then give the remaining ones also in similar chunks.
> Query Analyzer must be using some API calls through which this is possible
.
> Can any one help me in figure out what APIs are used by query analyzer in
> text mode option.
> Any help is appreciated.
> Thanks
> Pushkar
It may help you to read:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Good thinking, David. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139910046.905427.137570@.g43g2000cwa.googlegroups.com...
> Pushkar wrote:
> It may help you to read:
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
how query Active Directory from sql2k server?
I've seen this linked server syntax many times in the group. but it
didn't work for me, maybe someone can explain it better for me?
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
i know that:
* " ADSI" is the server argument, which will be the name of this
linked server.
* "Active Directory Services" is the srvproduct argument, which is
the name of the OLE DB data source that you are adding as a linked
server.
* "ADSDSOObject" is the provider_name argument and indicates you
are using the OLE DB Provider.
* "adsdatasource" is the data_source argument, which is the name of
the data source as interpreted by the OLE DB Provider.
my questions are:
what "ADSDSOObject" and "adsdatasource" should be in my case? do i
need an dsn for odbc, does the AD server needs to have an instance of
sql server and so on.
for example, if i have a sql2k server call 'sql2ksvr', and an active
directory server called 'adsvr'.
exactly what step by step instructions would be to set it up in a way
that i can query the groups and users information in AD?
thank you guys!!Hi Steve
You linked server should be as is, the open query should specify your
server. See the example at:
http://msdn.microsoft.com/library/d...eneous_data.asp
And the query such as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
You will need to change the IP address (or server name) and thje OU/DC parts
.
This may also help:
http://msdn.microsoft.com/library/d...r />
_94fn.asp
John
"=== Steve L ===" wrote:
> I've seen this linked server syntax many times in the group. but it
> didn't work for me, maybe someone can explain it better for me?
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> i know that:
> * " ADSI" is the server argument, which will be the name of this
> linked server.
> * "Active Directory Services" is the srvproduct argument, which is
> the name of the OLE DB data source that you are adding as a linked
> server.
> * "ADSDSOObject" is the provider_name argument and indicates you
> are using the OLE DB Provider.
> * "adsdatasource" is the data_source argument, which is the name of
> the data source as interpreted by the OLE DB Provider.
>
> my questions are:
> what "ADSDSOObject" and "adsdatasource" should be in my case? do i
> need an dsn for odbc, does the AD server needs to have an instance of
> sql server and so on.
> for example, if i have a sql2k server call 'sql2ksvr', and an active
> directory server called 'adsvr'.
> exactly what step by step instructions would be to set it up in a way
> that i can query the groups and users information in AD?
> thank you guys!!
>|||i ran the following scripts on my sql2k server
script#1
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
go
script#2
SELECT * FROM
OpenQuery( ADSI, 'SELECT ADsPath FROM "LDAP://DC=MyCompany's domain
name,DC=COM" WHERE
objectClass= "user" ')
and i've got the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
is there a way to test if the linked server is working? or is there a
sample folder in AD i can query against with to see where the problems
are?
thank you.|||i finally figured how that works. i have to log into server directly
and run the query. and now i have a new question.
how to modify the following query so i can have the users and groups
they belong to? I just want to find out all the groups and users
relationship.
can some one tell me what fields available? I try to use * but it
didnt' work.
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''group''') b|||Steve how did you finally get it to work because I am still getting that
error message running straight from the SQL Server.
"=== Steve L ===" wrote:
> i finally figured how that works. i have to log into server directly
> and run the query. and now i have a new question.
> how to modify the following query so i can have the users and groups
> they belong to? I just want to find out all the groups and users
> relationship.
> can some one tell me what fields available? I try to use * but it
> didnt' work.
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''group''') b
>
didn't work for me, maybe someone can explain it better for me?
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
i know that:
* " ADSI" is the server argument, which will be the name of this
linked server.
* "Active Directory Services" is the srvproduct argument, which is
the name of the OLE DB data source that you are adding as a linked
server.
* "ADSDSOObject" is the provider_name argument and indicates you
are using the OLE DB Provider.
* "adsdatasource" is the data_source argument, which is the name of
the data source as interpreted by the OLE DB Provider.
my questions are:
what "ADSDSOObject" and "adsdatasource" should be in my case? do i
need an dsn for odbc, does the AD server needs to have an instance of
sql server and so on.
for example, if i have a sql2k server call 'sql2ksvr', and an active
directory server called 'adsvr'.
exactly what step by step instructions would be to set it up in a way
that i can query the groups and users information in AD?
thank you guys!!Hi Steve
You linked server should be as is, the open query should specify your
server. See the example at:
http://msdn.microsoft.com/library/d...eneous_data.asp
And the query such as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
You will need to change the IP address (or server name) and thje OU/DC parts
.
This may also help:
http://msdn.microsoft.com/library/d...r />
_94fn.asp
John
"=== Steve L ===" wrote:
> I've seen this linked server syntax many times in the group. but it
> didn't work for me, maybe someone can explain it better for me?
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> i know that:
> * " ADSI" is the server argument, which will be the name of this
> linked server.
> * "Active Directory Services" is the srvproduct argument, which is
> the name of the OLE DB data source that you are adding as a linked
> server.
> * "ADSDSOObject" is the provider_name argument and indicates you
> are using the OLE DB Provider.
> * "adsdatasource" is the data_source argument, which is the name of
> the data source as interpreted by the OLE DB Provider.
>
> my questions are:
> what "ADSDSOObject" and "adsdatasource" should be in my case? do i
> need an dsn for odbc, does the AD server needs to have an instance of
> sql server and so on.
> for example, if i have a sql2k server call 'sql2ksvr', and an active
> directory server called 'adsvr'.
> exactly what step by step instructions would be to set it up in a way
> that i can query the groups and users information in AD?
> thank you guys!!
>|||i ran the following scripts on my sql2k server
script#1
sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
go
script#2
SELECT * FROM
OpenQuery( ADSI, 'SELECT ADsPath FROM "LDAP://DC=MyCompany's domain
name,DC=COM" WHERE
objectClass= "user" ')
and i've got the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
is there a way to test if the linked server is working? or is there a
sample folder in AD i can query against with to see where the problems
are?
thank you.|||i finally figured how that works. i have to log into server directly
and run the query. and now i have a new question.
how to modify the following query so i can have the users and groups
they belong to? I just want to find out all the groups and users
relationship.
can some one tell me what fields available? I try to use * but it
didnt' work.
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
WHERE objectCategory = ''group''') b|||Steve how did you finally get it to work because I am still getting that
error message running straight from the SQL Server.
"=== Steve L ===" wrote:
> i finally figured how that works. i have to log into server directly
> and run the query. and now i have a new question.
> how to modify the following query so i can have the users and groups
> they belong to? I just want to find out all the groups and users
> relationship.
> can some one tell me what fields available? I try to use * but it
> didnt' work.
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://shiva/ DC=MyCompany's Domain,DC=com''
> WHERE objectCategory = ''group''') b
>
Wednesday, March 28, 2012
How much time does a query take?
Hello!
First of all, please excuse my (probably) bad english! :-/
My question is, how much time goes by until a query to a database is
finished? Is it fast enough for a query every 0.5 second?
In my application it's not usual that every 0.5 to 1 second a query is sent,
but it can happen.
I hope you can understand my concern and maybe can help me!
Greetings,
Christian
Am Fri, 10 Mar 2006 09:07:51 +0100 schrieb chris.lo:
> Hello!
> First of all, please excuse my (probably) bad english! :-/
> My question is, how much time goes by until a query to a database is
> finished? Is it fast enough for a query every 0.5 second?
> In my application it's not usual that every 0.5 to 1 second a query is sent,
> but it can happen.
> I hope you can understand my concern and maybe can help me!
> Greetings,
> Christian
Do you know what you want to ask? We have no information about the hardware
you use, how many users at the same time, how much data you have, what
queries are executed against the database, which funktions, triggers,
stored procedures are involved ...
So i can only say: if you use the same hardware as i and the same queries,
then it is fast enough :-)
bye, Helmut
|||Hello Helmut!
Of course you are right! I can't expect accurate answers without accurate
details!
So, here we are:
The MSDE is running on a P4 with 1.6 GHz and 512 MB Ram. On this PC there's
not only the MSDE running, but also a visualisation for a part of a
production plant. We plan to install a extra PC only for the MSDE, but that
depends on our customer...
At the moment there can be 10 users max "online". The amount of data depends
on the different Views which the user can select. I can't tell you that
amount in detail...
> which funktions, triggers, stored procedures are involved ...
I'm not THAT experienced in databases, but I can tell you that we didn't use
that things. (maybe the system uses that automatically?!)
Well, alltogether I can't make a proper description of what is happening in
detail. Maybe I tell you what I'm planning to do and maybe then you can tell
me whether is it possible or not:
Our application receives Messages from several PLCs. This messages tell our
app some info about the plant the PLC is controlling. Mostly the messages
tell our app when a fault (in the plant) occured und when it went away. Also
with this messages the app can count the produced parts (parts of a car) and
how long every single part took to be finished.
The message is represented by a 32-Bit number, an additional timestamp a
some flags. This numbers are not very informative, of course. So our
application has to lookup this number in a list and pick out the appropriate
description. This description can be seen by the user.
So far, so good. This mechanism is allready implemented in our application,
but I don't like it because it's extremely inflexible. So I plan to do it a
different way. I wanted to create the list with the numbers and description
as a table in the database. And every time a message was received from a
PLC, the app would execute a query against the database to retrieve the
description.
Like I wrote in my first question, in that way there can be a query every
0.5 second. And now I wonder whether this method is practicable or not. I
have concerns that this way will maybe slow down the whole application...
What do you thing? Should I do this? Maybe you wonder why I don't change the
mechanism in our app itself. Well, that belongs to internal functionality,
to which I don't have access.
I'm looking forward to some helpfull informations or opinions.
Greatings,
Chris
"helmut woess" <hw@.iis.at> schrieb im Newsbeitrag
news:byh92sdxklyh$.bcfu0dq0a5y9$.dlg@.40tude.net...
> Am Fri, 10 Mar 2006 09:07:51 +0100 schrieb chris.lo:
>
> Do you know what you want to ask? We have no information about the
> hardware
> you use, how many users at the same time, how much data you have, what
> queries are executed against the database, which funktions, triggers,
> stored procedures are involved ...
> So i can only say: if you use the same hardware as i and the same queries,
> then it is fast enough :-)
> bye, Helmut
|||hi,
chris.lo wrote:
> The message is represented by a 32-Bit number, an additional
> timestamp a some flags. This numbers are not very informative, of
> course. So our application has to lookup this number in a list and
> pick out the appropriate description. This description can be seen by
> the user.
> So far, so good. This mechanism is allready implemented in our
> application, but I don't like it because it's extremely inflexible.
> So I plan to do it a different way. I wanted to create the list with
> the numbers and description as a table in the database. And every
> time a message was received from a PLC, the app would execute a query
> against the database to retrieve the description.
> Like I wrote in my first question, in that way there can be a query
> every 0.5 second. And now I wonder whether this method is practicable or
> not. I have concerns that this way will maybe slow down the whole
> application...
I do not know your actual schema, but your words seems to tell you actually
have a normalized one...
personally I'd not make a lookup every time a message is received, as you
probably already have all needed pieces of information to lookup only at
"reporting time" whenever it is performed/needed.. without that lookup you
preserve your schema in a better normalized manner and you preserve system
resources..
when reports are needed, you can perform your join and present data in the
human readeble way as all encoded values will be there resolved to their
human "descriprions"...
if you keep your batch operations as little as possible, you should decrease
possibility to hit the internal MSDE limit of 8 (5 + 3 system batches)
concurrent workloads, but, more important, you keep a normalized schema
where your foreign key constraints are resolved only on a "when needed"
basis..
obviously, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Chris,
there are still many dark horses and i think it is not possible to give you
a warranty that this will work or will surely not work.
A dangerous part is MSDE and 10 concurrent users. You can have 10
concurrent users, but if some requests arrives nearly at the same time,
then MSDE starts to insert wait cycles and then it comes very fast to a
bigger delay.
This has gone with SQLExpress, so if you have only MSDE and no real
SQL-Server then it would be better to use SQLExpress.
And because visualisation can be very ressource intensive, i would not do
both on the same machine.
My suggestion: a new PC with 1 Gb Memory and a fast harddrive, Windows 2K
as OS and SQLExpress. Under normal circumstances this should be enough.
bye,
Helmut
|||Hello!
Thank you both for your answers und opinions! I think I simply will test it,
and then I can decide whether I will do it that way or not...
Greetings,
Christian
First of all, please excuse my (probably) bad english! :-/
My question is, how much time goes by until a query to a database is
finished? Is it fast enough for a query every 0.5 second?
In my application it's not usual that every 0.5 to 1 second a query is sent,
but it can happen.
I hope you can understand my concern and maybe can help me!
Greetings,
Christian
Am Fri, 10 Mar 2006 09:07:51 +0100 schrieb chris.lo:
> Hello!
> First of all, please excuse my (probably) bad english! :-/
> My question is, how much time goes by until a query to a database is
> finished? Is it fast enough for a query every 0.5 second?
> In my application it's not usual that every 0.5 to 1 second a query is sent,
> but it can happen.
> I hope you can understand my concern and maybe can help me!
> Greetings,
> Christian
Do you know what you want to ask? We have no information about the hardware
you use, how many users at the same time, how much data you have, what
queries are executed against the database, which funktions, triggers,
stored procedures are involved ...
So i can only say: if you use the same hardware as i and the same queries,
then it is fast enough :-)
bye, Helmut
|||Hello Helmut!
Of course you are right! I can't expect accurate answers without accurate
details!
So, here we are:
The MSDE is running on a P4 with 1.6 GHz and 512 MB Ram. On this PC there's
not only the MSDE running, but also a visualisation for a part of a
production plant. We plan to install a extra PC only for the MSDE, but that
depends on our customer...
At the moment there can be 10 users max "online". The amount of data depends
on the different Views which the user can select. I can't tell you that
amount in detail...
> which funktions, triggers, stored procedures are involved ...
I'm not THAT experienced in databases, but I can tell you that we didn't use
that things. (maybe the system uses that automatically?!)
Well, alltogether I can't make a proper description of what is happening in
detail. Maybe I tell you what I'm planning to do and maybe then you can tell
me whether is it possible or not:
Our application receives Messages from several PLCs. This messages tell our
app some info about the plant the PLC is controlling. Mostly the messages
tell our app when a fault (in the plant) occured und when it went away. Also
with this messages the app can count the produced parts (parts of a car) and
how long every single part took to be finished.
The message is represented by a 32-Bit number, an additional timestamp a
some flags. This numbers are not very informative, of course. So our
application has to lookup this number in a list and pick out the appropriate
description. This description can be seen by the user.
So far, so good. This mechanism is allready implemented in our application,
but I don't like it because it's extremely inflexible. So I plan to do it a
different way. I wanted to create the list with the numbers and description
as a table in the database. And every time a message was received from a
PLC, the app would execute a query against the database to retrieve the
description.
Like I wrote in my first question, in that way there can be a query every
0.5 second. And now I wonder whether this method is practicable or not. I
have concerns that this way will maybe slow down the whole application...
What do you thing? Should I do this? Maybe you wonder why I don't change the
mechanism in our app itself. Well, that belongs to internal functionality,
to which I don't have access.
I'm looking forward to some helpfull informations or opinions.
Greatings,
Chris
"helmut woess" <hw@.iis.at> schrieb im Newsbeitrag
news:byh92sdxklyh$.bcfu0dq0a5y9$.dlg@.40tude.net...
> Am Fri, 10 Mar 2006 09:07:51 +0100 schrieb chris.lo:
>
> Do you know what you want to ask? We have no information about the
> hardware
> you use, how many users at the same time, how much data you have, what
> queries are executed against the database, which funktions, triggers,
> stored procedures are involved ...
> So i can only say: if you use the same hardware as i and the same queries,
> then it is fast enough :-)
> bye, Helmut
|||hi,
chris.lo wrote:
> The message is represented by a 32-Bit number, an additional
> timestamp a some flags. This numbers are not very informative, of
> course. So our application has to lookup this number in a list and
> pick out the appropriate description. This description can be seen by
> the user.
> So far, so good. This mechanism is allready implemented in our
> application, but I don't like it because it's extremely inflexible.
> So I plan to do it a different way. I wanted to create the list with
> the numbers and description as a table in the database. And every
> time a message was received from a PLC, the app would execute a query
> against the database to retrieve the description.
> Like I wrote in my first question, in that way there can be a query
> every 0.5 second. And now I wonder whether this method is practicable or
> not. I have concerns that this way will maybe slow down the whole
> application...
I do not know your actual schema, but your words seems to tell you actually
have a normalized one...
personally I'd not make a lookup every time a message is received, as you
probably already have all needed pieces of information to lookup only at
"reporting time" whenever it is performed/needed.. without that lookup you
preserve your schema in a better normalized manner and you preserve system
resources..
when reports are needed, you can perform your join and present data in the
human readeble way as all encoded values will be there resolved to their
human "descriprions"...
if you keep your batch operations as little as possible, you should decrease
possibility to hit the internal MSDE limit of 8 (5 + 3 system batches)
concurrent workloads, but, more important, you keep a normalized schema
where your foreign key constraints are resolved only on a "when needed"
basis..
obviously, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Chris,
there are still many dark horses and i think it is not possible to give you
a warranty that this will work or will surely not work.
A dangerous part is MSDE and 10 concurrent users. You can have 10
concurrent users, but if some requests arrives nearly at the same time,
then MSDE starts to insert wait cycles and then it comes very fast to a
bigger delay.
This has gone with SQLExpress, so if you have only MSDE and no real
SQL-Server then it would be better to use SQLExpress.
And because visualisation can be very ressource intensive, i would not do
both on the same machine.
My suggestion: a new PC with 1 Gb Memory and a fast harddrive, Windows 2K
as OS and SQLExpress. Under normal circumstances this should be enough.
bye,
Helmut
|||Hello!
Thank you both for your answers und opinions! I think I simply will test it,
and then I can decide whether I will do it that way or not...
Greetings,
Christian
Monday, March 26, 2012
how much overhead with query notification services in sqlserer2005?
I notice a lot of activity on my sqlserver2005 box when nothing is
happening. (just query notificatins which I have set up) . I can't
find any write ups on this. To me, it seems
important.
thanks
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netQuery notifications utilizes the Service Broker infrastructure internally to
handle the automatic activation of the notification when something changes.
This is where the activity is coming from. Query notifications were
primarily designed to allow you to maintain relatively static caches of
data. You don't want to use them in highly volitile data sets or with high
volume systems, because the notification mechanism can get overwhelmed by
the pace of changes in the environment.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:p6l442tbga971ustvm4dg2u583ab864hlt@.
4ax.com...
>I notice a lot of activity on my sqlserver2005 box when nothing is
> happening. (just query notificatins which I have set up) . I can't
> find any write ups on this. To me, it seems
> important.
> thanks
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Sun, 16 Apr 2006 09:57:17 -0500, "Michael Hotek"
<mike@.solidqualitylearning.com> wrote:
>Query notifications utilizes the Service Broker infrastructure internally t
o
>handle the automatic activation of the notification when something changes.
>This is where the activity is coming from. Query notifications were
>primarily designed to allow you to maintain relatively static caches of
>data. You don't want to use them in highly volitile data sets or with high
>volume systems, because the notification mechanism can get overwhelmed by
>the pace of changes in the environment.
thanks Mike. I understand now. -Peter
Peter Kellner
http://peterkellner.netsql
happening. (just query notificatins which I have set up) . I can't
find any write ups on this. To me, it seems
important.
thanks
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netQuery notifications utilizes the Service Broker infrastructure internally to
handle the automatic activation of the notification when something changes.
This is where the activity is coming from. Query notifications were
primarily designed to allow you to maintain relatively static caches of
data. You don't want to use them in highly volitile data sets or with high
volume systems, because the notification mechanism can get overwhelmed by
the pace of changes in the environment.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:p6l442tbga971ustvm4dg2u583ab864hlt@.
4ax.com...
>I notice a lot of activity on my sqlserver2005 box when nothing is
> happening. (just query notificatins which I have set up) . I can't
> find any write ups on this. To me, it seems
> important.
> thanks
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Sun, 16 Apr 2006 09:57:17 -0500, "Michael Hotek"
<mike@.solidqualitylearning.com> wrote:
>Query notifications utilizes the Service Broker infrastructure internally t
o
>handle the automatic activation of the notification when something changes.
>This is where the activity is coming from. Query notifications were
>primarily designed to allow you to maintain relatively static caches of
>data. You don't want to use them in highly volitile data sets or with high
>volume systems, because the notification mechanism can get overwhelmed by
>the pace of changes in the environment.
thanks Mike. I understand now. -Peter
Peter Kellner
http://peterkellner.netsql
how much overhead with query notification services in sqlserer2005?
I notice a lot of activity on my sqlserver2005 box when nothing is
happening. (just query notificatins which I have set up) . I can't
find any write ups on this. To me, it seems
important.
thanks
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netQuery notifications utilizes the Service Broker infrastructure internally to
handle the automatic activation of the notification when something changes.
This is where the activity is coming from. Query notifications were
primarily designed to allow you to maintain relatively static caches of
data. You don't want to use them in highly volitile data sets or with high
volume systems, because the notification mechanism can get overwhelmed by
the pace of changes in the environment.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:p6l442tbga971ustvm4dg2u583ab864hlt@.4ax.com...
>I notice a lot of activity on my sqlserver2005 box when nothing is
> happening. (just query notificatins which I have set up) . I can't
> find any write ups on this. To me, it seems
> important.
> thanks
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Sun, 16 Apr 2006 09:57:17 -0500, "Michael Hotek"
<mike@.solidqualitylearning.com> wrote:
>Query notifications utilizes the Service Broker infrastructure internally to
>handle the automatic activation of the notification when something changes.
>This is where the activity is coming from. Query notifications were
>primarily designed to allow you to maintain relatively static caches of
>data. You don't want to use them in highly volitile data sets or with high
>volume systems, because the notification mechanism can get overwhelmed by
>the pace of changes in the environment.
thanks Mike. I understand now. -Peter
Peter Kellner
http://peterkellner.net
happening. (just query notificatins which I have set up) . I can't
find any write ups on this. To me, it seems
important.
thanks
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netQuery notifications utilizes the Service Broker infrastructure internally to
handle the automatic activation of the notification when something changes.
This is where the activity is coming from. Query notifications were
primarily designed to allow you to maintain relatively static caches of
data. You don't want to use them in highly volitile data sets or with high
volume systems, because the notification mechanism can get overwhelmed by
the pace of changes in the environment.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:p6l442tbga971ustvm4dg2u583ab864hlt@.4ax.com...
>I notice a lot of activity on my sqlserver2005 box when nothing is
> happening. (just query notificatins which I have set up) . I can't
> find any write ups on this. To me, it seems
> important.
> thanks
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Sun, 16 Apr 2006 09:57:17 -0500, "Michael Hotek"
<mike@.solidqualitylearning.com> wrote:
>Query notifications utilizes the Service Broker infrastructure internally to
>handle the automatic activation of the notification when something changes.
>This is where the activity is coming from. Query notifications were
>primarily designed to allow you to maintain relatively static caches of
>data. You don't want to use them in highly volitile data sets or with high
>volume systems, because the notification mechanism can get overwhelmed by
>the pace of changes in the environment.
thanks Mike. I understand now. -Peter
Peter Kellner
http://peterkellner.net
Friday, March 23, 2012
How many rows has been affected?
hi everyone,
I’d like to know how many rows has been affected for this query. But I don’t want to use aprevious Sql Task style “SELECT COUNT(*) REGISTROS FROM…”
Such a query like that:
INSERT INTO TABLEDEST
SELECT F1,F2,
FROM TABLESOURCE
WHERE <CONDITION>
I’d like to retrieve that value from a later Script Task
Thanks a lot for your help and thoughts,
Moved from SSIS to t-sql forum...|||I don't know about getting it from a later script task, but you could get it from the same script task pretty easily. You could add a SELECT @.@.ROWCOUNT after your insert statement and then catch the result in a variable.|||I agree. Use @.@.rowcount to get the rows affected, but getting it into a SSIS variable (or something similar) is beyond this forum. If you need to know that we can bounce it back to the SSIS forum
|||Thanks for that JayH. It was very useful.
Monday, March 12, 2012
How make Query Analyzer remember to be maximized?
First, if this isn't the appropriate group for this type of question,
please let me know which one is.
I'm running SQL 2000. When I start Query Analyzer, it always bring up
the query window in a non-maximized state, even though I always maximize
it each time. Is there any way to make QA remember that I want the
window to start up maximized? I know it isn't the biggest deal in the
world, but it's an irritation. Thanks for any help. p
** Posted from http://www.teranews.com **"Rav" wrote:
> First, if this isn't the appropriate group for this type of question,
> please let me know which one is.
> I'm running SQL 2000. When I start Query Analyzer, it always bring up
> the query window in a non-maximized state, even though I always maximize
> it each time. Is there any way to make QA remember that I want the
> window to start up maximized? I know it isn't the biggest deal in the
> world, but it's an irritation. Thanks for any help. p
> ** Posted from http://www.teranews.com **
>
Hi Rav
On my system (XP sp2) it will open in the same state as it was last closed.
If you always want to start a program maximized you can change the
properties on the start menu. Right clcik the Query Analyser and you will get
the properties dialog, change to the shortcut tab and you will see the run
option which has the options of "Normal window", "Maximized" or "Minimized".
Choose "Maximized" to always start as maximized.
John|||I don't think there's a way you can do to maximize Query Window everytime
you open QA.
--
Ekrem Önsoy
"Rav" <PaulR@.cais.com> wrote in message
news:ee0c8$480e048a$26520@.news.teranews.com...
> First, if this isn't the appropriate group for this type of question,
> please let me know which one is.
> I'm running SQL 2000. When I start Query Analyzer, it always bring up the
> query window in a non-maximized state, even though I always maximize it
> each time. Is there any way to make QA remember that I want the window to
> start up maximized? I know it isn't the biggest deal in the world, but
> it's an irritation. Thanks for any help. p
> ** Posted from http://www.teranews.com **|||Hi John,
I think OP asks for the Query Window in the QA, not the Query Analyzer
itself. You know, the Query Window always opens in a not maximized state.
But if you close the QA as maximized then when you open it again it opens
maximized... But the Query Window does not. This must be the one which
bothers the OP =)
--
Ekrem Ã?nsoy
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AF2788B2-BC2A-411D-A75B-AEB91C60860F@.microsoft.com...
> "Rav" wrote:
>> First, if this isn't the appropriate group for this type of question,
>> please let me know which one is.
>> I'm running SQL 2000. When I start Query Analyzer, it always bring up
>> the query window in a non-maximized state, even though I always maximize
>> it each time. Is there any way to make QA remember that I want the
>> window to start up maximized? I know it isn't the biggest deal in the
>> world, but it's an irritation. Thanks for any help. p
>> ** Posted from http://www.teranews.com **
> Hi Rav
> On my system (XP sp2) it will open in the same state as it was last
> closed.
> If you always want to start a program maximized you can change the
> properties on the start menu. Right clcik the Query Analyser and you will
> get
> the properties dialog, change to the shortcut tab and you will see the run
> option which has the options of "Normal window", "Maximized" or
> "Minimized".
> Choose "Maximized" to always start as maximized.
> John|||Ekrem Ã?nsoy wrote:
> Hi John,
> I think OP asks for the Query Window in the QA, not the Query Analyzer
> itself. You know, the Query Window always opens in a not maximized
> state. But if you close the QA as maximized then when you open it again
> it opens maximized... But the Query Window does not. This must be the
> one which bothers the OP =)
>
Correct, Ekrem. The Query Analyzer window opens maximized just fine.
It's the initial Query window contained with that that doesn't remember
to open maximized. I saw your separate message saying that you don't
think there's a way to maximize the Query window each time I open QA.
Oh well. Thanks for the response (and to John also). p
.
** Posted from http://www.teranews.com **
please let me know which one is.
I'm running SQL 2000. When I start Query Analyzer, it always bring up
the query window in a non-maximized state, even though I always maximize
it each time. Is there any way to make QA remember that I want the
window to start up maximized? I know it isn't the biggest deal in the
world, but it's an irritation. Thanks for any help. p
** Posted from http://www.teranews.com **"Rav" wrote:
> First, if this isn't the appropriate group for this type of question,
> please let me know which one is.
> I'm running SQL 2000. When I start Query Analyzer, it always bring up
> the query window in a non-maximized state, even though I always maximize
> it each time. Is there any way to make QA remember that I want the
> window to start up maximized? I know it isn't the biggest deal in the
> world, but it's an irritation. Thanks for any help. p
> ** Posted from http://www.teranews.com **
>
Hi Rav
On my system (XP sp2) it will open in the same state as it was last closed.
If you always want to start a program maximized you can change the
properties on the start menu. Right clcik the Query Analyser and you will get
the properties dialog, change to the shortcut tab and you will see the run
option which has the options of "Normal window", "Maximized" or "Minimized".
Choose "Maximized" to always start as maximized.
John|||I don't think there's a way you can do to maximize Query Window everytime
you open QA.
--
Ekrem Önsoy
"Rav" <PaulR@.cais.com> wrote in message
news:ee0c8$480e048a$26520@.news.teranews.com...
> First, if this isn't the appropriate group for this type of question,
> please let me know which one is.
> I'm running SQL 2000. When I start Query Analyzer, it always bring up the
> query window in a non-maximized state, even though I always maximize it
> each time. Is there any way to make QA remember that I want the window to
> start up maximized? I know it isn't the biggest deal in the world, but
> it's an irritation. Thanks for any help. p
> ** Posted from http://www.teranews.com **|||Hi John,
I think OP asks for the Query Window in the QA, not the Query Analyzer
itself. You know, the Query Window always opens in a not maximized state.
But if you close the QA as maximized then when you open it again it opens
maximized... But the Query Window does not. This must be the one which
bothers the OP =)
--
Ekrem Ã?nsoy
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AF2788B2-BC2A-411D-A75B-AEB91C60860F@.microsoft.com...
> "Rav" wrote:
>> First, if this isn't the appropriate group for this type of question,
>> please let me know which one is.
>> I'm running SQL 2000. When I start Query Analyzer, it always bring up
>> the query window in a non-maximized state, even though I always maximize
>> it each time. Is there any way to make QA remember that I want the
>> window to start up maximized? I know it isn't the biggest deal in the
>> world, but it's an irritation. Thanks for any help. p
>> ** Posted from http://www.teranews.com **
> Hi Rav
> On my system (XP sp2) it will open in the same state as it was last
> closed.
> If you always want to start a program maximized you can change the
> properties on the start menu. Right clcik the Query Analyser and you will
> get
> the properties dialog, change to the shortcut tab and you will see the run
> option which has the options of "Normal window", "Maximized" or
> "Minimized".
> Choose "Maximized" to always start as maximized.
> John|||Ekrem Ã?nsoy wrote:
> Hi John,
> I think OP asks for the Query Window in the QA, not the Query Analyzer
> itself. You know, the Query Window always opens in a not maximized
> state. But if you close the QA as maximized then when you open it again
> it opens maximized... But the Query Window does not. This must be the
> one which bothers the OP =)
>
Correct, Ekrem. The Query Analyzer window opens maximized just fine.
It's the initial Query window contained with that that doesn't remember
to open maximized. I saw your separate message saying that you don't
think there's a way to maximize the Query window each time I open QA.
Oh well. Thanks for the response (and to John also). p
.
** Posted from http://www.teranews.com **
how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?
I am try to start with SQL BROKER service,
When I lunch from sql Management studio the following query, this don't finish never.
ALTER DATATABLE dbname SET ENABLE_BROKER
Where I am mistaking ?
I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx
Essentially:
I assume you mean ALTER DATABASE.
ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.
HTH,
~ Remus
|||
Holas!
How can I close all the sessions using a query?
Saludos,
|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||Thanks Ramus,
Saludos,
|||thank you for your best guidance that was very useful for me to solve my problem.
regard you
mojgan
how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?
I am try to start with SQL BROKER service,
When I lunch from sql Management studio the following query, this don't finish never.
ALTER DATATABLE dbname SET ENABLE_BROKER
Where I am mistaking ?
I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx
Essentially:
I assume you mean ALTER DATABASE.
ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.
HTH,
~ Remus
|||
Holas!
How can I close all the sessions using a query?
Saludos,
|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||Thanks Ramus,
Saludos,
|||thank you for your best guidance that was very useful for me to solve my problem.
regard you
mojgan
how long need ALTER DATATABLE (DATABASE) SET ENABLE_BROKER ?
I am try to start with SQL BROKER service,
When I lunch from sql Management studio the following query, this don't finish never.
ALTER DATATABLE dbname SET ENABLE_BROKER
Where I am mistaking ?
I've written some article about SB here: http://www.dotnetfun.com/Articles/sql/sql2005/SQL2005ServiceBrokerProblems.aspx
Essentially:
I assume you mean ALTER DATABASE.
ALTER DATABSE dbname SET ENABLE_BROKER requires an exclusive lock on the database. Any session using that database has a shared lock on it, thus bloking the ALTER. So make sure you close (or switch to another database context) all sessions.
HTH,
~ Remus
|||
Holas!
How can I close all the sessions using a query?
Saludos,
|||You can use the WITH ROLLBACK IMMEDIATE clause of the ALTER DATABASE to force the close off conflicting sessions|||Thanks Ramus,
Saludos,
|||thank you for your best guidance that was very useful for me to solve my problem.
regard you
mojgan
|||Hi there,I faced the same problem you have mentioned.
Then, I found a simple solution. You need to use the following 2 statements together, and the query will finish in no time:
Use NorthWind
alter database NorthWind SET ENABLE_BROKER
Friday, March 9, 2012
How long does a query plan stay in the procedure cache?
How long does a a query plan stay in the procedure cache? Is there someway
we can reload a specific sp in SQL Server is restarted? TIADepends on the cost associated with creating the plan as well as how often
the sproc is run. Yes you can configure the sproc to run each time the SQL
Server is started by using the sp_procoption system stored procedure (see
BOL for details).
HTH
J
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>|||Hi,
The most common reasons why a query execution plan is flushed from procedure
cache are:
1. There is not enough internal memory, or the query is not used frequently.
This causes the memory manager to assign the memory to other
procedure to be moved out from cache.You can try to run the query from time
to time.
2. Statistics are updated. When column statistics or index statistics are
updated, all query plans that use those tables will be discarded.
You can turn off auto-update statistics, and schedule UPDATE STATISTICS
yourself. After that you would need to run the query again in order to
can a compiled query plan
3. Schema changes. If the data structure is changed, indexes are added or
deleted, etc., then the related queries are marked for recompile.
Is there some way we can reload a specific sp in SQL Server is restarted?
We can put the procedure in startup . See sp_procoption system procedure in
books online.
Thanks
Hari
SQL Server MVP
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>
we can reload a specific sp in SQL Server is restarted? TIADepends on the cost associated with creating the plan as well as how often
the sproc is run. Yes you can configure the sproc to run each time the SQL
Server is started by using the sp_procoption system stored procedure (see
BOL for details).
HTH
J
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>|||Hi,
The most common reasons why a query execution plan is flushed from procedure
cache are:
1. There is not enough internal memory, or the query is not used frequently.
This causes the memory manager to assign the memory to other
procedure to be moved out from cache.You can try to run the query from time
to time.
2. Statistics are updated. When column statistics or index statistics are
updated, all query plans that use those tables will be discarded.
You can turn off auto-update statistics, and schedule UPDATE STATISTICS
yourself. After that you would need to run the query again in order to
can a compiled query plan
3. Schema changes. If the data structure is changed, indexes are added or
deleted, etc., then the related queries are marked for recompile.
Is there some way we can reload a specific sp in SQL Server is restarted?
We can put the procedure in startup . See sp_procoption system procedure in
books online.
Thanks
Hari
SQL Server MVP
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>
how link executionid of sysdtslog90
I have enabled logging using database.
When I try to query the sysdtslog90 table using System::ExecutionInstanceGUID to link executionid - it is not matching.
Any resolution?
Thanks
The variable System::ExecutionInstanceGUID always matches the sysdtslog90.executionid column when I try it.
Are the times matching up? Are you looking in the right table? Try using a Script Task to fire an event with the ExecutionInstanceGUID in the description text, so that it is logged and you can compare the two values in the same row.
|||It worked when I pass the System::ExecutionInstanceGUID as VARCHAR parameter instead of GUID in SQL Task parameter mapping. And when the stored procedure recieves the same parameter as uniqueidentifier
sorry for my late response.
Thanks
How is the metadata of the result of a UNION determined?
Hi,
A colleague and I have just found a slightly strange situation that we don't understand.
We had (effectively) the following query:
select cast(1 as decimal(38,10))
union all
select cast(1 as decimal(38,4))
And the result contained 2 rows, each with a a scale of 4. This surprised us, we expected that the metadata of the result would be determined by the topmost query.
So we reversed them and tried this:
select cast(1 as decimal(38,4))
union all
select cast(1 as decimal(38,10))
and got exactly the same result. 2 rows with a scale of 4.
We can't understand why the scale always gets determined to be 4 regardless of the order of the queries.
Any explanation would be much appreciated!
Thanks
Jamie
Doesn't matter. The answer's here: http://msdn2.microsoft.com/en-us/library/ms190476.aspx
-Jamie
|||In addition to the "Precision, scale and length" topic, the UNION operator topic also documents how the data conversions happens if the various SELECT statements contain columns with different data types. See link below for more details:
http://msdn2.microsoft.com/en-us/library/ms180026(SQL.90).aspx
Wednesday, March 7, 2012
How is it possible that inserted and deleted are both empty in trigger?
I created manage update trigger to react on one column changes. There is an application which is working with DB, so I don't have access to SQL query which changes this column. In most cases trigger works fine, but in one case when this column changes, trigger is fired and IsUpdatedColumn is true for this column, but both inserted and deleted table are empty, so I can't get new value for this column. Any idea why is it happened? Is any way around?
This column type is uniqueidentifier. Inserted and deleted tables are empty when application is changing value from NULL to not null value, but if I change it myself from Management Studio inserted table contains right values. Most like problem is in query which is changing that value.
I'm doing that on Sql Server 2005.
Triggers are also firing when no rows is affected, it is fired on a per statement basis not per row.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Friday, February 24, 2012
How I get the random row from the table?
When I execute the following query several times, I get the same row if there is no new data inserted:
SELECT TOP 1 * FROM TableName
Is there a way to get a random row from the table? Thanks in advance.Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from table
where id = @.value|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think. You would have to make a while-loop and check if the ID exists I belive, and then loop for each ID that doesn't exist. have never done this myself so there might be a better way...|||The only issue with that is that you have to have an unique integer for your ID. Additionally your ID can not have gaps and must start at one (or is it zero).
Perhaps something like this??
----
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
executesql 'select top ' + @.value + ' from table order by XYZ'
----
then you need to select the first one or last one that is selected...
I don't know how you would do it though...
HTH|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think
Providing the id column is unique, then
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||Unfortunately I can't test this out on the machine I am on, for the above example wouldn't you need a group by clause in your select count(*) and a having instead of a where clause...
so something like...
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id group by t1.id) AS ID2
from table t1) v
having v.id2 = @.value
once again, sorry I can't check it.|||No aggregate functions have been computed on the set 'V', meaning that the group by and having clauses are not required.
Consider,
Select a, b, (select count(*) from table) AS COUNT
from table t1
group by a, b
This is invalid as COUNT is interpreted as a column as opposed to an aggregate function of t1.|||Okie cool. :) Like I said, I couldn't check so. ;)
It's an interesting problem though... personally I wouldn't try and get the database to do this...
I'd get the app to generate a random id to select and just do a standard query on that id...
Each to their own though. :)|||Thank you for your all helps.
Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456
Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
How do you think about it?|||Try this and see:
SELECT TOP 1 * FROM TableName
order by newid()|||Originally posted by gyuan
Thank you for your all helps.
Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456
Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
How do you think about it?
The code above will still encounter problems with the gaps and the not starting at zero...
this is the one you want
Originally posted by r123456
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||The problem you get with this solution
Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
is say you have 5000 records and you delete 4000 records.
Your rand value will be between 1 and 4000 but your max UserId is 5000 anything with an Id over 4000 is pretty much unreachable...
I think you'd be better with this...
Declare @.value As Integer
SET @.value = (RAND() * (SELECT max(UserID) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
It would mean when gaps occur the row after the gap would be hit more often, but atleast you would cover your entire collection of rows.
Hope that makes sense.|||quote:
------------------------
Originally posted by r123456
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value
------------------------
What does columns stand for in the query?|||errr...won't my simple statement solve it?
SELECT TOP 1 * FROM TableName
order by newid()
I don't get it...|||the columns you want to select eg * or username, firstname, lastname etc...|||Hi Patrick,
I'm really not sure how your solution would work, what is the newid()?|||Its a build in command specifically to be use to "select random rows".
It is also use as a comand to auto gen Unique Identifier ids.
But when use in "order by newid()" it generates random rows.
Try it out and see...it works...unless..well theres something I'm missing in the whole discussion.|||Originally posted by Patrick Chua
errr...won't my simple statement solve it?
SELECT TOP 1 * FROM TableName
order by newid()
I don't get it...
It works too, but it takes 2 seconds, a little slower. Now I know the function NewID() and it helps. Thanks.|||rokslide,
That is a good idea to replace Count(*) to MAX(UserID). Thanks.|||Cool, I think I'll have to have a look into that one some time. :)
Thanks Patrick. :)|||No worries gyuan, please note though that (as I said above) it's not truely random.|||Correct.
Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".
If TOP * 1 is used in conjunction with max(id) then only the first value past a "gap" value will be returned, should @.value be equal to a "gap" value.|||I think Patrick's query is better:
SELECT TOP 1 *
FROM Users
ORDER BY NewID()
although the running time is a little longer.|||I've used Patrick's method in the past with success.|||Isn't anyone going to ask WHY do you want to do this?|||I use Patricks method daily and it works out great for me.
NewID() takes a little longer due to it generates a GUID but it is truely random.|||Originally posted by Brett Kaiser
Isn't anyone going to ask WHY do you want to do this?
Brett ...
You are always after the "why" instead of the "how"? I like you for the spirit coz I believe "Prevention is better than cure".|||How's easy.....
And thanks...
Seen to many rocket ships built...|||I have to admit to orginally thinking why would you want to, but I have seen a few "scuffles" break out on here over the "why" of things so I decided to leave it alone.
Then of course the curiosity took over and I started to think,... hey, how would you do that...
anyhow...
r123456
with this statement...
Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".
but if you use count and then compare count to the ID value you are going to completely miss some sections of the data entirely (eg. they will never have a chance to be selected). See the example that I noted eariler. The the max(id) option atleast you cover your entire span of data.
Of course Patrick's solution will cover everything perfectly so....|||Not true.
ID | ID2
1 1
2 2
3 3
4 4
5 5
Delete from table where id=2 OR id=3;
ID | ID2
1 1
4 2
5 3
You have two solutions. One of which requires very little code and an SQL Server function. The other requires a unique id, which for example can be the value of ROWID for an Oracle database.|||Ah yes, sorry, I forgot the second version of your solution with the new ID. :)
My fault entirely.
SELECT TOP 1 * FROM TableName
Is there a way to get a random row from the table? Thanks in advance.Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from table
where id = @.value|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think. You would have to make a while-loop and check if the ID exists I belive, and then loop for each ID that doesn't exist. have never done this myself so there might be a better way...|||The only issue with that is that you have to have an unique integer for your ID. Additionally your ID can not have gaps and must start at one (or is it zero).
Perhaps something like this??
----
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
executesql 'select top ' + @.value + ' from table order by XYZ'
----
then you need to select the first one or last one that is selected...
I don't know how you would do it though...
HTH|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think
Providing the id column is unique, then
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||Unfortunately I can't test this out on the machine I am on, for the above example wouldn't you need a group by clause in your select count(*) and a having instead of a where clause...
so something like...
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id group by t1.id) AS ID2
from table t1) v
having v.id2 = @.value
once again, sorry I can't check it.|||No aggregate functions have been computed on the set 'V', meaning that the group by and having clauses are not required.
Consider,
Select a, b, (select count(*) from table) AS COUNT
from table t1
group by a, b
This is invalid as COUNT is interpreted as a column as opposed to an aggregate function of t1.|||Okie cool. :) Like I said, I couldn't check so. ;)
It's an interesting problem though... personally I wouldn't try and get the database to do this...
I'd get the app to generate a random id to select and just do a standard query on that id...
Each to their own though. :)|||Thank you for your all helps.
Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456
Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
How do you think about it?|||Try this and see:
SELECT TOP 1 * FROM TableName
order by newid()|||Originally posted by gyuan
Thank you for your all helps.
Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456
Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
How do you think about it?
The code above will still encounter problems with the gaps and the not starting at zero...
this is the one you want
Originally posted by r123456
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||The problem you get with this solution
Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
is say you have 5000 records and you delete 4000 records.
Your rand value will be between 1 and 4000 but your max UserId is 5000 anything with an Id over 4000 is pretty much unreachable...
I think you'd be better with this...
Declare @.value As Integer
SET @.value = (RAND() * (SELECT max(UserID) FROM Users)) + 1
SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID
It would mean when gaps occur the row after the gap would be hit more often, but atleast you would cover your entire collection of rows.
Hope that makes sense.|||quote:
------------------------
Originally posted by r123456
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1
select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value
------------------------
What does columns stand for in the query?|||errr...won't my simple statement solve it?
SELECT TOP 1 * FROM TableName
order by newid()
I don't get it...|||the columns you want to select eg * or username, firstname, lastname etc...|||Hi Patrick,
I'm really not sure how your solution would work, what is the newid()?|||Its a build in command specifically to be use to "select random rows".
It is also use as a comand to auto gen Unique Identifier ids.
But when use in "order by newid()" it generates random rows.
Try it out and see...it works...unless..well theres something I'm missing in the whole discussion.|||Originally posted by Patrick Chua
errr...won't my simple statement solve it?
SELECT TOP 1 * FROM TableName
order by newid()
I don't get it...
It works too, but it takes 2 seconds, a little slower. Now I know the function NewID() and it helps. Thanks.|||rokslide,
That is a good idea to replace Count(*) to MAX(UserID). Thanks.|||Cool, I think I'll have to have a look into that one some time. :)
Thanks Patrick. :)|||No worries gyuan, please note though that (as I said above) it's not truely random.|||Correct.
Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".
If TOP * 1 is used in conjunction with max(id) then only the first value past a "gap" value will be returned, should @.value be equal to a "gap" value.|||I think Patrick's query is better:
SELECT TOP 1 *
FROM Users
ORDER BY NewID()
although the running time is a little longer.|||I've used Patrick's method in the past with success.|||Isn't anyone going to ask WHY do you want to do this?|||I use Patricks method daily and it works out great for me.
NewID() takes a little longer due to it generates a GUID but it is truely random.|||Originally posted by Brett Kaiser
Isn't anyone going to ask WHY do you want to do this?
Brett ...
You are always after the "why" instead of the "how"? I like you for the spirit coz I believe "Prevention is better than cure".|||How's easy.....
And thanks...
Seen to many rocket ships built...|||I have to admit to orginally thinking why would you want to, but I have seen a few "scuffles" break out on here over the "why" of things so I decided to leave it alone.
Then of course the curiosity took over and I started to think,... hey, how would you do that...
anyhow...
r123456
with this statement...
Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".
but if you use count and then compare count to the ID value you are going to completely miss some sections of the data entirely (eg. they will never have a chance to be selected). See the example that I noted eariler. The the max(id) option atleast you cover your entire span of data.
Of course Patrick's solution will cover everything perfectly so....|||Not true.
ID | ID2
1 1
2 2
3 3
4 4
5 5
Delete from table where id=2 OR id=3;
ID | ID2
1 1
4 2
5 3
You have two solutions. One of which requires very little code and an SQL Server function. The other requires a unique id, which for example can be the value of ROWID for an Oracle database.|||Ah yes, sorry, I forgot the second version of your solution with the new ID. :)
My fault entirely.
How i get "rank" in this one
Hello
I have this query that works good, but I cant figure out how to get the rank
and order by it desc.
SELECT * FROM Companies WHERE CONTAINS(*,'something')
how I add rank to this one?
TIA
/Lasse
you can't you need to use the contains table syntax for this, ie
SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as FT
on FT.[key]=Companies.pk
where pk is the primary key/unique key of the table you are full text
indexing.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> Hello
> I have this query that works good, but I cant figure out how to get the
rank
> and order by it desc.
> SELECT * FROM Companies WHERE CONTAINS(*,'something')
> how I add rank to this one?
> TIA
> /Lasse
>
|||Hilary,
hmm, one problem, when i search for both Firstname and Lastname using OR it
doesnt really get the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"
is there a way to solve this?
/Lasse
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> you can't you need to use the contains table syntax for this, ie
> SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as
FT
> on FT.[key]=Companies.pk
> where pk is the primary key/unique key of the table you are full text
> indexing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> rank
>
|||Hilary,
this one returns rank=128
5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund Sverige
5472
this one rank=112
3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
Rita Andersson Sverige 3714
when i search for 'rita OR andersson'
not sure i understand how that first one got higher value
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> Hilary,
> hmm, one problem, when i search for both Firstname and Lastname using OR
it
> doesnt really get the "most correct" values first, they get the highest
rank[vbcol=seagreen]
> but...... and if I use AND nothing shows since no columns contain all
> "keywords"
> is there a way to solve this?
> /Lasse
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
as[vbcol=seagreen]
> FT
the
>
|||Lasse,
Adding RANK is easy, understanding the results is a bit more difficult as
you will need to supply additional information about your environment in
order to understand the RANK values that are returned from your FTS queries.
First of all, you should review the SQL Server 2000 BOL title "Full-text
Search Recommendations" and the next to last paragraph on RANK.
Another factor is the number of rows in your table must be "statistically
significantly", i.e.. at least 10,000 rows in order for the query specific
RANK values to return the executants results. If you are testing with a very
small table, then this can explain why the rank values are not what you are
expecting.
As for searching [both Firstname and Lastname using OR it doesn't really get
the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"], this is a different problem as SQL Server 2000 (correct?) does
not search across columns, see KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
Note, while the above KB articles are specific to SQL Server 7.0, they also
apply to SQL Server 2000 as SQL Server 7.0's behavior was fixed to
correspond to the default SQL Server 2000 behavior.
Finally, one way to join multiple columns in one CONTAINSTABLE query and
influence the rank value of one column over another via the weight parameter
is the following:
use Northwind
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, the AND condition between the Primary key (PK) values as you can also
make this an OR clause, but you may need to use a DISTINCT parameter to
remove duplicate results. Keep in mind, that you still need a "statistically
significantly" number of rows to get the best results.
Regards,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:O#mzCy9yEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hilary,
> this one returns rank=128
> 5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund
Sverige
> 5472
> this one rank=112
> 3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
> Rita Andersson Sverige 3714
> when i search for 'rita OR andersson'
> not sure i understand how that first one got higher value
>
>
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> it
> rank
> as
> the
>
I have this query that works good, but I cant figure out how to get the rank
and order by it desc.
SELECT * FROM Companies WHERE CONTAINS(*,'something')
how I add rank to this one?
TIA
/Lasse
you can't you need to use the contains table syntax for this, ie
SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as FT
on FT.[key]=Companies.pk
where pk is the primary key/unique key of the table you are full text
indexing.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> Hello
> I have this query that works good, but I cant figure out how to get the
rank
> and order by it desc.
> SELECT * FROM Companies WHERE CONTAINS(*,'something')
> how I add rank to this one?
> TIA
> /Lasse
>
|||Hilary,
hmm, one problem, when i search for both Firstname and Lastname using OR it
doesnt really get the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"
is there a way to solve this?
/Lasse
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> you can't you need to use the contains table syntax for this, ie
> SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as
FT
> on FT.[key]=Companies.pk
> where pk is the primary key/unique key of the table you are full text
> indexing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> rank
>
|||Hilary,
this one returns rank=128
5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund Sverige
5472
this one rank=112
3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
Rita Andersson Sverige 3714
when i search for 'rita OR andersson'
not sure i understand how that first one got higher value
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> Hilary,
> hmm, one problem, when i search for both Firstname and Lastname using OR
it
> doesnt really get the "most correct" values first, they get the highest
rank[vbcol=seagreen]
> but...... and if I use AND nothing shows since no columns contain all
> "keywords"
> is there a way to solve this?
> /Lasse
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
as[vbcol=seagreen]
> FT
the
>
|||Lasse,
Adding RANK is easy, understanding the results is a bit more difficult as
you will need to supply additional information about your environment in
order to understand the RANK values that are returned from your FTS queries.
First of all, you should review the SQL Server 2000 BOL title "Full-text
Search Recommendations" and the next to last paragraph on RANK.
Another factor is the number of rows in your table must be "statistically
significantly", i.e.. at least 10,000 rows in order for the query specific
RANK values to return the executants results. If you are testing with a very
small table, then this can explain why the rank values are not what you are
expecting.
As for searching [both Firstname and Lastname using OR it doesn't really get
the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"], this is a different problem as SQL Server 2000 (correct?) does
not search across columns, see KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
Note, while the above KB articles are specific to SQL Server 7.0, they also
apply to SQL Server 2000 as SQL Server 7.0's behavior was fixed to
correspond to the default SQL Server 2000 behavior.
Finally, one way to join multiple columns in one CONTAINSTABLE query and
influence the rank value of one column over another via the weight parameter
is the following:
use Northwind
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, the AND condition between the Primary key (PK) values as you can also
make this an OR clause, but you may need to use a DISTINCT parameter to
remove duplicate results. Keep in mind, that you still need a "statistically
significantly" number of rows to get the best results.
Regards,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:O#mzCy9yEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hilary,
> this one returns rank=128
> 5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund
Sverige
> 5472
> this one rank=112
> 3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
> Rita Andersson Sverige 3714
> when i search for 'rita OR andersson'
> not sure i understand how that first one got higher value
>
>
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> it
> rank
> as
> the
>
How I do this Query ?
Hello, Everyone
I have a table that hold Phone Calls Data, I store in this table the following information :
- customer name
- vendor name (i`m a thirdparty company)
- call date (ex: 7/1/2007 00:00:00)
- destination of call
- duration of call
- other information
--
I want to create table for destination only, i want to disply destination, every hour and the rest of table columns from the above table
i want one row for each destination every hourSorry. Not clear what you want.
Is this for a class assignment?
I have a table that hold Phone Calls Data, I store in this table the following information :
- customer name
- vendor name (i`m a thirdparty company)
- call date (ex: 7/1/2007 00:00:00)
- destination of call
- duration of call
- other information
--
I want to create table for destination only, i want to disply destination, every hour and the rest of table columns from the above table
i want one row for each destination every hourSorry. Not clear what you want.
Is this for a class assignment?
how i convert varchar sal field to numeric in query
how i convert varchar sal field to numeric in query
select sum(sal) from emp1
error:the sum or average aggregate operation cannot take a varchar data type as an argument.
Use Cast as in:
select sum(cast(sal as float)) from emp1
Subscribe to:
Posts (Atom)