DSO in SQL2005: stored

Showing posts with label stored. Show all posts

Thursday, March 29, 2012

Dropping Temporary Tables

Is it better to drop temporary tables at the end of a stored procedure?
I have heard somewhere that there can be stored proc recompilation issues which affect performance when you explicitly drop temp tables?

Will SQL Server "Clean Up" once the stored proc is out of scope or should I "do it myself".
Which method has known performance benefits?

CheersHi,
If resources arent an issue on your sqlserver you dont need to drop temps yourself. sqlserver will drop them when the sp is done.

Be careful though when working with temps, more than five updates to a temp table could force sqlserver to recomple. Any Set statements in the sp will also force sqlserver to recompile(SET NOCOUNT ON for example).

Dropping System Stored Procedures

.
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:

> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz

Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql

Dropping stored procedures

Hello!
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>

Dropping FK Constraints in a Stored Procedure

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

I was furious when I found this because every once in a while I would
notice that the constraints would be in flux...some days they were
there, othere days there were not. I mean, this is a good enough reason
to NOT do this, but I need some additional feedback. Maybe there *is* a
good reason, and that the logic just needs tweaking.

Thanks.The only justification for this technique is developer productivity. It's a
lot easier to write code without those nasty foreign key constraints getting
in the way :-)

Seriously, I can't think of a good reason to do this. There may be some
situations where temporarily removing constraints can improve batch
performance but this kind of thing should only be done during maintenance
windows. Adding and removing constraints during normal operation can lead
to blocking and data integrity issues.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dmitri" <nienna.gaia@.gmail.com> wrote in message
news:1111719489.616490.262060@.f14g2000cwb.googlegr oups.com...
>I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?
> I was furious when I found this because every once in a while I would
> notice that the constraints would be in flux...some days they were
> there, othere days there were not. I mean, this is a good enough reason
> to NOT do this, but I need some additional feedback. Maybe there *is* a
> good reason, and that the logic just needs tweaking.
> Thanks.|||Dmitri (nienna.gaia@.gmail.com) writes:
> I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?

Good reason...

There is a problem with constraints and triggers in SQL Server, as they
always fire at statemet time. Other products have commit-time constraints
and/or triggers. This is good, because that permits you to violate
constraints temporarily in a transaction where it does not matter. One
example is that you have Orders and OrderDetails, and for some reason
need to reallocating order numbers and the constraints are not defined
as cascading. (Maybe because of the many restrictions with cascading
DRI in SQL Server.)

That said, I would say that it vert bad practice to do this in application
code. For a maintenance procedure or a fix proecedure that is run once in
a blue moon it could be acceptable. If there is some really difficult
situation where you must to this, you *must* do it within the realm of a
transaction, so that the final result of the operation in case of an error
or a power failure is that the constraints are gone.

