November 29, 2012 at 6:38 am
How do I fix this issue?: I'm trying to run the SQL:
SET IDENTITY_INSERT dbo.tblTransactions ON
I keep getting a 1088 error "Cannot find the object because it does not exist or you do not have permissions."
November 29, 2012 at 7:07 am
make sure the table actually exists. the user you're running it under needs the alter permission on the table. your sys admin can grant that right...
GRANT ALTER ON [dbo].[tblTransactions] TO [UserName]
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 29, 2012 at 7:11 am
table exists and I routinely run commands like this which would indicate I have permissions (?):
ALTER TABLE dbo.tblTransactions
ADD TransferFrom VARCHAR(100) Null;
GO
November 29, 2012 at 7:30 am
Are you sure you're running under the right database context?
try it with the [DBName].[dbo].[tblTransactions]
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 29, 2012 at 8:11 am
SET IDENTITY_INSERT [TrackIT].[dbo].[tblTransactions] ON worked as far as:
"Command(s) completed successfully."
But When I try to run my INSERT I get:
"Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tblTransactions' when IDENTITY_INSERT is set to OFF."
USE [TrackIT]
GO
INSERT INTO [dbo].[tblTransactions]
(tblTransactions.Buyer_Initial,
tblTransactions.HeatTicketNumber,
tblTransactions.PO_Number,
tblTransactions.Transaction_Date,
tblTransactions.EndUserFirstName,
tblTransactions.EndUserLastName,
tblTransactions.EmployeeStatus,
tblTransactions.CostCenter,
tblTransactions.TermDate,
tblTransactions.License_Available,
tblTransactions.TransferFrom,
tblTransactions.TransferTo,
tblTransactions.EndUserLocation,
tblTransactions.EquipType,
tblTransactions.Quantity,
tblTransactions.Software_Description,
tblTransactions.Notes,
tblTransactions.Unit_Price,
tblTransactions.AllocationDate,
tblTransactions.Last_Renewal_Date,
tblTransactions.NextRenewalDate,
tblTransactions.LastRenewalPO,
tblTransactions.Transaction_Number)
Select
Master.Buyer_Initial,
Master.HeatTicketNumber,
Master.PO_Number,
Master.Transaction_Date,
Master.EndUserFirstName,
Master.EndUserLastName,
Master.EmployeeStatus,
Master.CostCenter,
Master.TermDate,
Master.License_Available,
Master.TransferFrom,
Master.TransferTo,
Master.EndUserLocation,
Master.EquipType,
Master.Quantity,
Master.Software_Description,
Master.Notes,
Master.Unit_Price,
Master.AllocationDate,
Master.Last_Renewal_Date,
Master.NextRenewalDate,
Master.LastRenewalPO,
Master.Transaction_Number
from [Master]
GO
November 29, 2012 at 9:00 am
make sure you're running in the same process (SPID). Put the set Identity_Insert statement somewhere before your insert statement...
set identity_insert tblTransactions ON;
GO
Insert into tblTransactions (...)
select ... from xyz
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 29, 2012 at 10:16 am
tried these variations to make sure syntax wasn't biting me:
USE [TrackIT]
GO
set identity_insert tblTransactions ON
GO
INSERT INTO [dbo].[tblTransactions]
(tblTransactions.Buyer_Initial,
USE [TrackIT]
GO
set identity_insert tblTransactions ON;
GO
INSERT INTO [dbo].[tblTransactions]
(tblTransactions.Buyer_Initial,
USE [TrackIT]
Set identity_insert tblTransactions ON;
GO
INSERT INTO [dbo].[tblTransactions]
(tblTransactions.Buyer_Initial,
USE [TrackIT]
GO
set identity_insert [dbo].[tblTransactions] ON
GO
INSERT INTO [dbo].[tblTransactions]
Getting this now with each variation:
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
November 29, 2012 at 10:36 am
First: Check Data Types
Second: you must be sure the destination table has an identity column :w00t: and be sure you include the identity column in the list of columns you want to insert :w00t:.
Hope it help you.
November 29, 2012 at 10:45 am
One or more of your data that you're trying to enter doesn't fit into your target table... I would setup an SSIS job for this and have it spit out the invalid data to a separate table to look at...
otherwise...
I get the max length of each field in your select statement and then compare the length to the field length in the target table...
select max(len([field1])) Field1
,max(len([Field2])) Field2
.
.
from [master]
it's fairly quick if you use Excel to put the select statement fields together with concatenate...
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 29, 2012 at 11:55 am
Here is my DDL for the two tables so looks like I need to do some cleanup:
CREATE TABLE [dbo].[Master](
[Buyer_Initial] [varchar](50) NULL,
[HeatTicketNumber] [varchar](50) NULL,
[PO_Number] [varchar](50) NULL,
[Transaction_Date] [date] NULL,
[EndUserLastName] [varchar](100) NULL,
[EndUserFirstName] [varchar](100) NULL,
[EmployeeStatus] [varchar](50) NULL,
[CostCenter] [varchar](20) NULL,
[TermDate] [date] NULL,
[License_Available] [bit] NULL,
[TransferFrom] [nvarchar](255) NULL,
[TransferTo] [varchar](100) NULL,
[EndUserLocation] [varchar](100) NULL,
[EquipType] [nvarchar](255) NULL,
[Quantity] [nvarchar](255) NULL,
[Software_Description] [nvarchar](255) NULL,
[Notes] [varchar](255) NULL,
[Unit_Price] [money] NULL,
[AllocationDate] [date] NULL,
[Last_Renewal_Date] [date] NULL,
[NextRenewalDate] [date] NULL,
[LastRenewalPO] [nvarchar](255) NULL,
[Transaction_Number] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblTransactions](
[Buyer_Initial] [varchar](50) NULL,
[HeatTicketNumber] [varchar](50) NULL,
[PO_Number] [varchar](50) NULL,
[Transaction_Date] [date] NULL,
[EndUserLastName] [varchar](100) NULL,
[EndUserFirstName] [varchar](100) NULL,
[EmployeeStatus] [varchar](50) NULL,
[CostCenter] [varchar](10) NULL,
[TermDate] [date] NULL,
[License_Available] [varchar](50) NULL,
[TransferFrom] [varchar](100) NULL,
[TransferTo] [varchar](100) NULL,
[EndUserLocation] [varchar](100) NULL,
[EquipType] [varchar](50) NULL,
[Quantity] [int] NULL,
[Software_Description] [varchar](100) NULL,
[Notes] [varchar](255) NULL,
[Unit_Price] [money] NULL,
[AllocationDate] [date] NULL,
[Last_Renewal_Date] [date] NULL,
[NextRenewalDate] [date] NULL,
[LastRenewalPO] [varchar](50) NULL,
[Transaction_Number] [int] IDENTITY(18000,1) NOT NULL
CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED
(
[Transaction_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply