SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different RESULT mssql_query vs. SSMS?????


Different RESULT mssql_query vs. SSMS?????

Author
Message
gmark 10972
gmark 10972
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59885 Visits: 9730
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
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3316 Visits: 4799
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.
gmark 10972
gmark 10972
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
gmark 10972
gmark 10972
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3316 Visits: 4799
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?
gmark 10972
gmark 10972
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3316 Visits: 4799
http://www.eggheadcafe.com/articles/sql_server_profiler.asp

http://www.sqlservernation.com/home/introduction-to-sql-server-profiler.html
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3316 Visits: 4799
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.
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3316 Visits: 4799
I meant...

I'm still a little unclear
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search