February 11, 2011 at 9:30 am
Insert fails due to datatype errors when in a script calling mssql_query, but
the cut-and-pasted query that's put in Microsoft SQL Server Management Studio
Express succeeds with no problem. The datatypes are the same, say, putting a
"22" in an integer and the like. This makes me think the table is corrupted, and
that SSMS is more tolerant of whatever is wrong with the database.
Is there a way to "rebuild" the table so that metadata is "refreshed" or otherwise
made consistent? Or possibly a way I can simply go in and set the types of the
fields again so that "mssql_query" doesn't see a mismatch and will allow me to
insert? Or FORCE an insert by performing some type of casting/datatype-change
of the source value?
Mark
February 11, 2011 at 10:31 am
In SSMS, does the query insert into the same target?
I've seen SSIS have problems like what you're describing, because of different datatypes in the target than in the source. Like Excel, which wants all the string type columns to be unicode instead of ASCII.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 10:37 am
The GUI is probably doing the data conversion for you. I would not assume there is any corruption or that there is anything wrong with the database.
Can you post your insert statement and errors?
>>Or FORCE an insert by performing some type of casting/datatype-change
of the source value?
Yes, go this route although I wouldn't call it forcing the insert, just fixing the insert.
February 11, 2011 at 11:23 am
Yes -- the same DB and Table. I have the PHP running in one window and I simply print
out the query and paste it into SSMS, where it then succeeds without a hitch.
The ORIGINAL source of the data is, in fact, an Excel file in CSV format. I have problems
with all sorts of field types, tho' -- text-to-text, date-to-date, integer-to-integer.
I was THINKING that I have corruption in the metadata and would like to know how to check
that and repair it.
in ANY event, I'm stuck with this data, and I really need to be able to insert new stuff into
it, so ANY help would be appreciated. ( Perhaps there's a way to "force" the insert through some
conversion or other operations??? )
Mark
************************************
In SSMS, does the query insert into the same target?
I've seen SSIS have problems like what you're describing, because of different datatypes in the target than in the source. Like Excel, which wants all the string type columns to be unicode instead of ASCII.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 11:56 am
Here's a LOT of data -- hopefully this will provide everything needed.
As you can see below, a simple string being put into a simple text
file is failing:
This is the test datafile. The first line is skipped during input,
as is verified by the successful insertion of the data into various
other tables in ASA --
NameX,PurposeX,1,AccountX,PriceX,DateX
Consumery Loansy, Mortgagey Cedary Gladey Drivey,22,2021913,$678.90 ,1/3/1909
Suburbany Banky, Uncategorizedy *28723,5,2021913,$765.43 , 04/30/1909
BPy Chasey, Credity Cardy 0695 24% *90695,0,2021913,$1.00 , 05/03/1909
The types of these fields (Table.Column) are:
company.Name: (nvarchar(50), null allowed)
company.MonthsConsideredNew: (tinyint, not null)
Contact.LongText05: (nvarchar(50), text, null allowed)
Here's a SUCCESSFUL run using Account, also one of the largest tables:
Data Operation:
INSERT INTO Account (Text206, CompanyID) VALUES ('Consumery Loansy', '22')
Data Operation:
INSERT INTO Account (Text206, CompanyID) VALUES ('Suburbany Banky', '5')
Data Operation:
INSERT INTO Account (Text206, CompanyID) VALUES ('BPy Chasey', '0')
Data Operation:
INSERT INTO Account (Text206, CompanyID) VALUES ('Consumery Loansy', '22')
Data Operation:
INSERT INTO Account (Text206, CompanyID) VALUES ('Suburbany Banky', '5')
Data Operation:
INSERT INTO Account (Text206, CompanyID) VALUES ('BPy Chasey', '0')
You have just uploaded fully-screened, processed and verified records.
Here are more FAILURES with company and Contact:
Data Operation (These are the queries submitted to 'mssql_query'):
INSERT INTO company (Name, MonthsConsideredNew) VALUES ('Consumery Loansy', '22')
Data Operation:
INSERT INTO company (Name, MonthsConsideredNew) VALUES ('Suburbany Banky', '5')
Data Operation:
INSERT INTO company (Name, MonthsConsideredNew) VALUES ('BPy Chasey', '0')
Data Operation:
INSERT INTO Contact (LongText05) VALUES ('Consumery Loansy')
Fatal error: Uncaught exception 'import_exception' with message 'Datafield Mismatch' in
C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\Xsettings.php: 53
Stack trace:
#0 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\class\msdb.php(25):
throw_exception()
#1 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\class\msdb.php(49):
msdb->call('Datafield Misma...')
#2 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\class\table_loader.php(45):
msdb->insert('INSERT INTO Con...')
#3 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\Xpage_data_import.php(94):
table_loader->save_datasets('INSERT INTO Con...')#4 {main} thrown in C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\Xsettings.php on line 53Here is Second Run with the order of the Tables Reversed
("Contact" first, then company"):
Data Operation:
INSERT INTO Contact (LongText05, AccountID) VALUES ('Mortgagey Cedary Gladey Drivey', '22')Fatal error: Uncaught exception 'import_exception' with message 'Datafield Mismatch' in C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\Xsettings.php:53 Stack trace:
#0 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\class\msdb.php(25):
throw_exception()#1 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\class\msdb.php(49): msdb->call('Datafield Misma...')#2 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\class\table_loader.php(45):
msdb->insert('INSERT INTO Con...')
#3 C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\Xpage_data_import.php(93):
table_loader->save_datasets('INSERT INTO Con...')
#4 {main} thrown in C:\mpower\slipstream\trunk\www\administrator\uploader-inserter\Xsettings.php on line 53
February 11, 2011 at 1:13 pm
Sorry I misunderstood. I thought your successful INSERT was done entering data directly in the tables in SSMS.
Can you run profiler during during the success and failure and see if there are differences?
February 11, 2011 at 1:23 pm
No, you're right. BOTH of the failures from that listing SUCCEEDED
in SMSS, but all I saw was the same data in that CSV listing correctly
inserted in the table, no other information. I wanted to show that the
code itself DOES succeed for the other table, Account, also a large table.
How would I use Profiler to provide the information you're looking for?
Mark
February 11, 2011 at 2:45 pm
I'm still a little clear about what is happening. It sounds like you have a data file which is being parsed by a pPHPp program. The PHP program generates INSERT statements which are then passed to SQL server.
Are the falures always related to the Contact table?
Is this the failing INSERT statement?
INSERT INTO Contact (LongText05) VALUES ('Consumery Loansy')
In SSMS right-click on the table where the inserts are failing and choose script table as CREATE TO new queury window.
Let us see this output which will be the table structure.
February 11, 2011 at 2:46 pm
I meant...
I'm still a little unclear
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply