Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Different RESULT mssql_query vs. SSMS????? Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 5:54 PM
Points: 5, Visits: 17
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
Post #1062671
Posted Friday, February 11, 2011 10:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1062708
Posted Friday, February 11, 2011 10:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:15 PM
Points: 653, Visits: 3,831
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.
Post #1062715
Posted Friday, February 11, 2011 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 5:54 PM
Points: 5, Visits: 17
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
Post #1062752
Posted Friday, February 11, 2011 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 5:54 PM
Points: 5, Visits: 17
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
Post #1062772
Posted Friday, February 11, 2011 1:13 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:15 PM
Points: 653, Visits: 3,831
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?
Post #1062801
Posted Friday, February 11, 2011 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 5:54 PM
Points: 5, Visits: 17
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
Post #1062805
Posted Friday, February 11, 2011 2:30 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:15 PM
Points: 653, Visits: 3,831
http://www.eggheadcafe.com/articles/sql_server_profiler.asp

http://www.sqlservernation.com/home/introduction-to-sql-server-profiler.html
Post #1062838
Posted Friday, February 11, 2011 2:45 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:15 PM
Points: 653, Visits: 3,831
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.
Post #1062845
Posted Friday, February 11, 2011 2:46 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:15 PM
Points: 653, Visits: 3,831
I meant...

I'm still a little unclear
Post #1062846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse