IDENTITY_INSERT Issue

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Login scripting to a file I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis. Anyone have any examples to share Tag: IDENTITY_INSERT Issue
  - 2
    - truncation error message Guys, please read the error messages. Please! The error is with the flat file source, not the derived column. Make sure that the flat file source's column is wide enough to hold all of the characters in the flat file. The truncation is occurring there. Tag: IDENTITY_INSERT Issue
  - 3
    - SQL Parameter and wildcards, how to make it work? Hello, I have what should be a very simple problem, but I cant solve it. I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!) Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example: DECLARE @Dv_id nchar ( 15 ) SET @Drv_id = '%' SELECT Diver .*, ( ROW_NUMBER () OVER ( ORDER BY Dv_id )) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id SELECT Diver .*, ( ROW_NUMBER () OVER ( ORDER BY Dv_id )) as RowNum FROM Diver WHERE Dv_id LIKE '%' OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows. The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between: WHERE Drv_id LIKE @Drv_id and WHERE Drv_id LIKE '%' The wildcard statement '%' is supposed match everything, correct It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL. Is there any way around this Tag: IDENTITY_INSERT Issue
  - 4
    - SSIS Logs Why don't you use an Execute SQL Task to perform your logging insert Place an Execute SQL Task between each data flow. Tag: IDENTITY_INSERT Issue
  - 5
    - Received message constantly processed. Hi Remus, thank you very much for your answer... Maybe it is not necessarely but this is the SP wuch start the conversation (activated by a trigger on a insert ) PROCEDURE [dbo] . [sp_sendInserted] @MessageXML nvarchar ( max ) AS BEGIN DECLARE @dialog_handle uniqueidentifier ; DECLARE @dialog_id uniqueidentifier ; DECLARE @msg XML ; DECLARE @Error INT ; SET NOCOUNT OFF ; BEGIN TRY BEGIN TRANSACTION ; set @msg = Convert ( xml , @MessageXML ); WHILE ( 1 = 1 ) BEGIN set @dialog_handle = ( select conversation_handle from sys.conversation_endpoints where far_service = 'ReceivedService' ); if @dialog_handle is null --Begin new dialog BEGIN BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE [SendService] TO SERVICE 'ReceivedService' ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ; BEGIN CONVERSATION TIMER ( @dialog_handle ) TIMEOUT = 600 ; END ; SEND ON CONVERSATION @dialog_handle MESSAGE TYPE MyMessage ( @msg ); END COMMIT TRANSACTION ; END TRY BEGIN CATCH IF XACT_STATE () = - 1 BEGIN ROLLBACK TRANSACTION ; END Insert into [dbo] . [tblErrorXMLMessages] values ( @MessageXML , @dialog_handle , @dialog_id ,null,null, ERROR_NUMBER (), ERROR_MESSAGE (), ERROR_PROCEDURE (), getdate ()); END CATCH END And this is the SP activated on the ReceiveQueue: PROCEDURE [dbo] . [OnReceivedMessage] AS DECLARE @message_type INT ; DECLARE @messagetypename NVARCHAR ( 1000 ); DECLARE @XMLmessage_body XML ; DECLARE @dialog UNIQUEIDENTIFIER ; DECLARE @dialog_id UNIQUEIDENTIFIER ; DECLARE @ErrorSave INT ; DECLARE @ErrorDesc NVARCHAR ( 100 ); SET NOCOUNT ON ; WHILE ( 1 = 1 ) BEGIN BEGIN TRY BEGIN TRANSACTION ; WAITFOR ( RECEIVE top ( 1 ) -- just handle one message at a time @message_type = message_type_id , --the type of message received @messagetypename = message_type_name , @XMLmessage_body = message_body , -- the message contents @dialog = conversation_handle -- the identifier of the dialog this message was received on FROM [ReceivedQueue] ) --, timeout 5000; -- if the queue is empty for three second --Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages set @dialog_id = ( select conversation_id from sys.Conversation_endpoints where conversation_handle = @dialog ); -- If we didn't get anything, bail out if ( @@ROWCOUNT = 0 ) BEGIN INSERT INTO [TestReceiver] . [dbo] . [tblReceived] VALUES ( 'Received SP' , '<ReceivedQueue>NO more messages in the queue</ReceivedQueue>' ); COMMIT ; BREAK ; END If ( @messagetypeName = N 'MyMessage' ) BEGIN DECLARE @ret integer ; EXECUTE @ret = [TestReceiver] . [dbo] . [sp_ShredXMLMessageToRelationalData] @XMLmessage_body ; -- SP that PROCESSES THE MESSAGE RECEIVED ON THE QUEUE if ( @ret <> 0 ) INSERT INTO [TestSender] . [dbo] . [tblErrorXMLMessages] VALUES ( cast ( @XMLmessage_body as nvarchar ( max )) , @dialog , @dialog_id ,null ,null , 0 ,null ,null , getdate ()); END ELSE IF ( @messagetypeName = N 'EndOfStream' ) BEGIN END CONVERSATION @dialog ; END -- Check for the Error Dialog message. ELSE IF ( @messagetypeName = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' ) BEGIN DECLARE @WrongXMLMessage NVARCHAR ( max ); set @WrongXMLMessage = ( Select message_body FROM dbo . TradeReceivedQueue WITH ( NOLOCK ) where Message_type_name = 'MyMessage' and status = 3 and conversation_handle = @dialog ); DECLARE @Error int ; DECLARE @ErrorDescription nvarchar ( 4000 ); WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb ) SELECT @Error = cast ( @XMLMessage_Body as XML ). value ( '(//ssb:Error/ssb:Code)[1]' , 'INT' ), @ErrorDescription = cast ( @XMLMessage_Body as XML ). value ( '(//ssb:Error/ssb escription)[1]' , 'nvarchar(4000)' ) IF @WrongXMLMessage is null BEGIN SET @WrongXMLMessage = N '<Error>Error retrieving the wrong XML message from the sender queue</Error>' ; END Insert into [TestSender] . [dbo] . [tblErrorXMLMessages] values ( @WrongXMLMessage , @dialog , @dialog_id , 'MArina' , 'ReceivedQueue' , @Error , @ErrorDescription , ERROR_PROCEDURE (), getdate ()); --After the End Conversation all messages from the queue are deleted END CONVERSATION @dialog ; END COMMIT TRANSACTION ; END TRY BEGIN CATCH insert into [testReceiver] . [dbo] . [tblReceived] values ( 'Error' , '<Marina>Error in SP Traget QUEUE</Marina>' ); END CATCH END Let me know whether you need more info regarding contract, queues etc..etc.. Thank you very much! Marina B. Tag: IDENTITY_INSERT Issue
  - 6
    - How to Send a SQL Server notification to Messenger Hello, I'm trying to send a SQL Server notification to MSNMessenger and also to Yahoo Messenger, but I didn't find the way. If you have some information about it, please answer this question as soon as possible. Thank you so much Tag: IDENTITY_INSERT Issue
  - 7
    - Error with SMO.TransferData() (Exception from HRESULT: 0xC0011008) Thank you for the example, Aaron. However, it did not work. Please note that I cannot connect to the default instance of SQL Server (also note that I only have SQL Express installed). I did try to apply your approach as best I could considering that I need to connect to the User Instance of SQL Express running with my application: Code Snippet Dim srv As Smo.Server Dim source As Smo.Server '*******This is the code to determine the Master Database name, used in original code Dim dbName_Master As String Dim SqlCon_Master As SqlClient.SqlConnection = New SqlClient.SqlConnection( My .Settings.LM_Master_SQLConStr) SqlCon_Master.Open() dbName_Master = SqlCon_Master.Database.ToString() 'This did not help: SqlCon_Master.ChangeDatabase("master") Console.WriteLine( "Master DB Name: " & dbName_Master) '******* Dim srvCon As New ServerConnection(SqlCon_Master) srv = New Smo.Server(srvCon) ''Verify User Instance connection Console.WriteLine( "User Instance Server: " & srv.InstanceName) If srv.Databases.Contains( "MasterDB_Copy" ) Then srv.Databases( "MasterDB_Copy" ).Drop() End If Dim newDB As New Smo.Database(srv, "MasterDB_Copy" ) newDB.Create() 'Create connection to Source DB Dim sourceCon As New ServerConnection(SqlCon_Master) source = New Smo.Server(sourceCon) ''Verify User Instance connection Console.WriteLine( "User Instance Server: " & source.InstanceName) Dim MasterDB As Smo.Database = source.Databases(dbName_Master) Dim xfr As New Smo.Transfer(MasterDB) xfr.CopyAllObjects = True xfr.DropDestinationObjectsFirst = True xfr.CopySchema = True xfr.CopyData = True xfr.DestinationServer = srv.Name xfr.DestinationDatabase = newDB.Name xfr.Options.IncludeIfNotExists = True Try xfr.TransferData() Catch ex As Exception Console.WriteLine( "Error: " & ex.Message) End Try srv = Nothing source = Nothing I still get the same error upon reaching TransferData(). If it means anything to this thread, I am using SMO for Backup and Restore successfully...so some SMO functions are working on my machine. Is there anything else I can try Thanks, AL Tag: IDENTITY_INSERT Issue
  - 8
    - Report Builder Is Report Builder capable of building any complex reports that Visual Studio Report Designer can build What are the limitations of using Report Builder with SQL Server Databases Tag: IDENTITY_INSERT Issue
  - 9
    - 64-Bit SQL Server 2005 (Enterprise), SQL Agent Notifications and Database Mail We have database mail configured on our 64-Bit SQL Server Enterprise 2005 (SP1) and are able to send test messages from the context menu that appears when selecting database mail component. However for some reason when a SQL Agent Job fails, no emails are sent to the 'operator' that has been defined for the job. I have confirmed that we have the operators correctly configured. We also have a small SQL Agent Job that just sends a test email via the sp_send_dbmail command and that works properly. However if I deliberately create a malformed parameter in the sp_send_dbmail command to 'break' it...no notification of a job failure message is sent. I have verified that the service_broker component is enabled...and the status of database mail in the MSDB database is 'started.' We have setup several other 32-bit SQL Server 2005 systems using database mail...and they all work flawlessly. After reviewing existing message posts...I am unsure if there is a problem with database mail on a 64-bit SQL Server box. Some say 'yes' other say 'no'. There are several MSDN notes about the problem...however these appeared prior to the release of SP1 of SQL Server 2005. Thank you for your assistance. ...cordell... Tag: IDENTITY_INSERT Issue
  - 10
    - importing from excel into SQL Server 2005 changing values to NULL Philip, Since the user interface of the Import and Export Wizard doesn't let you specify the Extended Properties for the Jet Provider anywhere, you cannot use the Wizard alone when you need to specify IMEX=1. However you can save the package, edit the connection string to add the IMEX setting, and then your import should work as expected. -Doug Tag: IDENTITY_INSERT Issue
  - 11
    - Could not load package because of error 0xC001000A A little hand, If you are at command prompt, to run a package you need to use dtexec /f "d:\backup\teste.dtsx" use f for file system. I received the same message when I was at command prompt using the option dtexec /sql I hope it help. senger Tag: IDENTITY_INSERT Issue
  - 12
    - Disabling extended stored procedures for security Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time Or, is the security team being too cautious and we should just tell them to leave these intact Tag: IDENTITY_INSERT Issue
  - 13
    - The SSIS package does not run using SQL Server Agent job I am afraid you are not following all the recomendatgions in the KB article. One of them is to use is to use dtexec when running tpackage via SS agent. By doing so you will get more detail errors (see bellow teh section I am taking about). Uhe se a CmdExec step type in the agent (rather than SSIS step type). The package logging is not helpfull here because the error is happening even before the package gets executed (failing validation may be). Notice that by changing the step type in the job won't fix the problem; but hopefully will give you a better clue about what is wrong Code Snippet About the exec subsystem command and output information By using the exec subsystem command approach, you add verbose console logging switches to the SSIS command line to call the Dtexec.exe SSIS command-line executable file. Additionally, you use the Advanced job feature of the output file. You can also use the Include Step Output in the history option to redirect the logging information to a file or to the SQL Server Agent Job History. The following is an example of a command line: dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT Tag: IDENTITY_INSERT Issue
  - 14
    - smalldatettime Yes, the format can change. Does this mean there is no way to solve this Tag: IDENTITY_INSERT Issue
  - 15
    - missing borders when SSRS report is rendered in Sharepoint Hi , I am trying to host an SSRS report as a web part in Microsoft Sharepoint 2007. All the elements and data come out well except that a few borders are missing in the rendered report. What could be the possible cause and solution for this. Please suggest. Tag: IDENTITY_INSERT Issue
  - 16
    - Reporting services permission denied on microsoft access linked I have a Microsoft access 2003 database with tables linked to a number of "SharePoint lists" I do this by going to File -> Get External Data -> Link Tables. A new window pop-ups in which I select Files of Type to be Windows SharePoint Services.. Then I select the correct site.. Then select the correct Lists.. Then access correctly links the tables to the SharePoint lists. When I Open reporting services and connect to this access database. "Using the Test Connection" (Jet 4.0) its successful" When i when create a new report and select the new datasource i have just created and in reporting services I write a select all query on one of the tables in access I get this error: An Error occurred while executing query. You do not have the necessary permissions to use the ��object. Have your system administrator or the person who created this object establish the appropriate permissions for you. Catastrophic Failure. I have tried this on the super admin machine and still get the same error Any suggestions or help Tag: IDENTITY_INSERT Issue
- Next
  - 1
    - SQLIS Checksum Install Problems For detecting delta records, I'm a big fan of SQLIS' checksum transform. I'm having difficulty in it's install on my current machine, however. After the installation and the new transform is added to my DataFlow toolbox... I can't open the UI for the transform to define the checksum. Instead, I get the following error: =================================== Could not load file or assembly 'Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft Visual Studio) ------------------------------ Program Location: at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransformUI.Edit(IWin32Window parentWindow, Variables variables, Connections connections) at Microsoft.DataTransformationServices.Design.DtsComponentDesigner.StartComponentUI(Boolean startGenericUI) Anyone have any suggestions Thanks in advance. Tag: IDENTITY_INSERT Issue
  - 2
    - Count 4 tables I am trying to get a count of rows in 4 tables at once. Output: Table1 Table2 Table3 Table4 12345 143 892626 28428 Or maybe even Table Count Table1 12345 Table2 143 Table3 892626 Table4 28428 Thanks, Cory Tag: IDENTITY_INSERT Issue
  - 3
    - Duplicates Ah, ok, the destination table primary key is Day (int). Tag: IDENTITY_INSERT Issue
  - 4
    - Adding Columns and rows in pivot table to Excel 2007 Hi, I am developing Excel pivot table reports pointing to Analysis services Cube (SSAS 2005). I tried to add Columns and rows inside the pivot table but it doesnt allow me to do it. Is there a way to achieve this kind of scenario in Excel 2007 apart from converting pivot table into a normal table and manipulating it. I once read that pivot table is a single object and we cannot manipulate(i.e. add new rows and columns) inside the pivot table. Thanks Kiran Tag: IDENTITY_INSERT Issue
  - 5
    - Error: Incorrect syntax near the keyword 'DECLARE' Can someone tell me why I am getting this error: Msg 156, Level 15, State 1, Procedure fn_FunctionName, Line 11 Incorrect syntax near the keyword 'DECLARE' CREATE FUNCTION fn_FunctionName(@ElementID int) RETURNS TABLE AS RETURN --create a table (o) to hold the outcomes returned by fn_GetDescendantsByClass DECLARE @OUTCOMES TABLE (ID int, PARENT_ID int, CLASS_ID int, TITLE nvarchar(255)) INSERT @OUTCOMES SELECT * FROM fn_GetDescendantsByClass(@ElementID, 302) SELECT DISTINCT sl.JOB_ID, o.ID FROM @OUTCOMES o INNER JOIN (SELECT START_SCENE_ID, DEST_SCENE_ID AS JOB_ID FROM SCENE_LINK WHERE PROPERTY_ID IN (97, 151)) AS sl ON o.ID = sl.START_SCENE_ID GO Thanks very much Tag: IDENTITY_INSERT Issue
  - 6
    - SQL Server Reporting Services and Http Compression We have a wierd situation in our project. We have the following setup - 1. a Production web server cluster with 4 web servers 2. a Production sql reporting server that has reports called from web apps hosted on the production cluster We had an app on Production that was serving Pdf reports (not saved on the server) using he report server. Last week we setup Http Compression on the web servers. From around the same time, the Pdf reports no longer come up. Opening the report just produces a blank IE window. When I hit the same reporting server for the same report using a test web server (thus bypassing the production cluster and the Http Compression), the report comes up fine. Is the Http Compression interfering with the Pdf data served The report is called using an aspx page and .aspx files are included for compression. Appreciate all your help. Tag: IDENTITY_INSERT Issue
  - 7
    - SQL Express compatibility mode I currently use MSDE and now I need to upgrade because we moved to 2005. The problem for me is teh compatibility mode. I need to change it to 80 (2000). I also need to do it via command line because we have a base command line install for users. We use it to attach databases and view information. Is there a way to install SQL Express with a default mode of 80 (SQL 2000). THanks Tag: IDENTITY_INSERT Issue
  - 8
    - SSRS Report Parameters Hi, I have created two parameters as StartDateTime and EndtDateTime and i am displaying in the Page Header section in the Report as " Report Duration from " & Parameters!DimActivityStartDatetime.Value & " to " & Parameters!DimActivityEndDatetime.Value" It is displaying at the runtime in the report like Report Duration from [DimAct].[StartDateTime].&[2007-03-10TO7:30:00] to [DimAct].[EndDateTime].&[2007-03-10TO7:30:00]. But i need to display only the dates. is is displaying with the dimension name also. Is there any solution to solve this, can anybody help me on this Thanks Dinesh Tag: IDENTITY_INSERT Issue
  - 9
    - MSSQL2005 Analysis Service Distinct Count OK found why I got the diff : Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure Tag: IDENTITY_INSERT Issue
  - 10
    - Maximum Number of Processes Reached I am trying to setup transactional replication between Server A and Server B. There are 265 databases on each server. I am running SQL Server 2005 on Windows Server 2003. The problem comes in at the 201st database. The message in the SQL Server Agent Error Log is : Warning,[398] The job (WSSWPG09-EmpirePaint-WSSWPG06-104) has been queued because the maximum number of working threads (400) are already running. This job will be executed as soon as one of the working thread finishes execution. SQL Server's max worker threads is set to 0. The Distribution, LogReader and T-SQL subsystems have been increased to 200 max_worker_threads. Is there some other setting (maybe a Windows Registry setting) that can be configured to fix this Or have I just hit a physical maximum of the processor Any help is much appreciated. Tag: IDENTITY_INSERT Issue
  - 11
    - Bar Charts - possible to set max widths of columns ? Hi, Is it possible to fix the max width of the columns Sometime the report only generates 1 or 2 columns and it looks a bit odd as they're stretched to the width of the page. Also, when we have a large number of culumns, say 20 odd, the legend text get's real squashed up and un readable, are there any tips/tricks so i can have more flexibility over what's displayed thanks Steve Tag: IDENTITY_INSERT Issue
  - 12
    - Report Builder Query Hi Experts, I have a report model on top of a cube. I have a calculated metrics called Policy Count and different amounts in cube. When i try to create report in report builder with Policy count, amounts and different dimensional attributes it doesnt work. The problem is report builder allows me to select either Policy count in the report or the amount field. Have anyone faced this problem. The same report works in cube browser. Regards' Neeraj Tag: IDENTITY_INSERT Issue
  - 13
    - Trigger / Constraint / ? Hello All I am working on a calendar system where, among other things, we are track resources by event. I.e. here is a conference room and it can only be used by one event at a time. This system also is used by various departments as a calendar for their employees, i.e. sick / vacation / out of office / etc which can be used by more than one person at a time... (it was inherited) My question is if I have a table for the resources (conference room / IT Department Sick Leave / etc) and I have a table for the events (w/ FK to the resources) what will be the best way to make sure that those resources that can't have "double booking" would not allow an insert to the database (while not inhibiting those that can) SQL 2000 table: event eventid int identity pk resourceid int fk comments nvarchar 250 startdate datetime enddate datetime ... table: resource resourceid int identity pk RestrictDoubleBooking bit EmailOnNewEvent bit ... Also, what would be the best practice for emailing an admin on new event insertion for resources that have the flag set Would these best be performed in the application, or on the db side Tag: IDENTITY_INSERT Issue
  - 14
    - tsql for database , size , free space or used space in database hi, i want to know how to find out database size Tag: IDENTITY_INSERT Issue
  - 15
    - SQL DB Cleanup Hi! Well you have to find an definition of "needed" by yourself. But when you have it, you can write an stored proc that deletes contents based on that list. But why do you want to delete data, maybe you need them later... How do you access data Maybe an logon trigger can help for some parts of your problem. Do you use stored proc If yes, you can look at dependencies... Tag: IDENTITY_INSERT Issue
  - 16
    - Retriving Deleted record from database Hi friends I have a bit problem here Just I want to get back all deleted record of database How do I perform this task If It is possible then plz help me out Thanks in Advance Khan Tag: IDENTITY_INSERT Issue