Performance implications? You bet. If, as you say, he drops and re-adds
the constraints, there is a cost for checking the constraints. An
alternative would be to disable the constraints and enabling them
again without checking. In this case, the constraints would not be
trusted by the optimizer, and this could affect query plans. (And of
course, if his logic violated the constraints, no one would know.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Standard SQL has a DEFERABLE option on constraints. They can be turned
on or off by default or by action. But all constraints have to be true
at COMMIT time. I would guess that he is used to DB2 or another SQL
that hs this feature.

SQL Server does not work this way. So this is very dangerous and he
should not be doing it. He can lock up or trash the whole system with
his stored procedures. My guess would be that his procedure ought to
update the table in the proper order, or that the schema has something
really ugly in it, like a cyclic reference.

If this has been goign on for awhile, you better do a data audit, too.|||Thanks for the input all!

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?
sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?
|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?
|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John
|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>
|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John
|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>
|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb
sql

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:
> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try i
t
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Serve
r
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to dro
p *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try
it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Ser
ver
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation,
and I have an
xp_sendmail in master and none in msdb. If you have something called xp_send
mail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Tuesday, March 27, 2012

Dropping Data Connect String

I've created a data source connection using the sa login and password with
the save password option. Running the stored procedure returns a good result
set. Ok, I'm feeling pretty good at this point . But after clicking on the
Preview tab, I receive the following message: "A connection cannot be made
to the database. Set and test the connection string. Login failed for
DIM\tj."
Of course testing the connection works fine, but why is trying to
authenticate using my credentials when it should be use the connect string?
I'm running RS SP1 against a SQL 2000 db.
--
Any and all contributions are greatly appreciated ...
Regards TJIt couldn't be something inside the stored procedure, could it? Can you do a
straight select?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> I've created a data source connection using the sa login and password with
> the save password option. Running the stored procedure returns a good
> result
> set. Ok, I'm feeling pretty good at this point . But after clicking on the
> Preview tab, I receive the following message: "A connection cannot be made
> to the database. Set and test the connection string. Login failed for
> DIM\tj."
> Of course testing the connection works fine, but why is trying to
> authenticate using my credentials when it should be use the connect
> string?
> I'm running RS SP1 against a SQL 2000 db.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>|||If it is, I'm not seeing any errors when I execute the stored procedure on
the Data Tab. Is there an error log for the Data Tab?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> It couldn't be something inside the stored procedure, could it? Can you do
a
> straight select?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "TJ" <nospam@.nowhere.com> wrote in message
> news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > I've created a data source connection using the sa login and password
with
> > the save password option. Running the stored procedure returns a good
> > result
> > set. Ok, I'm feeling pretty good at this point . But after clicking on
the
> > Preview tab, I receive the following message: "A connection cannot be
made
> > to the database. Set and test the connection string. Login failed for
> > DIM\tj."
> >
> > Of course testing the connection works fine, but why is trying to
> > authenticate using my credentials when it should be use the connect
> > string?
> >
> > I'm running RS SP1 against a SQL 2000 db.
> > --
> > Any and all contributions are greatly appreciated ...
> > Regards TJ
> >
> >
>|||I am running into the exact same issue... Any resolution to this yet?
"TJ" wrote:
> If it is, I'm not seeing any errors when I execute the stored procedure on
> the Data Tab. Is there an error log for the Data Tab?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > It couldn't be something inside the stored procedure, could it? Can you do
> a
> > straight select?
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "TJ" <nospam@.nowhere.com> wrote in message
> > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > I've created a data source connection using the sa login and password
> with
> > > the save password option. Running the stored procedure returns a good
> > > result
> > > set. Ok, I'm feeling pretty good at this point . But after clicking on
> the
> > > Preview tab, I receive the following message: "A connection cannot be
> made
> > > to the database. Set and test the connection string. Login failed for
> > > DIM\tj."
> > >
> > > Of course testing the connection works fine, but why is trying to
> > > authenticate using my credentials when it should be use the connect
> > > string?
> > >
> > > I'm running RS SP1 against a SQL 2000 db.
> > > --
> > > Any and all contributions are greatly appreciated ...
> > > Regards TJ
> > >
> > >
> >
> >
>
>|||Here are a couple of things you might try:
a) I was calling a stored procedure from within a stored procedure using the
exec command; The report data connection account didn't have permissions for
the stored procedure inside the main stored procedure. I found the
permissions issue by running the main stored procedure in Query Analyzer,
but you need to open your Query Analyzer connection using the same data
connection information being used in your report.
b) You can hard code the User Id and password setting in the Connection
String on the Data Source Tab for the report.
Good Luck
TJ
"StanDaMon" <StanDaMon@.discussions.microsoft.com> wrote in message
news:102E7428-08C9-467E-8DC6-B69A4E8D094E@.microsoft.com...
> I am running into the exact same issue... Any resolution to this yet?
> "TJ" wrote:
> > If it is, I'm not seeing any errors when I execute the stored procedure
on
> > the Data Tab. Is there an error log for the Data Tab?
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > It couldn't be something inside the stored procedure, could it? Can
you do
> > a
> > > straight select?
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "TJ" <nospam@.nowhere.com> wrote in message
> > > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > > I've created a data source connection using the sa login and
password
> > with
> > > > the save password option. Running the stored procedure returns a
good
> > > > result
> > > > set. Ok, I'm feeling pretty good at this point . But after clicking
on
> > the
> > > > Preview tab, I receive the following message: "A connection cannot
be
> > made
> > > > to the database. Set and test the connection string. Login failed
for
> > > > DIM\tj."
> > > >
> > > > Of course testing the connection works fine, but why is trying to
> > > > authenticate using my credentials when it should be use the connect
> > > > string?
> > > >
> > > > I'm running RS SP1 against a SQL 2000 db.
> > > > --
> > > > Any and all contributions are greatly appreciated ...
> > > > Regards TJ
> > > >
> > > >
> > >
> > >
> >
> >
> >

Dropping constraint on temporary table

