how to convert msaccess 2003 data to sqlserver 2005
Check out this post:Transferring information from Access to Sql Express
http://forums.asp.net/p/1161806/1922543.aspx
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Tuesday, March 27, 2012
convert
Sunday, March 25, 2012
Conversion of code from oracle to sql server
hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanx
Hello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegr oups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanx
Hello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegr oups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>
Sunday, March 11, 2012
Controlling Access
Is there a method of only allowing users to access SQL
Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
I am not referring to the "Application Roles" available
within SQL Server, rather I would like the following to
take place: -
Example 1.
User connects to SQL Server Database, by either using
Commercial Software or Bespoke System, using "NT
Authentication".
SQL Server recognises the user and the Application/System
and allows access to the Database.
Example 2.
User connects to SQL Server Database, by linking/Importing
Tables to a new Database Document, using "NT
Authentication".
SQL Server recognises the user but not the
Application/System and denies access to the Database.
I am using SQL Server 7.0, although we will soon be
migrating to 2000.
TIA
Tony C.Unfortunately no. You could monitor for connections after
the fact, e.g. have a job that checks who is connected using
what application and kill processes that are connected using
the non-approved applications. But you can't really control
access based on what application is being used.
-Sue
On Fri, 2 Apr 2004 01:34:33 -0800, "Tony C"
<tony.chorleyRUBBISH@.mcalpineplc.com> wrote:
>Is there a method of only allowing users to access SQL
>Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
>I am not referring to the "Application Roles" available
>within SQL Server, rather I would like the following to
>take place: -
>Example 1.
>User connects to SQL Server Database, by either using
>Commercial Software or Bespoke System, using "NT
>Authentication".
>SQL Server recognises the user and the Application/System
>and allows access to the Database.
>Example 2.
>User connects to SQL Server Database, by linking/Importing
>Tables to a new Database Document, using "NT
>Authentication".
>SQL Server recognises the user but not the
>Application/System and denies access to the Database.
>I am using SQL Server 7.0, although we will soon be
>migrating to 2000.
>TIA
>Tony C.
Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
I am not referring to the "Application Roles" available
within SQL Server, rather I would like the following to
take place: -
Example 1.
User connects to SQL Server Database, by either using
Commercial Software or Bespoke System, using "NT
Authentication".
SQL Server recognises the user and the Application/System
and allows access to the Database.
Example 2.
User connects to SQL Server Database, by linking/Importing
Tables to a new Database Document, using "NT
Authentication".
SQL Server recognises the user but not the
Application/System and denies access to the Database.
I am using SQL Server 7.0, although we will soon be
migrating to 2000.
TIA
Tony C.Unfortunately no. You could monitor for connections after
the fact, e.g. have a job that checks who is connected using
what application and kill processes that are connected using
the non-approved applications. But you can't really control
access based on what application is being used.
-Sue
On Fri, 2 Apr 2004 01:34:33 -0800, "Tony C"
<tony.chorleyRUBBISH@.mcalpineplc.com> wrote:
>Is there a method of only allowing users to access SQL
>Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
>I am not referring to the "Application Roles" available
>within SQL Server, rather I would like the following to
>take place: -
>Example 1.
>User connects to SQL Server Database, by either using
>Commercial Software or Bespoke System, using "NT
>Authentication".
>SQL Server recognises the user and the Application/System
>and allows access to the Database.
>Example 2.
>User connects to SQL Server Database, by linking/Importing
>Tables to a new Database Document, using "NT
>Authentication".
>SQL Server recognises the user but not the
>Application/System and denies access to the Database.
>I am using SQL Server 7.0, although we will soon be
>migrating to 2000.
>TIA
>Tony C.
control transaction in sybase database using sql-server 2000
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Helpcan anyone help me regarding this topic|||what was the error?
go thru books on line (BOL) about
sp_addlinkedserver and sp_addlinkedsrvlogin.
Originally posted by RajiniKasturi
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Help|||Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||I guess it should be BEGIN DISTRIBUTED TRANSACTION
instead of begin tran.
let me check it out.
Originally posted by RajiniKasturi
Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||Thanks again for the prompt reply
It is not working with that option too
Is there is anyway I can achieve this??
Can u pl.help me
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Helpcan anyone help me regarding this topic|||what was the error?
go thru books on line (BOL) about
sp_addlinkedserver and sp_addlinkedsrvlogin.
Originally posted by RajiniKasturi
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Help|||Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||I guess it should be BEGIN DISTRIBUTED TRANSACTION
instead of begin tran.
let me check it out.
Originally posted by RajiniKasturi
Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||Thanks again for the prompt reply
It is not working with that option too
Is there is anyway I can achieve this??
Can u pl.help me
control transaction duration
Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
control transaction duration
Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
control transaction duration
Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
control the percentage of processor used by SQL server
exists a form to control the percentage of processor used by SQL
server? for example, not use more than 60% percent.
I don't think you can do that.You can tell the number of cpu's to use but but
not by
percent
"hongo32" wrote:
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegro ups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/dow...srvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David
|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsserv...m/default.mspx
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegro ups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
server? for example, not use more than 60% percent.
I don't think you can do that.You can tell the number of cpu's to use but but
not by
percent
"hongo32" wrote:
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegro ups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/dow...srvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David
|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsserv...m/default.mspx
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegro ups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
control the percentage of processor used by SQL server
exists a form to control the percentage of processor used by SQL
server? for example, not use more than 60% percent.I don't think you can do that.You can tell the number of cpu's to use but bu
t
not by
percent
"hongo32" wrote:
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/do...nsrvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsser...lt.ms
px
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
server? for example, not use more than 60% percent.I don't think you can do that.You can tell the number of cpu's to use but bu
t
not by
percent
"hongo32" wrote:
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/do...nsrvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsser...lt.ms
px
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
Thursday, March 8, 2012
Control jobs using SQL code?
Is there a way (system stored proc) to schedule/reschedule jobs through
SQL code (stored procedure) instead of GUI? We have a job set up on SQL
server and we are trying to control scheduling piece of it through
stored proc. Any help would be appreciated. Thanx!
*** Sent via Developersdex http://www.examnotes.net ***Have a look at sp_update_jobschedule in Books Online. This procedure is in t
he
msdb database, so it is used like
EXEC msdb.dbo.sp_update_jobschedule
"Test Test" wrote:
> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||have a look at following system stored procedures in BOL, there are few more
as well which are related to scheduling the job, you can have a look at them
in BOL.
sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_delete_job
sp_help_job
sp_help_jobstep
sp_update_job
"Test Test" wrote:
> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Mark. It really helps!
*** Sent via Developersdex http://www.examnotes.net ***
SQL code (stored procedure) instead of GUI? We have a job set up on SQL
server and we are trying to control scheduling piece of it through
stored proc. Any help would be appreciated. Thanx!
*** Sent via Developersdex http://www.examnotes.net ***Have a look at sp_update_jobschedule in Books Online. This procedure is in t
he
msdb database, so it is used like
EXEC msdb.dbo.sp_update_jobschedule
"Test Test" wrote:
> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||have a look at following system stored procedures in BOL, there are few more
as well which are related to scheduling the job, you can have a look at them
in BOL.
sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_delete_job
sp_help_job
sp_help_jobstep
sp_update_job
"Test Test" wrote:
> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Mark. It really helps!
*** Sent via Developersdex http://www.examnotes.net ***
Wednesday, March 7, 2012
Continue SP after Database Access Failure
Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:
> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:
> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:
> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:
> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>
Friday, February 24, 2012
Contains clause with only NOT keywords
Hello everyone,
I posted this on sqlserver.programming and it was recommended I try this
group.
I am designing a search screen that searches for keywords in Text fields as
well as searching other related tables with fields like Date ranges and
other lookup code fields. One of our users asked why they can't use a
Date-Range search in conjunction with keywords NOT found in the free text. I
have read that it is not possible to do with Contains.
For example, a standard keyword search might create this Contains clause:
contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &! "bull"')
The users just want to use the &! "cow" &! "bull" part of the Contains query
along with other more standard Where criteria, for example "and OrderDate >
'10/10 2006' ".
I have tried to pass "noise" words for the first part of the Contains, but
they are ignored.
I also tried separating out the NOT keywords into a series of " and not
description like 'bull%' " type filters, but the performance becomes
intolerably slow.
Is there any way to get around this problem? Maybe some crafty trickery?
Thanks to all...
You have to parse your query so that it looks like this:
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull")')
I have upper cased the boolean operators for clarity.
For your date query it would look like this
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull")')
where orderdate>'2007-01-01'
You cannot search on a date string and hope for it to be interpreted as a
date and do inequality operations on it. So I could not do something like
this
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull") and OrderDate>'2007-01-01')
as sql FTS can only interpret the date string as a string and only do not
equal or equal operations against it.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:%23MZFHLUIIHA.5352@.TK2MSFTNGP03.phx.gbl...
> Hello everyone,
> I posted this on sqlserver.programming and it was recommended I try this
> group.
> I am designing a search screen that searches for keywords in Text fields
> as
> well as searching other related tables with fields like Date ranges and
> other lookup code fields. One of our users asked why they can't use a
> Date-Range search in conjunction with keywords NOT found in the free text.
> I
> have read that it is not possible to do with Contains.
> For example, a standard keyword search might create this Contains clause:
> contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &! "bull"')
> The users just want to use the &! "cow" &! "bull" part of the Contains
> query
> along with other more standard Where criteria, for example "and OrderDate
> '10/10 2006' ".
> I have tried to pass "noise" words for the first part of the Contains, but
> they are ignored.
> I also tried separating out the NOT keywords into a series of " and not
> description like 'bull%' " type filters, but the performance becomes
> intolerably slow.
> Is there any way to get around this problem? Maybe some crafty trickery?
> Thanks to all...
>
>
|||FYI - the original thread can be found here:
[url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
As I understand it, he OP was trying to find out how to combine a negative
FTI search (using CONTAINS) with additional restrictions in the WHERE caluse.
I suggested building the condition using NOT(CONTAINS()).
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||thanks ML - that is an interesting approach. That should work, but it would
be expensive if the results set was large.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:329CBEFC-00B7-4396-B16C-79C5B6461DCB@.microsoft.com...
> FYI - the original thread can be found here:
> [url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
> As I understand it, he OP was trying to find out how to combine a negative
> FTI search (using CONTAINS) with additional restrictions in the WHERE
> caluse.
> I suggested building the condition using NOT(CONTAINS()).
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
I posted this on sqlserver.programming and it was recommended I try this
group.
I am designing a search screen that searches for keywords in Text fields as
well as searching other related tables with fields like Date ranges and
other lookup code fields. One of our users asked why they can't use a
Date-Range search in conjunction with keywords NOT found in the free text. I
have read that it is not possible to do with Contains.
For example, a standard keyword search might create this Contains clause:
contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &! "bull"')
The users just want to use the &! "cow" &! "bull" part of the Contains query
along with other more standard Where criteria, for example "and OrderDate >
'10/10 2006' ".
I have tried to pass "noise" words for the first part of the Contains, but
they are ignored.
I also tried separating out the NOT keywords into a series of " and not
description like 'bull%' " type filters, but the performance becomes
intolerably slow.
Is there any way to get around this problem? Maybe some crafty trickery?
Thanks to all...
You have to parse your query so that it looks like this:
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull")')
I have upper cased the boolean operators for clarity.
For your date query it would look like this
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull")')
where orderdate>'2007-01-01'
You cannot search on a date string and hope for it to be interpreted as a
date and do inequality operations on it. So I could not do something like
this
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull") and OrderDate>'2007-01-01')
as sql FTS can only interpret the date string as a string and only do not
equal or equal operations against it.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:%23MZFHLUIIHA.5352@.TK2MSFTNGP03.phx.gbl...
> Hello everyone,
> I posted this on sqlserver.programming and it was recommended I try this
> group.
> I am designing a search screen that searches for keywords in Text fields
> as
> well as searching other related tables with fields like Date ranges and
> other lookup code fields. One of our users asked why they can't use a
> Date-Range search in conjunction with keywords NOT found in the free text.
> I
> have read that it is not possible to do with Contains.
> For example, a standard keyword search might create this Contains clause:
> contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &! "bull"')
> The users just want to use the &! "cow" &! "bull" part of the Contains
> query
> along with other more standard Where criteria, for example "and OrderDate
> '10/10 2006' ".
> I have tried to pass "noise" words for the first part of the Contains, but
> they are ignored.
> I also tried separating out the NOT keywords into a series of " and not
> description like 'bull%' " type filters, but the performance becomes
> intolerably slow.
> Is there any way to get around this problem? Maybe some crafty trickery?
> Thanks to all...
>
>
|||FYI - the original thread can be found here:
[url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
As I understand it, he OP was trying to find out how to combine a negative
FTI search (using CONTAINS) with additional restrictions in the WHERE caluse.
I suggested building the condition using NOT(CONTAINS()).
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||thanks ML - that is an interesting approach. That should work, but it would
be expensive if the results set was large.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:329CBEFC-00B7-4396-B16C-79C5B6461DCB@.microsoft.com...
> FYI - the original thread can be found here:
> [url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
> As I understand it, he OP was trying to find out how to combine a negative
> FTI search (using CONTAINS) with additional restrictions in the WHERE
> caluse.
> I suggested building the condition using NOT(CONTAINS()).
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
CONTAINS clause problem with single-quotes
Hi all,
This is cross posted to sqlserver.prgramming also...
I am confused by the MSDN help regarding the Contains clause for fulltext
search.
The Help states that the single-quote does not need to be escaped for
CONTAINS but must be escaped for FREETEXT.
Here is an example of a CONTAINS put together by my search program which
does not produce an error:
AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
order by list_Name
The fonts in my email are not correctly representing the clause. I have
replaced ' in the word girl's with 2 single quotes as in typical escaping
for strings...similar to a simple:
(Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
course produces a syntax error.
The problem is that I expect only list_names that contain "girl's" to be
returned. However, with the CONTAINS clause using escape for single-quote
also returns any list_name that contains 'girl' as well.
When I don't double up the quotes, the CONTAINS clause returns an error "
syntax error near 's' ", not what is expected by the online Help.
Anybody have some help for me?
John,
SQL Server fulltext indexes do not support searching for punctuation. In
the string 'girl''s' the quote character is a word-breaker, so you wind up
with two words 'girl' and 's'. In most cases 's' is a noise word and gets
dropped altogether.
If you are looking for punctuation, you will have to combine a full text
query clause with a string search clause such as:
AND summary LIKE '%girl''s%'
RLF
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> This is cross posted to sqlserver.prgramming also...
> I am confused by the MSDN help regarding the Contains clause for fulltext
> search.
> The Help states that the single-quote does not need to be escaped for
> CONTAINS but must be escaped for FREETEXT.
> Here is an example of a CONTAINS put together by my search program which
> does not produce an error:
> AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
> OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
> order by list_Name
> The fonts in my email are not correctly representing the clause. I have
> replaced ' in the word girl's with 2 single quotes as in typical escaping
> for strings...similar to a simple:
> (Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
> course produces a syntax error.
> The problem is that I expect only list_names that contain "girl's" to be
> returned. However, with the CONTAINS clause using escape for single-quote
> also returns any list_name that contains 'girl' as well.
> When I don't double up the quotes, the CONTAINS clause returns an error "
> syntax error near 's' ", not what is expected by the online Help.
> Anybody have some help for me?
>
|||Thanks Russell,
That makes perfectly good sense. I appreciate the help.
Happy holidays.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OAQrdmLQIHA.4912@.TK2MSFTNGP06.phx.gbl...
> John,
> SQL Server fulltext indexes do not support searching for punctuation. In
> the string 'girl''s' the quote character is a word-breaker, so you wind up
> with two words 'girl' and 's'. In most cases 's' is a noise word and gets
> dropped altogether.
> If you are looking for punctuation, you will have to combine a full text
> query clause with a string search clause such as:
> AND summary LIKE '%girl''s%'
> RLF
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
> news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
>
This is cross posted to sqlserver.prgramming also...
I am confused by the MSDN help regarding the Contains clause for fulltext
search.
The Help states that the single-quote does not need to be escaped for
CONTAINS but must be escaped for FREETEXT.
Here is an example of a CONTAINS put together by my search program which
does not produce an error:
AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
order by list_Name
The fonts in my email are not correctly representing the clause. I have
replaced ' in the word girl's with 2 single quotes as in typical escaping
for strings...similar to a simple:
(Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
course produces a syntax error.
The problem is that I expect only list_names that contain "girl's" to be
returned. However, with the CONTAINS clause using escape for single-quote
also returns any list_name that contains 'girl' as well.
When I don't double up the quotes, the CONTAINS clause returns an error "
syntax error near 's' ", not what is expected by the online Help.
Anybody have some help for me?
John,
SQL Server fulltext indexes do not support searching for punctuation. In
the string 'girl''s' the quote character is a word-breaker, so you wind up
with two words 'girl' and 's'. In most cases 's' is a noise word and gets
dropped altogether.
If you are looking for punctuation, you will have to combine a full text
query clause with a string search clause such as:
AND summary LIKE '%girl''s%'
RLF
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> This is cross posted to sqlserver.prgramming also...
> I am confused by the MSDN help regarding the Contains clause for fulltext
> search.
> The Help states that the single-quote does not need to be escaped for
> CONTAINS but must be escaped for FREETEXT.
> Here is an example of a CONTAINS put together by my search program which
> does not produce an error:
> AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
> OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
> order by list_Name
> The fonts in my email are not correctly representing the clause. I have
> replaced ' in the word girl's with 2 single quotes as in typical escaping
> for strings...similar to a simple:
> (Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
> course produces a syntax error.
> The problem is that I expect only list_names that contain "girl's" to be
> returned. However, with the CONTAINS clause using escape for single-quote
> also returns any list_name that contains 'girl' as well.
> When I don't double up the quotes, the CONTAINS clause returns an error "
> syntax error near 's' ", not what is expected by the online Help.
> Anybody have some help for me?
>
|||Thanks Russell,
That makes perfectly good sense. I appreciate the help.
Happy holidays.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OAQrdmLQIHA.4912@.TK2MSFTNGP06.phx.gbl...
> John,
> SQL Server fulltext indexes do not support searching for punctuation. In
> the string 'girl''s' the quote character is a word-breaker, so you wind up
> with two words 'girl' and 's'. In most cases 's' is a noise word and gets
> dropped altogether.
> If you are looking for punctuation, you will have to combine a full text
> query clause with a string search clause such as:
> AND summary LIKE '%girl''s%'
> RLF
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
> news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
>
Sunday, February 19, 2012
consuming sqlserver 2005 webservice from asp.net 1.1
Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:
Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).Thanks,
Olja
Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx
If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.
Jimmy
consuming sqlserver 2005 webservice from asp.net 1.1
Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:
Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).Thanks,
Olja
Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx
If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.
Jimmy
Consume DataReaderDest from asp.net page?
I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?
protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";
DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();
DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";
IDataReader reader = command.ExecuteReader(CommandBehavior.Default);
DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);
_grid.DataSource = set;
_grid.DataBind();
connection.Close();
}
You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).Consume DataReaderDest from asp.net page?
I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?
protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";
DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();
DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";
IDataReader reader = command.ExecuteReader(CommandBehavior.Default);
DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);
_grid.DataSource = set;
_grid.DataBind();
connection.Close();
}
You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).
Subscribe to:
Posts (Atom)