January 16, 2015 at 8:34 am
Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):
1. script the source table's schema to a create
2. the new script:
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
127.0.0.1 [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
insert into EventlogHistory
It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?
Thank you.
January 16, 2015 at 8:54 am
halifaxdal (1/16/2015)
Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):1. script the source table's schema to a create
2. the new script:
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
127.0.0.1 [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
insert into EventlogHistory
It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?
Thank you.
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
if you SELECT *
INTO #temp
From YourSourceTable, and compare the column sizes, can you see what is the issue?
select
colz.name As ColumnName,
TYPE_NAME(colz.system_type_id) As DataType,
CASE
WHEN TYPE_NAME(colz.system_type_id) IN('nchar','nvarchar')
THEN colz.max_length / 2
WHEN TYPE_NAME(colz.system_type_id) IN('char','varchar')
THEN colz.max_length
ELSE NULL
END,
colz.column_id
from tempdb.sys.columns colz
where object_id=object_id('tempdb.dbo.#temp')
Lowell
January 16, 2015 at 9:06 am
halifaxdal (1/16/2015)
Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):1. script the source table's schema to a create
2. the new script:
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
127.0.0.1 [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
[font="Arial Black"]insert into EventlogHistory [/font]
It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?
Thank you.
There seems to be a wee bit o' code missing in the area that I've embolded above.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 9:18 am
Jeff Moden (1/16/2015)
halifaxdal (1/16/2015)
Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):1. script the source table's schema to a create
2. the new script:
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
127.0.0.1 [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
[font="Arial Black"]insert into EventlogHistory [/font]
It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?
Thank you.
There seems to be a wee bit o' code missing in the area that I've embolded above.
You are right Jeff, [font="Arial Black"]select * from Eventlog[/font] is the missing part. Thanks for pointing that.
January 16, 2015 at 9:20 am
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
January 16, 2015 at 9:23 am
maybe this is not a good way to back up a specific table.
can anyone share their experience on back up a specific table in a weekly basis (scheduled in a job)? Thanks.
What I originally thought is to add a datetime (the timestamp of backup) in the backup table.
The table I need to back up has 30+ fields of different types
January 16, 2015 at 9:39 am
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
could the order of columns in the target table be differnet than the desitnaation table?
ie if you expicitly name the insert column names as wella s the names of the SELECT columns?, are they in the same order as SELECT *?
INSERT INTO EventlogHistory
([LogID],[ProjectID],[Description],[EventType],127.0.0.1,[UserLogon],[CreatedOn])
SELECT [LogID],[ProjectID],[Description],[EventType],127.0.0.1,[UserLogon],[CreatedOn]
FROM EventLog
Lowell
January 16, 2015 at 10:04 am
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
Its fine the way you have it. If your history table has exactly the same schema and datatypes as the source table, then there's something very strange going on that's going to require a deeper dive on your part.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 11:02 am
Lowell (1/16/2015)
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
could the order of columns in the target table be differnet than the desitnaation table?
ie if you expicitly name the insert column names as wella s the names of the SELECT columns?, are they in the same order as SELECT *?
INSERT INTO EventlogHistory
([LogID],[ProjectID],[Description],[EventType],127.0.0.1,[UserLogon],[CreatedOn])
SELECT [LogID],[ProjectID],[Description],[EventType],127.0.0.1,[UserLogon],[CreatedOn]
FROM EventLog
I might have to do that explicitly although I am unwilling to because of the number of fields in the real table I am working on.
January 16, 2015 at 11:03 am
Jeff Moden (1/16/2015)
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
Its fine the way you have it. If your history table has exactly the same schema and datatypes as the source table, then there's something very strange going on that's going to require a deeper dive on your part.
You can see my script is to create an exactly the same schema with only one extra field added which is ArchivedOn (to record the back up time)
January 16, 2015 at 11:42 am
halifaxdal (1/16/2015)
Jeff Moden (1/16/2015)
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
Its fine the way you have it. If your history table has exactly the same schema and datatypes as the source table, then there's something very strange going on that's going to require a deeper dive on your part.
You can see my script is to create an exactly the same schema with only one extra field added which is ArchivedOn (to record the back up time)
Dunno for sure, but that could be the reason why you're getting the error. You might need to list the columns both in the INSERT and the SELECT feeding it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 11:58 am
Jeff Moden (1/16/2015)
halifaxdal (1/16/2015)
Jeff Moden (1/16/2015)
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
Its fine the way you have it. If your history table has exactly the same schema and datatypes as the source table, then there's something very strange going on that's going to require a deeper dive on your part.
You can see my script is to create an exactly the same schema with only one extra field added which is ArchivedOn (to record the back up time)
Dunno for sure, but that could be the reason why you're getting the error. You might need to list the columns both in the INSERT and the SELECT feeding it.
I prefer doing it that way all the time. The exception to that is the SELECT INTO syntax, which I use for one-offs.
January 16, 2015 at 3:11 pm
halifaxdal (1/16/2015)
Lowell (1/16/2015)
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on your table, only columns [EventType] [varchar](10) or [UserLogon] [varchar](30) seem to stand out to me to be candidates for strings bigger than the destination.
No, the two fields' max len returns as 6 and 18.
could the order of columns in the target table be differnet than the desitnaation table?
ie if you expicitly name the insert column names as wella s the names of the SELECT columns?, are they in the same order as SELECT *?
INSERT INTO EventlogHistory
([LogID],[ProjectID],[Description],[EventType],127.0.0.1,[UserLogon],[CreatedOn])
SELECT [LogID],[ProjectID],[Description],[EventType],127.0.0.1,[UserLogon],[CreatedOn]
FROM EventLog
I might have to do that explicitly although I am unwilling to because of the number of fields in the real table I am working on.
Or like this:
insert into EventlogHistory
select *, getdate()
from Eventlog
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 16, 2015 at 5:01 pm
To be sure, the point I'm trying to make is that this error happens for a reason. You say there are a lot of columns in this table... are you absolutely 100% sure that the columns in the history table are in the exact same order as the original table and that they both start with the same thing in the first column?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 5:50 pm
halifaxdal (1/16/2015)
Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
127.0.0.1 [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
Problem #1 : The identity column means you can't be inserting without specifying the columns.
Problem #2 : That table is not an exact replica of the source table, it has at least one extra column, so you can't be inserting into it from a SELECT *.
Problem #3 : Don't ever use insert without a column list, and don't use select *
Problem #4 : If you are using an IDENTITY column it's because you want to have a new set of numbers in this archive table for LogID, or else you would just have it as an int, wouldn't you?
It seems like you might be trying to obfuscate the real problem (as we have all had to do from time to time) and in the process you may be hiding the real problem, because there is no way that an insert into that table without a column list from a select star would even get compiled, let alone run and produce that error.
Of course, it is late and I could be wrong 😎
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply