IF EXISTS for drop table.

what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
--
Message posted via http://www.sqlmonster.comif object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via SQLMonster.com" wrote:
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.com
>|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Grant H via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.SQLMonster.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.com|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.SQLMonster.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.comsql

IE cannot access Webpage for Report Manager-what can I do?

Greetings,
I am trying to access the Report Manager website, but I am getting an error
message (http 403) that IE can connect to the website but doesn't have the
permision - requires login. Here is what I did - I am at the server computer
logged in as administrator (the builtin administrator - sa). I go to
Start/all programs/Microsoft Sql Server/Reporting Services/Report Manager.
This brings up IE to the webpage where I get the message that I am not
authorized to access the site (error http 403) and that I need to log in.
But there is no login box anywhere. What do I need to do to access the
Report Manager site? Do I need to configure something in my Server
Administration? Or do I need to configure something in IIS?
Thanks,
RichI went to IIS to the Reports Virtual directory to Pages and found
Folder.aspx. The security set on the Reports dir is Window Integrated
security and the same for the Pages directory. It says that the user will
need to enter an ID/password. But when I try to bring up
http://myserver/reports/pages/folder.aspx all I get is the error message
"...Forbidden..." and I do not get the logon dialog.
Do I need to modify something in IIS?
"Rich" wrote:
> Greetings,
> I am trying to access the Report Manager website, but I am getting an error
> message (http 403) that IE can connect to the website but doesn't have the
> permision - requires login. Here is what I did - I am at the server computer
> logged in as administrator (the builtin administrator - sa). I go to
> Start/all programs/Microsoft Sql Server/Reporting Services/Report Manager.
> This brings up IE to the webpage where I get the message that I am not
> authorized to access the site (error http 403) and that I need to log in.
> But there is no login box anywhere. What do I need to do to access the
> Report Manager site? Do I need to configure something in my Server
> Administration? Or do I need to configure something in IIS?
> Thanks,
> Rich

IE 7 B3 Not Compatible With RS2005

Lon posted a message on the RS UseNet newsgroup about IE 7 B3 being unresponsive and using 100% of the cpu when trying to view RS reports.

I tried it on a test system, and indeed the problem showed up for me as well. Both the IE 7 B3 32 bit and 64 bit version where stuck in a loop when attempting to view RS 2005 reports. I tried both parameter and non-parameterized reports with the same results. The only way to stop it is via Task Manager.

A warning to all, do not install IE 7 B3 if you want to run RS reports.

Hey MS, you may want to pass this on to IE 7 development team. This
isn't good when MS products aren't compatible with each other.

I have IE Beta 3 and work fine with RS 2005.

May be a special report.

|||I experienced the same problem, the screen starts flickering and IE won′t come back. If you open up a BUG on connect.microsoft.com I will vote on it.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes, please open a bug so we can take a look. Do you also see the problem using URL Access? ie:

http://<server>/reportserver?/Folder/Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false

Thanks, Donovan.

|||

So I just tried that, if called directly via the Webservice and the path string, the problem is not occuring. Unfortunaly if you use this method you would have to pass the parameters via URL string, so I opened up a bug targeting this problem. Thats even not a problem of complexity, I just used a blank report with one textbox and deployed that to the server, which will also cause IE to hang.

I filed the BUG under https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=157905

Please vote on it, if you also experienced the problem.

Danovan, could you share this feedback along with the IE 7 group, I don′t know who is responsible for that, I filed the bug under SQL Server not IE.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Filing it under SQL is the right place to put this issue. We have a lot of script that executes when viewing the report through the report manager that doesn't execute when using URL Access. We'll take a look at what's going on and let the IE team know what we find, if necessary.

I just upgraded my laptop to IE7 B3 and see the same behavior before a report is even present (ie, just blank parameters are showing). Thanks for pointing this out.

Thanks, Donovan.

|||

We've identified the problem and are discussing the fix with the IE team. Thanks for reporting this.

Donovan

|||

If there is a hotfix etc available please post. I am flipping between two machines to view my reports.

Thanks

|||

A fix has gone into IE7 so B2 and RTM should work fine. Unfortunately, in the meantime, there is no work-around for B3.

Thanks, Donovan.

|||http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/17/Default.aspx

FYI

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

IE 7 B3 Not Compatible With RS2005

Lon posted a message on the RS UseNet newsgroup about IE 7 B3 being unresponsive and using 100% of the cpu when trying to view RS reports.

I tried it on a test system, and indeed the problem showed up for me as well. Both the IE 7 B3 32 bit and 64 bit version where stuck in a loop when attempting to view RS 2005 reports. I tried both parameter and non-parameterized reports with the same results. The only way to stop it is via Task Manager.

A warning to all, do not install IE 7 B3 if you want to run RS reports.

Hey MS, you may want to pass this on to IE 7 development team. This
isn't good when MS products aren't compatible with each other.

I have IE Beta 3 and work fine with RS 2005.

May be a special report.

