Different RESULT mssql_query vs. SSMS?????

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Different RESULT mssql_query vs. SSMS?????

Different RESULT mssql_query vs. SSMS?????

Rate Topic

Display Mode

Topic Options

Author

Message

gmark 10972

Posted Friday, February 11, 2011 9:30 AM

Forum 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

GSquared

Posted Friday, February 11, 2011 10:31 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Chrissy321

Posted Friday, February 11, 2011 10:37 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:01 PM
Points: 550, Visits: 2,978

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

gmark 10972

Posted Friday, February 11, 2011 11:23 AM

Forum 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

gmark 10972

Posted Friday, February 11, 2011 11:56 AM

Forum 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

Chrissy321

Posted Friday, February 11, 2011 1:13 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:01 PM
Points: 550, Visits: 2,978

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

gmark 10972

Posted Friday, February 11, 2011 1:23 PM

Forum 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

Chrissy321

Posted Friday, February 11, 2011 2:30 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:01 PM
Points: 550, Visits: 2,978

http://www.eggheadcafe.com/articles/sql_server_profiler.asp

http://www.sqlservernation.com/home/introduction-to-sql-server-profiler.html

Post #1062838

Chrissy321

Posted Friday, February 11, 2011 2:45 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:01 PM
Points: 550, Visits: 2,978

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

Chrissy321

Posted Friday, February 11, 2011 2:46 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:01 PM
Points: 550, Visits: 2,978

I meant...

I'm still a little unclear

Post #1062846

« Prev Topic | Next Topic »

Permissions