I made a constraint on a temporary table in a stored procedure but now i can't delete it.

Here's what happened:
I ran this in a stored procedure

CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.

but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @.name
set @.name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @.name

giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.This kind of problem is symptomatic of multiple sub-problems. You need to reconsider how your application works to truly solve the underlying problem or problems.

To solve the specific issue that you see here, the simplest answer is to drop the temp table itself using something like:DROP TABLE #teFotograferen-PatP|||Pat

That's exactly what defines my problem
If i run
DROP TABLE #teFotograferen

i get
Cannot drop the table '#tefotograferen', because it does not exist or you do not have permission

because the table was a temporary table and there's no way to get back in the scope where it was defined.

If i recreate the table and then drop it the constraint still remains in my database.

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime)
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
Constraint 'UniqueFields' does not belong to table '#tefotograferen'.
because it is not the same table

on the other hand

create table #tefotograferen (rowid int,Stamboeknummer char(11), Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum))
alter table #tefotograferen drop constraint UniqueFields
drop table #tefotograferen
gives me
There is already an object named 'UniqueFields' in the database.

In other words UniqueFields constraint is parentless, and the only way to delete constraint is to alter non-existent parent-table

It is not a design problem in my application, i just put some garbage in that i can't get out|||This kind of problem is symptomatic of multiple sub-problems.That comment wasn't an accident.

One problem is that you are being bitten by concurrent executions of the code that produces your temp table, and possibly by connection pooling too.

You have multiple temp tables, from multiple spids (connections to your database) with a constant constraint name of UniqueFields that is causing subsequent executions of the CREATE TABLE to fail.

I'd be willing to wager that there are other issues too, but these are enough to keep us amused for the moment.

The solution to this problem is to:

a) Stop execution of all running spids (disconnect them) that have a #teFotographen table at the moment.
b) Create the constraint with a default name (which is unique for each execution).

This should get you far enough to find the next problem!

-PatP|||[smacks forehead]

why would you need contraints on a temp table?

[/smacks forehead]|||After a restart of the server the offending constraint was gone.

Brett: Now i know NOT TO USE constraints on temp tables because of these issues. Rather check the data you insert into the temp table before you insert it.

I thought adding a constraint to ensure uniqueness was a good idea, but it seems with temp tables you get these kinds of issues.

But to me this seems like something that should be fixed. The temp table itself isn't visible outside the scope of execution, the constraint on the other hand is... so if you forget to drop the temp table or drop the constraint at the end of your stored procedure the constraint remains in the database until all connections are closed not just those tspids that have a temp table with that name.

Thanks for the advice and input. :beer:

Sunday, March 25, 2012

Dropping / Recreating Indexes

All -
You know when you generate the SQL script for a stored procedure, you get
that initial script at the top that first checks for the procedure in
sysobjects and drops it if it is found?
I want to do the same thing with indexes. I want to create a script that
will first delete a given index if its found and then create a new one.
How would you go about do this?
Thanks,
- Mattif exists (select * from dbo.sysindexes where name = 'IX_index_01' and id =object_id('dbo.tableA'))
drop index dbo.tableA.IX_index_01
GO
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||All -
Sorry about that. I found the DROP INDEX syntax.
Now it looks like the only problem is that if the index is a primary index,
you can't drop it. You have to drop the table, create the table, and then
re-create the index.
Does this sound right?
Thanks,
- Matt
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||Primary Key indexes are created as part of the Primary Key
Constraint. You cannot directly drop the index, but
instead you drop it by dropping the Primary Key
Constraint. So you don't have to drop the table. You must,
however, first drop any Foreign Key Constraints that
reference the PK.
HTH
Vern
>--Original Message--
>All -
>Sorry about that. I found the DROP INDEX syntax.
>Now it looks like the only problem is that if the index
is a primary index,
>you can't drop it. You have to drop the table, create
the table, and then
>re-create the index.
>Does this sound right?
>Thanks,
>- Matt
>
>"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
>news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
>> All -
>> You know when you generate the SQL script for a stored
procedure, you get
>> that initial script at the top that first checks for
the procedure in
>> sysobjects and drops it if it is found?
>> I want to do the same thing with indexes. I want to
create a script that
>> will first delete a given index if its found and then
create a new one.
>> How would you go about do this?
>> Thanks,
>> - Matt
>>
>
>.
>|||Hi Matt
No, you certainly don't have to drop the whole table just to drop an index!
If an index supports a primary key constraint, you have to drop the
constraint, and that will automatically drop the index that supports the
constraint.
You can then readd the constraint.
sp_helpconstraint will show you the constraint names
to drop and re-add the constraint use:
ALTER TABLE ... DROP CONSTRAINT
ALTER TABLE ... ADD CONSTRAINT
Full details are in Books Online
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>|||What's the reason behind dropping and recreating the index? If the reason
is to rebuild it (remove fragmentation), then use DBCC DBREINDEX. This will
rebuild your index and you don't need to deal with dropping constraints.
Gail Erickson [MSFT]
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>