|||I experienced the same problem, the screen starts flickering and IE won′t come back. If you open up a BUG on connect.microsoft.com I will vote on it.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes, please open a bug so we can take a look. Do you also see the problem using URL Access? ie:

http://<server>/reportserver?/Folder/Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false

Thanks, Donovan.

|||

So I just tried that, if called directly via the Webservice and the path string, the problem is not occuring. Unfortunaly if you use this method you would have to pass the parameters via URL string, so I opened up a bug targeting this problem. Thats even not a problem of complexity, I just used a blank report with one textbox and deployed that to the server, which will also cause IE to hang.

I filed the BUG under https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=157905

Please vote on it, if you also experienced the problem.

Danovan, could you share this feedback along with the IE 7 group, I don′t know who is responsible for that, I filed the bug under SQL Server not IE.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Filing it under SQL is the right place to put this issue. We have a lot of script that executes when viewing the report through the report manager that doesn't execute when using URL Access. We'll take a look at what's going on and let the IE team know what we find, if necessary.

I just upgraded my laptop to IE7 B3 and see the same behavior before a report is even present (ie, just blank parameters are showing). Thanks for pointing this out.

Thanks, Donovan.

|||

We've identified the problem and are discussing the fix with the IE team. Thanks for reporting this.

Donovan

|||

If there is a hotfix etc available please post. I am flipping between two machines to view my reports.

Thanks

|||

A fix has gone into IE7 so B2 and RTM should work fine. Unfortunately, in the meantime, there is no work-around for B3.

Thanks, Donovan.

|||http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/17/Default.aspx

FYI

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

Identity range managed by replication is full and must be updated by a replication agent.

Hello,

I'm getting the following error message when I try add a row using a
Stored Procedure.

"The identity range managed by replication is full and must be updated
by a replication agent".

I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )

sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

For Testing:

I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.

I've checked the current Identity values in the Agent Table:

DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.

I check the Table to make sure there will be no conflicts with the
primary key:

SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.

Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:

Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80

In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?

The Identity range for this table cannot be exhausted! I'm not sure
what to try next.

Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:

Quote:

Originally Posted by

I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.

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

Identity column value increments by 2 rather than 1 on insert

I have an issue with a stored procedure activation on a service broker queue. The activation stored procedure simply RECEIVES the top message and then INSERTs a row into a table with an identity INT column. Each row inserted has the identity column value incremented by 2 rather than 1. Only one row is inserted in the table.

If the activation is set to OFF and then manually calling the original stored procedure the insert works fine and the identity column value is only incremented by one.

Do you have any suggestions on why the identity column value increments by 2?

Thanks.

I tested what you describe and the identities are implemented by one, as expected.

Can you post the code of your procedure?

|||

Here are some simple test scripts to create the service broker logic:

-- Configure Queue

CREATE MESSAGE TYPE TestMessage VALIDATION = NONE

CREATE CONTRACT TestContract (TestMessage SENT BY INITIATOR)

CREATE QUEUE QueueTest WITH STATUS = ON, ACTIVATION ( PROCEDURE_NAME = PTest,

MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' )

CREATE SERVICE ServiceTest ON QUEUE QueueTest ( TestContract )

-- Send procedure

CREATE PROCEDURE [dbo].[PTestLogMessage]

@.msg_details varchar(390)

AS

BEGIN

DECLARE @.handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.handle FROM SERVICE ServiceTest TO

SERVICE 'ServiceTest' ON CONTRACT TestContract WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.handle MESSAGE TYPE TestMessage ( @.msg_details )

END

-- Activation procedure

CREATE PROCEDURE [dbo].[PTest]

AS

DECLARE @.msg VARCHAR(390)

BEGIN

RECEIVE TOP(1) @.msg = message_body

FROM dbo.QueueTest

-- This insert results in the identity column value incrementing by 2

INSERT INTO t_test ( msg )

VALUES (@.msg)

END

-- table

CREATE TABLE [dbo].[t_test]([id] [int] IDENTITY(1,1) NOT NULL,

[msg] [varchar](390) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_t_test] PRIMARY KEY CLUSTERED

([id] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

|||

Your activated procedure is not guaranteed to RECEIVE a message each time it's activated. In other words, you should expect that RECEIVE returns an empty rowset. In fact, if you do no loop inside your procedure until you hit an empty resultset (hit 'bottom' of queue), the activation launcher will loop for you, and call the procedure again. So the activated procedure code you showed is pretty much guaranteed to be called twice in a row for each message enqueuef, once because it was activated and once because the activation did not see that you hit an empty RECEIVE. The seconf time you will hit one. Because you do not check whether the RECEIVE returned or not an empty rowset (@.@.ROWCOUNT > 0) you insert twice for each message, so the identity is incremented by two.

Also you should project into RECEIVE the conversation_handle and message_type_name and repsond properly to 'EndDialog' and 'Error' messages, and the conversations have to be eventually ended, otherwise the sys.conversation_endpoints will grow out of control.