I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql
Showing posts with label force. Show all posts
Showing posts with label force. Show all posts
Tuesday, March 27, 2012
Can I force SQL Server to use the CONTAINS operator first?
If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0
How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:
SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0
It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.
Actually CONTAINS is a predicate that can be used with the WHERE clause there are two versions of it defined by ANSI SQL and implemented by Microsoft CONTAINS and CONTAINSTABLE, the other two FULLTEXT predicates are FREETEXT and FREETEXTTBALE, try the link below for details. Hope this helps.
http://technet.microsoft.com/en-us/library/ms187787.aspx
|||Hi Caddre,
Thanks for the reply -- but that didn't seem to have anything to do with my question!
|||(It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.)
It does because since CONTAINS is a predicate and not a clause what you can force it to do is determined by the restrictions and limitations listed in the link I provided.
|||no, you will have no control over the freetext functionality as in SQL Server 2005 the query in FT searches still is not connected to the query engine and therefore cannot be optimized and tweaked in your requested way.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
OK, I found a way to do it:
DECLARE @.ftQuery nvarchar(32)
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), @.ftQuery)
AND fn_TestCol(col1) = 0
OPTION (OPTIMIZE FOR(@.ftQuery = 'foo'))
This structure allows the optimizer to make assumptions about what might be in @.ftQuery -- so it correctly assumes that the CONTAINS predicate will be a faster starting point than the udf, and runs with the right plan.
|||_jesse,
Interesting.. this as long been a "by design" problem with SQL FTS since SQL Server 7.0. You never said which version of SQL Server you are using, but I'm assuming it is SQL 2000 and not SQL Server 2005. Correct? Can you provide more details on your UDF fn_TestCol? How many rows do you have in your real table or your myTable example?
Thanks,
John
John T. Kane
Search Evangelist, Intellisearch
email: john.kane@.intellisearch.no
Hi John,
It is SQL 2005.
The problem, as I understand it, is this: when you pass a parameter to CONTAINS, as in
CONTAINS(col1, @.searchText)
the optimizer isn't able to recognize that the CONTAINS operator is a good place to start. The execution plan changes if you replace @.searchText with a literal string, as in:
CONTAINS(col1, 'foo')
In my case, the table being scanned was relatively small (20,000 rows), but the UDF is slow -- it's doing a bunch of string manipulation & comparison. Since the CONTAINS clause is likely to be very selective, the query is very fast if you start with CONTAINS, then run the UDF on the few rows matching the fts, but very slow if you do it the other way around.
Declaring the @.searchText parameter as nvarchar, then adding the OPTIMIZE FOR hint gets it to consistently run in the right order.
Can I force printing?
I have a script or SP that takes a very long time to perform
multiple tasks, and after each one there is a PRINT statement
that shows the time and what was just accomplished, to help me
monitor what's happening and estimate how long it will take.
In a script, I can put a GO after each PRINT to cause the output
to appear immediately, but that's not possible inside an SP.
Instead, it seems the output goes to a buffer, and the buffer
is only output when a PRINT causes the buffer to become full.
Sometimes there is a long delay before the buffer fills. Is
there a way other than GO to cause immediate printing? (I
guess I could just use longer messages, and fill up the
buffer with every one, but that's not very elegant.)
multiple tasks, and after each one there is a PRINT statement
that shows the time and what was just accomplished, to help me
monitor what's happening and estimate how long it will take.
In a script, I can put a GO after each PRINT to cause the output
to appear immediately, but that's not possible inside an SP.
Instead, it seems the output goes to a buffer, and the buffer
is only output when a PRINT causes the buffer to become full.
Sometimes there is a long delay before the buffer fills. Is
there a way other than GO to cause immediate printing? (I
guess I could just use longer messages, and fill up the
buffer with every one, but that's not very elegant.)
Thanks,
Jim G
Countrywide Home LoansYou can use RAISERROR...WITH NOWAIT to cause messages to avoid buffering.
Messages will be displayed immediately when using a tool like Query
Analyzer:
RAISERROR ('my message', 0, 1) WITH NOWAIT
--
Hope this helps.
Dan Guzman
SQL Server MVP
<jim_geissman@.countrywide.com> wrote in message
news:1125531618.828313.53510@.z14g2000cwz.googlegro ups.com...
>I have a script or SP that takes a very long time to perform
> multiple tasks, and after each one there is a PRINT statement
> that shows the time and what was just accomplished, to help me
> monitor what's happening and estimate how long it will take.
> In a script, I can put a GO after each PRINT to cause the output
> to appear immediately, but that's not possible inside an SP.
> Instead, it seems the output goes to a buffer, and the buffer
> is only output when a PRINT causes the buffer to become full.
> Sometimes there is a long delay before the buffer fills. Is
> there a way other than GO to cause immediate printing? (I
> guess I could just use longer messages, and fill up the
> buffer with every one, but that's not very elegant.)
> Thanks,
> Jim G
> Countrywide Home Loans|||Thanks, Dan.
Jim Geissman
can i force disconnection from a stored proc ?
inside a stored proc
that validates the user,
can i force to disconnect ?
atte,
Hernn Castelo
SGA - UTN - FRBA
The only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernn Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
|||You need to be a member of sysadmin to run KILL...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>
|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
atte,
Hernn Castelo
SGA - UTN - FRBA
"Hernn Castelo" <bajopalabra@.hotmail.com> escribi en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
that validates the user,
can i force to disconnect ?
atte,
Hernn Castelo
SGA - UTN - FRBA
The only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernn Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
|||You need to be a member of sysadmin to run KILL...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>
|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
atte,
Hernn Castelo
SGA - UTN - FRBA
"Hernn Castelo" <bajopalabra@.hotmail.com> escribi en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
can i force disconnection from a stored proc ?
inside a stored proc
that validates the user,
can i force to disconnect ?
atte,
Hernn Castelo
SGA - UTN - FRBAThe only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernn Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10|||You need to be a member of sysadmin to run KILL...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
atte,
Hernn Castelo
SGA - UTN - FRBA
"Hernn Castelo" <bajopalabra@.hotmail.com> escribi en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
that validates the user,
can i force to disconnect ?
atte,
Hernn Castelo
SGA - UTN - FRBAThe only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernn Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10|||You need to be a member of sysadmin to run KILL...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
atte,
Hernn Castelo
SGA - UTN - FRBA
"Hernn Castelo" <bajopalabra@.hotmail.com> escribi en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
>
can i force disconnection from a stored proc ?
inside a stored proc
that validates the user,
can i force to disconnect ?
--
atte,
Hernán Castelo
SGA - UTN - FRBAThe only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernán Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10|||You need to be a member of sysadmin to run KILL...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
--
atte,
Hernán Castelo
SGA - UTN - FRBA
"Hernán Castelo" <bajopalabra@.hotmail.com> escribió en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>sql
that validates the user,
can i force to disconnect ?
--
atte,
Hernán Castelo
SGA - UTN - FRBAThe only way I know of to force a disconnect is to raise an error with a
severity of 20 or higher. However, that requires that the login is a member
of the sysadmin role. Not a good idea, generally, to put everyone in that
role! Can't you just RETURN if the user isn't validated properly?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||Yes, it is possible with the Kill Spid command
Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
the value for the current session.
This example shows how to terminate SPID 10.
KILL 10
"Hernán Castelo" <bajopalabra@.hotmail.com> schreef in bericht
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||And in addition to the sysadmin problem, try killing your own process and
see what happens:
Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10|||You need to be a member of sysadmin to run KILL...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Johan Koopmans" <koopmans.johan@.hccnet.nl> wrote in message
news:ecMX39mzEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Yes, it is possible with the Kill Spid command
> Execute sp_who to get a report on valid SPID values. Use @.@.SPID to display
> the value for the current session.
> This example shows how to terminate SPID 10.
> KILL 10
>|||Define validate and disconnect. I have my own definition.
Do you have English speaking people near you?
Jeff
"Hernán Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>|||thanks for the replies
yes...you are right ...it is a bad idea
and not very useful
--
atte,
Hernán Castelo
SGA - UTN - FRBA
"Hernán Castelo" <bajopalabra@.hotmail.com> escribió en el mensaje
news:OZZ$grmzEHA.2012@.TK2MSFTNGP15.phx.gbl...
> inside a stored proc
> that validates the user,
> can i force to disconnect ?
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>sql
Can I force a checkpoint.
Our application is having problems due to what appears to be a flush to disk
during a checkpoint. The flush lasts 2-6 seconds and then every thing
continues on as before with no delay.
I tried creating a job to run every two minutes (just during our stress test).
All the job does is call "CHECKPOINT". Looking at the logs during my stress
tests, there is no difference in the behavior.
So, how can I get the DB to do checkpoints more often to prevent such a
large lump write ?
Or, am I totally off in the wrong direction on this ?
Any thoughts would be greatly appreciated.
Mark.
Notes: all databases are running in simple mode.I am looking at checkpoint pages/sec and pages writes/sec along with
physical read and write.
Also, I am monitoring the log %used on all DB's. I can see that when one of
my DB's hits
70% it appears to be related to this dump which seems to go along with the
docs for
checkpoint and db set to simple.
I am 100% sure it is not related to a query. It might not have anything to
do with a checkpoint,
but when the log file grows to 70% and gets reset to 20% I can see this huge
write and
I get a major backup. I see a similar relation on another DB.
Should I just shrink the log file, will this help decrease the number of
pages to write?
It appears to be true on a different DB with a smaller log file the writes
following
the truncation of the log is very small in comparison, does that make sense?
Thanks,
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Oz3lJpYUDHA.484@.TK2MSFTNGP09.phx.gbl...
> how do you know for sure it's related to checkpoint? What counters are you
> basing that on? CHECKPOINT will run the checkpoint process so I'm not
really
> sure what you'e asking... you can run it as frequently as you want...
> also... I might take a look at SQL Profiler to see if there is some other
> query that is perhaps related to the IO... it might not be checkpoint
> related...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Mark DeBusschere" <developer@.line-4.com> wrote in message
> news:uTEjGgXUDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > Our application is having problems due to what appears to be a flush to
> disk
> > during a checkpoint. The flush lasts 2-6 seconds and then every thing
> > continues on as before with no delay.
> >
> > I tried creating a job to run every two minutes (just during our stress
> test).
> > All the job does is call "CHECKPOINT". Looking at the logs during my
> stress
> > tests, there is no difference in the behavior.
> >
> > So, how can I get the DB to do checkpoints more often to prevent such a
> > large lump write ?
> >
> > Or, am I totally off in the wrong direction on this ?
> >
> > Any thoughts would be greatly appreciated.
> >
> > Mark.
> >
> > Notes: all databases are running in simple mode.
> >
> >
>|||Hi Mark,
My name is Michael and I would like to thank you for using Microsoft VAP
newsgroup.
I will update you later. In the mean time, if you have any other useful
information, please feel free to let me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
during a checkpoint. The flush lasts 2-6 seconds and then every thing
continues on as before with no delay.
I tried creating a job to run every two minutes (just during our stress test).
All the job does is call "CHECKPOINT". Looking at the logs during my stress
tests, there is no difference in the behavior.
So, how can I get the DB to do checkpoints more often to prevent such a
large lump write ?
Or, am I totally off in the wrong direction on this ?
Any thoughts would be greatly appreciated.
Mark.
Notes: all databases are running in simple mode.I am looking at checkpoint pages/sec and pages writes/sec along with
physical read and write.
Also, I am monitoring the log %used on all DB's. I can see that when one of
my DB's hits
70% it appears to be related to this dump which seems to go along with the
docs for
checkpoint and db set to simple.
I am 100% sure it is not related to a query. It might not have anything to
do with a checkpoint,
but when the log file grows to 70% and gets reset to 20% I can see this huge
write and
I get a major backup. I see a similar relation on another DB.
Should I just shrink the log file, will this help decrease the number of
pages to write?
It appears to be true on a different DB with a smaller log file the writes
following
the truncation of the log is very small in comparison, does that make sense?
Thanks,
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Oz3lJpYUDHA.484@.TK2MSFTNGP09.phx.gbl...
> how do you know for sure it's related to checkpoint? What counters are you
> basing that on? CHECKPOINT will run the checkpoint process so I'm not
really
> sure what you'e asking... you can run it as frequently as you want...
> also... I might take a look at SQL Profiler to see if there is some other
> query that is perhaps related to the IO... it might not be checkpoint
> related...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Mark DeBusschere" <developer@.line-4.com> wrote in message
> news:uTEjGgXUDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > Our application is having problems due to what appears to be a flush to
> disk
> > during a checkpoint. The flush lasts 2-6 seconds and then every thing
> > continues on as before with no delay.
> >
> > I tried creating a job to run every two minutes (just during our stress
> test).
> > All the job does is call "CHECKPOINT". Looking at the logs during my
> stress
> > tests, there is no difference in the behavior.
> >
> > So, how can I get the DB to do checkpoints more often to prevent such a
> > large lump write ?
> >
> > Or, am I totally off in the wrong direction on this ?
> >
> > Any thoughts would be greatly appreciated.
> >
> > Mark.
> >
> > Notes: all databases are running in simple mode.
> >
> >
>|||Hi Mark,
My name is Michael and I would like to thank you for using Microsoft VAP
newsgroup.
I will update you later. In the mean time, if you have any other useful
information, please feel free to let me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Friday, February 10, 2012
calling subscription via the web service
I'd like to force a subscription to run in an adhoc fashion using the web
service provided. Is this possible? If not, does anyone know a work around
to make this happen?Create the subscription as a TimedSubscription with a schedule that fires
once in the past. When you want the schedule to fire, call FireEvent
passing in TimedSubscription and the id of the subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Stewart" <ChrisStewart@.discussions.microsoft.com> wrote in message
news:D8586F2B-EC5B-421B-A167-9A312FCFB8E5@.microsoft.com...
> I'd like to force a subscription to run in an adhoc fashion using the web
> service provided. Is this possible? If not, does anyone know a work
> around
> to make this happen?
service provided. Is this possible? If not, does anyone know a work around
to make this happen?Create the subscription as a TimedSubscription with a schedule that fires
once in the past. When you want the schedule to fire, call FireEvent
passing in TimedSubscription and the id of the subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Stewart" <ChrisStewart@.discussions.microsoft.com> wrote in message
news:D8586F2B-EC5B-421B-A167-9A312FCFB8E5@.microsoft.com...
> I'd like to force a subscription to run in an adhoc fashion using the web
> service provided. Is this possible? If not, does anyone know a work
> around
> to make this happen?
Subscribe to:
Posts (Atom)