Showing posts with label perform. Show all posts
Showing posts with label perform. Show all posts

Thursday, March 29, 2012

Dropping tempe tables

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.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

sql

Sunday, February 26, 2012

Drop & Create sProc

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,
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

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,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,