Jim Work

INSERT INTO dbo.my_table (table_key)
VALUES (007)

-- Msg 544, Level 16, State 1, Line 1
-- Cannot insert explicit value for identity column in table 'my_table' when IDENTITY_INSERT is set to OFF.

SET IDENTITY_INSERT dbo.my_table ON
INSERT INTO dbo.my_table(table_key)
VALUES (007)
SET IDENTITY_INSERT dbo.my_table OFF

-- Msg 1088, Level 16, State 11, Line 2
-- Cannot find the object "dbo.my_table" because it does not exist or you do not have permissions.

I know for certain that I have read/write permissions on this server. Is this somehow restricted to only the dba or something

I appreciate all of this community's help with my basically non-stop flow of questions. You're a huge chunk of help, and it's deeply appreciated!!

Jim Work

Re: IDENTITY_INSERT Issue

Phil Brammer

You should search Books On-Line for that command you are trying to run.

Default permissions for that command (SET IDENTITY_INSERT) are set to the sysadmin role.

Below is for SQL 2000, but I imagine the same is said for 2005. (BOL doesn't seem to be working too well for me at the moment.)

http://msdn2.microsoft.com/en-us/library/aa259221(SQL.80).aspx

Re: IDENTITY_INSERT Issue

Phil Brammer

Oh, and for future reference, you should post this in the Transact-SQL forum.

Re: IDENTITY_INSERT Issue

Jim Work

Thanks!

Oh! Sorry about posting in the wrong forum!!

Cheers!

Jim Work