Thursday, March 29, 2012
Dropping tempe tables
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
Wednesday, March 21, 2012
drop table
I upgraded my system from Sql2000 to sql2005.
And now i have a problem.
I have job wich runs every minute and perform the following
select * into tabler_1 from .....
begin tran
if exists(select * from sysobjects where xtype ='u' and name = 'tabler')
drop table tabler
exec sp_rename tabler_1, tabler
commit
And on client side i have IIS which runs asp pages which connect to database with
ado connction using ODBC and runs procedures wich performs select * from tabler
and there is a problem they some times failes with error tabler does not exists.
It worked fine in sql2000
all connections opened with default setting.
(transaction isolation level read commited)
What abou this here:
if not exists
(
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'tabler_1'
AND TABLE_TYPE = 'BASE_TABLE'
)
select * into tabler_1 from SomeTable
if exists
(
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'tabler'
AND TABLE_TYPE = 'BASE_TABLE'
)
BEGIN
DROP TABLE tabler
EXEC sp_rename tabler_1, tabler
END
Make sure that you check the existence of the table before doing a SELECT INTO.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||It is not a problem.
it make drop very nice.
the problem is on the client which runs store procedure like this
create procedure test_sp
as
select * from tabler
go
OR
create procedure test_sp_d
as
exec ('select * from tabler')
go
when i droping table i do it in transaction so schema is locked
the procedures have to wait for commit and then select
it worked fine sql2000
sqlSunday, February 26, 2012
Drop & Create sProc
that I will want to perform monthly. I would like to put the script into a
sproc, however when I try to compile it I receive an error that the tables
and indexes already exist in my database.
Is there any way around this other than dropping all the tables before I
compile
the sproc?
Thanks,
MarcEncapsulate the CREATE / DROP statements within an IF ?
IF OBJECT_ID('[YourObject]','U') IS NOT NULL
BEGIN
DROP ....
CREATE ...
END
"Marc Miller" wrote:
> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script into
a
> sproc, however when I try to compile it I receive an error that the tables
> and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile
> the sproc?
> Thanks,
> Marc
>
>|||Marc Miller (mm1284@.hotmail.com) writes:
> I generated SQL Script to drop and recreate some tables in my database
> that I will want to perform monthly. I would like to put the script
> into a sproc, however when I try to compile it I receive an error that
> the tables and indexes already exist in my database.
> Is there any way around this other than dropping all the tables before I
> compile the sproc?
Unless you are running SQL 6.5, you should not get that error.
I suspect that you have something that looks like:
CREATE PROCEDURE yoursp AS
CREATE TABLE abc
CREATE INDEX abc_ix ON abc(def)
go
CREATE TABLE xyz
CREATE INDEX xyz_ix ON xyz(www)
go
This is a script that first creates a procedure, and then creates a table.
This is because the script includes "go" which is an instruction to
the query tool that this is where the batch ends. "go" is not an SQL
command.
Thus, you would have to remove the "go" to include all code in the
stored procedure. However, you may then find that run into other
problems, because you have commands that must be in separate batches.
I would suggest that you store the script as a separate file. This is
would you should with stored procedures as well. The database should
be seen as a binary repository.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, February 19, 2012
DrillThrough Automation
Based on a User's credentials, I perform a query within our company's Sales
Org to determine which Branches and Sales Reps are displayed in Parent
Report's Table.
Then, when the user clicks on a Sales Rep, a secondary Report gets
"DrillThrough" to (not a sub-report) for the Rep's details... So far, so good.
If however, the User is simply a Sale Rep, I want the Parent report to
automatically DrillThrough to second Report, passing of course, the known
necessary UserID info as the parameter.
Ideally, the Rep wouldn't even see the initial Parent Report.
So I guess my question is...
If a Report Parameter has a specific value, can a DrillThrough Report get
"launched" automatically,
e.g. without the user needing to "click" on the object which DrillThroughs to
the second report with the appropriate parameter ?
Many thanks in advance,Drill through works with action/click event, even if you use conditions still
it waits for the click. So I would suggest you need to create a seperate
report for sales reps , even if you create; it has a back button (arrow) so
that it will go to parent report, which you intend not to show it to the reps.
Amarnath
"Deano" wrote:
> Sorry for repeating myself... I'm still looking for a workaround here...
> Based on a User's credentials, I perform a query within our company's Sales
> Org to determine which Branches and Sales Reps are displayed in Parent
> Report's Table.
> Then, when the user clicks on a Sales Rep, a secondary Report gets
> "DrillThrough" to (not a sub-report) for the Rep's details... So far, so good.
> If however, the User is simply a Sale Rep, I want the Parent report to
> automatically DrillThrough to second Report, passing of course, the known
> necessary UserID info as the parameter.
> Ideally, the Rep wouldn't even see the initial Parent Report.
> So I guess my question is...
> If a Report Parameter has a specific value, can a DrillThrough Report get
> "launched" automatically,
> e.g. without the user needing to "click" on the object which DrillThroughs to
> the second report with the appropriate parameter ?
> Many thanks in advance,