Dropdownlist with stored procedure problem

Hi Everyone,

I am trying to load the data into the dropdownlist using stored procedure. But when I run the code, the dropdownlist is empty. The code is shown below. Please help! Thanks.

public DataSet getProvince()
{
DataSet ds = new DataSet();
SqlParameter myParam;

string conString;
SqlConnection myConnection;

conString = ConfigurationManager.AppSettings["connectionString"];
myConnection = new SqlConnection(conString);

SqlCommand cmd = new SqlCommand("stored_procedure_GetProvinces", myConnection);

SqlDataAdapter adpt = new SqlDataAdapter(cmd);

try
{

cmd.CommandType = CommandType.StoredProcedure;
myParam = cmd.Parameters.Add("@.province_key", SqlDbType.Int);
myParam.Direction = ParameterDirection.Output;

myParam = cmd.Parameters.Add("@.province_name", SqlDbType.NVarChar, 200);
myParam.Direction = ParameterDirection.Output;

adpt.Fill(ds, "Provinces");

myConnection.Close();

}
catch (SqlException ex)
{
Response.Write("Error: " + ex.Message);
}
return ds;
}

-----------

create procedure stored_procedure_GetProvinces( @.province_key int output, @.province_name nvarchar (200) output)AsSelect @.province_key=province_key, @.province_name=province_name From provinceGO

--------

<asp:DropDownList id="Dropdownlist_Province" DataValueField="province_key" DataTextField="province_name" DataSource='<%# getProvince() %>' Runat="server" >
</asp:DropDownList>

----------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO

CREATE TABLE province (
province_key int IDENTITY PRIMARY KEY,
province_name nvarchar (200) NULL
)
GO

INSERT INTO province(province_name) VALUES
('- Select -');
GO
INSERT INTO province (province_name) VALUES
('Ontario');
GO
INSERT INTO province (province_name) VALUES
('Alberta');
GO
INSERT INTO province (province_name) VALUES
('British Columbia');
GO
INSERT INTO province (province_name) VALUES
('Manitoba');
GO
INSERT INTO province(province_name) VALUES
('New Brunswick');
GO
INSERT INTO province (province_name) VALUES
('Newfoundland');
GO
INSERT INTO province (province_name) VALUES
('Northwest Territories');
GO
INSERT INTO province (province_name) VALUES
('Nova Scotia');
GO
INSERT INTO province (province_name) VALUES
('Nunavut');
GO
INSERT INTO province (province_name) VALUES
('Prince Edward Island');
GO
INSERT INTO province (province_name) VALUES
('Quebec');
GO
INSERT INTO province (province_name) VALUES
('Saskatchewan');
GO
INSERT INTO province (province_name) VALUES
('Yukon Territory');
GO

What is the value of your parameter?|||

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

|||

bullpit:
What is the value of your parameter?

Sorry, I don't know. Thanks.

|||

ndinakar:
what you need is:
Select province_key, province_nameFrom province
also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

After I modify my code and my stored procedure. I still get the empty dropdownlist. Please help. Thanks.

|||did you change the proc to the way I suggested? Also please post your new .NET code..|||

ndinakar:
did you change the proc to the way I suggested? Also please post your new .NET code..

My updated code is shown below. Thanks!

create procedure

stored_procedure_GetProvinces

(

@.province_key

intoutput,

@.province_name nvarchar (200)

output

)

Select

province_key, province_nameFromprovince

--------

publicDataSet getProvince()

{

DataSet ds =newDataSet();SqlParameter myParam;string conString;SqlConnection myConnection;

conString =

ConfigurationManager.AppSettings["connectionString"];

myConnection =

newSqlConnection(conString);SqlCommand cmd =newSqlCommand("stored_procedure_GetProvinces", myConnection);SqlDataAdapter adpt =newSqlDataAdapter(cmd);try

{

cmd.CommandType =

CommandType.StoredProcedure;

adpt.Fill(ds,

"Provinces");

myConnection.Close();

}

catch (SqlException ex)

{

Response.Write(

"Error: " + ex.Message);

}

return ds;

}

--------- the end -------

|||

ndinakar:
what you need is:
Select province_key, province_nameFrom province
also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

I have just tried using ExecuteReader. And I still get empty dropdownlist. Please help. Thanks.

SqlCommand objCmd;

SqlConnection objConn;

SqlDataReader dataReader;

String strSql;

objConn =newSqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));

strSql ="EXECUTE stored_procedure_GetProvinces";

objCmd =newSqlCommand(strSql, objConn);

try

{

objConn.Open();

dataReader = objCmd.ExecuteReader(

CommandBehavior.CloseConnection);

Dropdownlist_Provinces.DataSource = dataReader;

Dropdownlist_Provinces.DataTextField =

"province_name";

Dropdownlist_Provinces.DataValueField =

"province_name";

Dropdownlist_Provinces.DataBind();

}

catch

{

}

finally

{

objConn.Close();

objConn.Dispose();

}

|||

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

|||

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

|||

MayLam:

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

I replace SqlCmd1 with myCommand. And it is working now. Thank you very much for your help! Bye!

May

DropDownList instead of Textbox in Reporting Services

Hello,
I have a report that uses a stored procedure. The parameter from the stored procedure is a name field defined nvarchar(50). But I have a list of values for this field in another table; is there anyway to use the values from the other table to populate a dropdownlist of these values, instead of prompting the user for a textbox?
Thanks.Hi ,
for populating drop down list instead of default text box, you have to create another dataset which will fetch fields from another table.
while in layout, go to Report --> Report Parameters..
click on the parameter on left and on right side, select radio btn From query and specify dataset and select fields.
you can also select default value also. i think this is what u want right?
Thanks
Rohit
|||

Hey,

Yes, that is exactly what I was looking to do. Thanks a lot.

|||That's good for designing reports on the server (RDL). I'm using Visual Web Developer Express and cliking on Report-Report Parameters it seems there's no option for configuring parameters to come from a dataset. Is there a way around this?|||No takers so far. So let me re-phrase the question. Is there any way by which I can move parametrized reports from the server (RDL) to the client (RDLC)?|||Sure you can. You need to use the parameter objects to pass parameters to the report.

DropdownLIst box question, Stored Procedure

Hi All,

I have a dropdownlist box with values (All, Paris, London, New York) -- Cities

Another dropdwonlist box with values (All, Bank of America, City Bank, CIBC) -- Banks

And have a stored procedure that populates the info:

CREATE Procedure Search
(
@.City nvarchar(50),
@.Bank nvarchar(50)
)
AS SELECT * FROM TableA WHERE
city = @.city AND bank= @.bank GO

If user selects a value anything other than "All" in both dropdownbox, everything is OK.

My question is, if user selects "All" in any of these dropdown combobox, how can I seach for all Cities or Banks.

SQL needs to run as a stored procedure. If it was on client end, it would be alot easier.

Any ideas?

Thanks for your help.

Duplicate post, see
http://forums.asp.net/1138420/ShowPost.aspx

Thursday, March 22, 2012

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
--
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:
> There is no need to issue FREEPROCCACHE as each time you edit the sp it will
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> > I've been doing some stored proc tuning and I want to ensure that after
> > the
> > changes I make to the query the execution times are accurate. So
> > basically,
> > if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> > users, am I clearing ALL buffers/proccache that SQL is using, or just the
> > ones tied to my connection?
>
>|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
>> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
> (without
>> change) every time I ran it after the first time. So SQL must have been
>> acessing the data from the buffer? Thanks for your quick response.
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>sql

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:

> There is no need to issue FREEPROCCACHE as each time you edit the sp it wi
ll
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
>
>|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> (without
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?
Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?
|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?
|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:

> There is no need to issue FREEPROCCACHE as each time you edit the sp it will
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
>
>
|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> (without
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>

Drop User Command

What is the stored procedure for drop a user? Drop_user
userx...
Thanks,
Brady Snow
McKinney, Texas
See:
sp_dropuser
sp_revokedbaccess
sp_droplogin
in Books Online (BOL)
Rohtash Kapoor
http://www.sqlmantra.com
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas
|||Hi,
In SQL Server you will be having Login and Users.
Login : Login to authenticate inside SQL server when you use SQL server
authnetication
User: Who got previlege to access the databases
So before deleting the Login you have to drop the user
Command to drop user:
sp_dropuser <user_name>
Command to drop Login
sp_droplogin <login_name>
Apart from this refere the below commands in books online:
1. sp_revokelogin <Loginame>
2.sp_revokedbaccess <user_name>
Thanks
Hari
MCDBA
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas

Drop the time portion of a smalldatetime field

In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
instead of 1/31/2004 10:30:25 AM
How can I do this in a stored procedure?
lqI'm not sure if this is the *cleanest* way, but you could do
CONVERT(SMALLDATETIME, myDateField, [100-1??]). If you look at the
online help for "CONVERT", you'll be able to see the optional paramater
at the end that allows you to format the data however you want. It is a
number that corresponds to a format. I use 102 which equals
'yyyy.mm.dd', but there are many others. Hope that helps.

Lauren Quantrell wrote:
> In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
> instead of 1/31/2004 10:30:25 AM
> How can I do this in a stored procedure?
> lq|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401271927.585c8a97@.posting.google.com>...
> In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
> instead of 1/31/2004 10:30:25 AM
> How can I do this in a stored procedure?
> lq

See the CONVERT() function in Books Online:

select convert(char(10), MyColumn, 101)
from dbo.MyTable

But in general you should format dates in your client application if
possible, as it's easier to handle different client regional settings
etc.

Simon

Monday, March 19, 2012

Drop stored procedures by...

Hi,
How do I drop all stored procedures with the prefix of "www_".
Basically, I need to add all new dev DB sprocs to our prod DB, but on the
prod DB there are a whole bunch of old sprocs that needs to be dropped.
Thanks in advance
ChristianFrom Vyas:
On SQL Server 2000 the following will give you a list of all stored
procedure names in the database:
SELECT ROUTINE_SCHEMA AS Owner,
ROUTINE_NAME AS ProcedureName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
The following will return the DROP PROC commands that you need to run:
SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) AS Command
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Christian Perthen" <abracadabara@.dontreplytothi

dress.com> schrieb im
Newsbeitrag news:e2VlEOESFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How do I drop all stored procedures with the prefix of "www_".
> Basically, I need to add all new dev DB sprocs to our prod DB, but on the
> prod DB there are a whole bunch of old sprocs that needs to be dropped.
> Thanks in advance
> Christian
>|||Must be sure changed in some way, sorry i forgot:

> SELECT ROUTINE_SCHEMA AS Owner,
> ROUTINE_NAME AS ProcedureName
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
> The following will return the DROP PROC commands that you need to run:
> SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
> QUOTENAME(ROUTINE_NAME) AS Command
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
>
Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:uLPtCWESFHA.1996@.TK2MSFTNGP10.phx.gbl...
> From Vyas:
> On SQL Server 2000 the following will give you a list of all stored
> procedure names in the database:
> SELECT ROUTINE_SCHEMA AS Owner,
> ROUTINE_NAME AS ProcedureName
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
> The following will return the DROP PROC commands that you need to run:
> SELECT 'DROP PROC ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
> QUOTENAME(ROUTINE_NAME) AS Command
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME LIKE 'www_%'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Christian Perthen" <abracadabara@.dontreplytothi

dress.com> schrieb im
> Newsbeitrag news:e2VlEOESFHA.248@.TK2MSFTNGP15.phx.gbl...
>

Thursday, March 29, 2012

Dropping Temporary Tables

Dropping System Stored Procedures

Dropping System Stored Procedures

Dropping stored procedures

Dropping FK Constraints in a Stored Procedure

Dropping Extended Stored Proc

Dropping Extended Stored Proc

Dropping Extended Stored Proc

Tuesday, March 27, 2012

Dropping Data Connect String

Dropping constraint on temporary table

Sunday, March 25, 2012

Dropping / Recreating Indexes

Dropdownlist with stored procedure problem

DropDownList instead of Textbox in Reporting Services

DropdownLIst box question, Stored Procedure

Thursday, March 22, 2012

DROPCLEANBUFFERS/FREEPROCCACHE

DROPCLEANBUFFERS/FREEPROCCACHE

DROPCLEANBUFFERS/FREEPROCCACHE

Drop User Command

Drop the time portion of a smalldatetime field

Monday, March 19, 2012

Drop stored procedures by...

DSO in SQL2005

Blog Archive

About Me