Can SQL Server do this?

I was curious.
I am building a filter string on a webpage to pass to a stored
procedure. The filter string would be some kind of IN clause.
Is there a way to pass the IN clause to the stored procedure?
For instance, let's say you have a stored procedure called MyProc
It takes 1 parameter, maybe called @.alpha, potentially a string like
"('CA','MA')"
Is there a way that you could say something like:
WHERE @.alpha
In other words, pass the filter string directly to the SP? I tried it
and couldn't get it to work and I was wondering if there were a way to
even do this so I can keep the query as a stored procedure.Hi Brent
Before we start I am hoping that you are aware with code injection issues
that the dynamic creation of SQL statements can lead to. I advise strongly
against building dynamic queries as such because the security implications
are enormous.
I would possible prepopulate a temp table such as the following.
CREATE TABLE #criteria ( lookup varchar(50))
INSERT #criteria (lookup) VALUES("John")
INSERT #criteria (lookup) VALUES("Peter")
INSERT #criteria (lookup) VALUES("Alan")
Then call the procedure
EXEC sp_lookup
With will do something like
SELECT somefield FROM sometable s where EXISTS ( SELECT * FROM #criteria
WHERE s.somelookupfield = #criteria.lookup)
This may require you to recode some of the page but it is safer, as long of
course as you validate the Web form input first so it doen't get injectect
into the insert statements.
Hope this helps
Phil
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||The only way you can do that is to use dynamic sql. This can introduce
security concerns, so you should be careful about using it. There have been
many posts on this newsgroup about dynamic sql--some with links to very good
information, so I suggest you review them and consider all of the
ramifications before implementing it.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>|||I personally prefer this method:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
...if you need it in pure T-SQL.
ML|||I use a method described here:
http://www.sommarskog.se/arrays-in-sql.html
In the table of contents, click on "List-of-strings".
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1130428318.975471.109560@.g44g2000cwa.googlegroups.com...
>I was curious.
> I am building a filter string on a webpage to pass to a stored
> procedure. The filter string would be some kind of IN clause.
> Is there a way to pass the IN clause to the stored procedure?
> For instance, let's say you have a stored procedure called MyProc
> It takes 1 parameter, maybe called @.alpha, potentially a string like
> "('CA','MA')"
> Is there a way that you could say something like:
> WHERE @.alpha
> In other words, pass the filter string directly to the SP? I tried it
> and couldn't get it to work and I was wondering if there were a way to
> even do this so I can keep the query as a stored procedure.
>

Can SQL Reporting and Crystal Web go on same web server?

Has anyone installed SQL Reporting Services on a web server that's already
hosting Crystal Web? Curious if this will work. Thanks!Haven't installed them on the same server however there is absolutely no
reason not to... it's just another web site - it's like saying would there be
a problem installing my own personal web site on the same server as RS or
CE... it's just another site in IIS.
"janda" wrote:
> Has anyone installed SQL Reporting Services on a web server that's already
> hosting Crystal Web? Curious if this will work. Thanks!

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
Meenal
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>
|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
MeenalMeenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
--
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
--
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
--
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
MeenalMeenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.
|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW