Invalid SQL when @centralized_conflicts='false'

The further I delive into SQL Server replication, the more apparent it
becomes what a complete and utter lash-up the whole "technology" is.
Has anyone ever encountered this particular problem? I have transactional
push replication in use, with a publication with
@.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
both publisher and subscriber).
However, when a conflict occurs, SQL Agent falls over with an error
"Incorrect syntax near the keyword 'null'". On further investigation, the
SQL generated by Agent for inserting the conflict into the subscriber's
conflict table is missing a comma in the VALUES list!!
It seems incredible to me how something as important as data replication can
have been implemented using nothing more than a whole load of (pretty poorly
written) triggers and stored procedures that may or may not be restored when
you backup and restore a database, that cause replication to fail when you
restore a published database on the subscriber unless you first run the
pathetic sp_scriptpublicationcustomprocs to generate the world's longest
nvarchars containing dynamically generated SQL, and which cause utterly
meaningless errors about invalid SQL to be logged to the event log.
The rant is over now, but the serious question remains - is anyone out there
actually using SQL Server Replication within a serious Enterprise class
application or should I just accept that it really is the half-hearted,
conceived-at-the-last-minute, hack that it appears to be?It works well for my clients. I have worked on replication projects with
several hundred subscribers.On one implementation we had a publisher
replicating 2500 transactions per sec! Barnes and Noble runs an
implementation with over 600 subscribers. Ticket Master uses transactional
replication and is the largest implementation of transactional replication
or was last time I spoke with some of the MS folks. In one of the channel 9
videos Bren Newman talks about repro'ing a topology with over 200. Its done,
its stable, it offers high performance.
I am curious though as to what method of transactional replication you are
using? There is a problem with the GUI in transactional replication methods.
IIRC it seems to indicate that you can log conflicts centrally or locally;
whereas you can only log them centrally. Perhaps this is what you are
encountering. What sp and version of SQL (7 or 2000) are you running?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
> The further I delive into SQL Server replication, the more apparent it
> becomes what a complete and utter lash-up the whole "technology" is.
> Has anyone ever encountered this particular problem? I have transactional
> push replication in use, with a publication with
> @.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
> both publisher and subscriber).
> However, when a conflict occurs, SQL Agent falls over with an error
> "Incorrect syntax near the keyword 'null'". On further investigation, the
> SQL generated by Agent for inserting the conflict into the subscriber's
> conflict table is missing a comma in the VALUES list!!
> It seems incredible to me how something as important as data replication
> can have been implemented using nothing more than a whole load of (pretty
> poorly written) triggers and stored procedures that may or may not be
> restored when you backup and restore a database, that cause replication to
> fail when you restore a published database on the subscriber unless you
> first run the pathetic sp_scriptpublicationcustomprocs to generate the
> world's longest nvarchars containing dynamically generated SQL, and which
> cause utterly meaningless errors about invalid SQL to be logged to the
> event log.
> The rant is over now, but the serious question remains - is anyone out
> there actually using SQL Server Replication within a serious Enterprise
> class application or should I just accept that it really is the
> half-hearted, conceived-at-the-last-minute, hack that it appears to be?
>|||Thanks for the info, Hilary. It's interesting to see how and where it is
being used for real.
The number of transactions and subscribers doesn't surprise me - I've
thrashed it myself with good throughput results. Neither am I surprised, I
guess, that some companies have achieved results with it... but it doesn't
change my opinion that it's quite clearly an afterthought that is
implemented by clear "hacks", and which make it very messy to deal with. I
guess that with enough time, patience and frustration, you could get
TicketMaster running on a million networked Commodore 64s with BASIC as the
programming language. But that wouldn't make it an elegant, sensible or
extensible solution.
I'm using transactional replication with a single queued updating
subscriber. sp_addpublication allows you to specify @.centralized_conflicts
= false, which is supposed to ensure conflicts are logged at both publisher
and subscriber (but which causes the error):
http://msdn.microsoft.com/library/d... />
l_4s32.asp .
I'm using SQL Server 2000 SP4 (Microsoft SQL Server 2000 - 8.00.2039 (Intel
X86)).
Cheers,
Chris.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OOT0%232%23zFHA.3124@.TK2MSFTNGP12.phx.gbl...
> It works well for my clients. I have worked on replication projects with
> several hundred subscribers.On one implementation we had a publisher
> replicating 2500 transactions per sec! Barnes and Noble runs an
> implementation with over 600 subscribers. Ticket Master uses transactional
> replication and is the largest implementation of transactional replication
> or was last time I spoke with some of the MS folks. In one of the channel
> 9 videos Bren Newman talks about repro'ing a topology with over 200. Its
> done, its stable, it offers high performance.
> I am curious though as to what method of transactional replication you are
> using? There is a problem with the GUI in transactional replication
> methods. IIRC it seems to indicate that you can log conflicts centrally or
> locally; whereas you can only log them centrally. Perhaps this is what you
> are encountering. What sp and version of SQL (7 or 2000) are you running?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
> news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
>

Invalid SQL when @centralized_conflicts='false'

The further I delive into SQL Server replication, the more apparent it
becomes what a complete and utter lash-up the whole "technology" is.
Has anyone ever encountered this particular problem? I have transactional
push replication in use, with a publication with
@.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
both publisher and subscriber).
However, when a conflict occurs, SQL Agent falls over with an error
"Incorrect syntax near the keyword 'null'". On further investigation, the
SQL generated by Agent for inserting the conflict into the subscriber's
conflict table is missing a comma in the VALUES list!!
It seems incredible to me how something as important as data replication can
have been implemented using nothing more than a whole load of (pretty poorly
written) triggers and stored procedures that may or may not be restored when
you backup and restore a database, that cause replication to fail when you
restore a published database on the subscriber unless you first run the
pathetic sp_scriptpublicationcustomprocs to generate the world's longest
nvarchars containing dynamically generated SQL, and which cause utterly
meaningless errors about invalid SQL to be logged to the event log.
The rant is over now, but the serious question remains - is anyone out there
actually using SQL Server Replication within a serious Enterprise class
application or should I just accept that it really is the half-hearted,
conceived-at-the-last-minute, hack that it appears to be?
It works well for my clients. I have worked on replication projects with
several hundred subscribers.On one implementation we had a publisher
replicating 2500 transactions per sec! Barnes and Noble runs an
implementation with over 600 subscribers. Ticket Master uses transactional
replication and is the largest implementation of transactional replication
or was last time I spoke with some of the MS folks. In one of the channel 9
videos Bren Newman talks about repro'ing a topology with over 200. Its done,
its stable, it offers high performance.
I am curious though as to what method of transactional replication you are
using? There is a problem with the GUI in transactional replication methods.
IIRC it seems to indicate that you can log conflicts centrally or locally;
whereas you can only log them centrally. Perhaps this is what you are
encountering. What sp and version of SQL (7 or 2000) are you running?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
> The further I delive into SQL Server replication, the more apparent it
> becomes what a complete and utter lash-up the whole "technology" is.
> Has anyone ever encountered this particular problem? I have transactional
> push replication in use, with a publication with
> @.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
> both publisher and subscriber).
> However, when a conflict occurs, SQL Agent falls over with an error
> "Incorrect syntax near the keyword 'null'". On further investigation, the
> SQL generated by Agent for inserting the conflict into the subscriber's
> conflict table is missing a comma in the VALUES list!!
> It seems incredible to me how something as important as data replication
> can have been implemented using nothing more than a whole load of (pretty
> poorly written) triggers and stored procedures that may or may not be
> restored when you backup and restore a database, that cause replication to
> fail when you restore a published database on the subscriber unless you
> first run the pathetic sp_scriptpublicationcustomprocs to generate the
> world's longest nvarchars containing dynamically generated SQL, and which
> cause utterly meaningless errors about invalid SQL to be logged to the
> event log.
> The rant is over now, but the serious question remains - is anyone out
> there actually using SQL Server Replication within a serious Enterprise
> class application or should I just accept that it really is the
> half-hearted, conceived-at-the-last-minute, hack that it appears to be?
>
|||Thanks for the info, Hilary. It's interesting to see how and where it is
being used for real.
The number of transactions and subscribers doesn't surprise me - I've
thrashed it myself with good throughput results. Neither am I surprised, I
guess, that some companies have achieved results with it... but it doesn't
change my opinion that it's quite clearly an afterthought that is
implemented by clear "hacks", and which make it very messy to deal with. I
guess that with enough time, patience and frustration, you could get
TicketMaster running on a million networked Commodore 64s with BASIC as the
programming language. But that wouldn't make it an elegant, sensible or
extensible solution.
I'm using transactional replication with a single queued updating
subscriber. sp_addpublication allows you to specify @.centralized_conflicts
= false, which is supposed to ensure conflicts are logged at both publisher
and subscriber (but which causes the error):
http://msdn.microsoft.com/library/de..._repl_4s32.asp .
I'm using SQL Server 2000 SP4 (Microsoft SQL Server 2000 - 8.00.2039 (Intel
X86)).
Cheers,
Chris.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OOT0%232%23zFHA.3124@.TK2MSFTNGP12.phx.gbl...
> It works well for my clients. I have worked on replication projects with
> several hundred subscribers.On one implementation we had a publisher
> replicating 2500 transactions per sec! Barnes and Noble runs an
> implementation with over 600 subscribers. Ticket Master uses transactional
> replication and is the largest implementation of transactional replication
> or was last time I spoke with some of the MS folks. In one of the channel
> 9 videos Bren Newman talks about repro'ing a topology with over 200. Its
> done, its stable, it offers high performance.
> I am curious though as to what method of transactional replication you are
> using? There is a problem with the GUI in transactional replication
> methods. IIRC it seems to indicate that you can log conflicts centrally or
> locally; whereas you can only log them centrally. Perhaps this is what you
> are encountering. What sp and version of SQL (7 or 2000) are you running?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
> news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
>
sql

invalid row count

Hi,

I am building a shopping cart program, complete with an admin back end.
I am using ASP.NET, VB. NET and a SQL SERVER 2000 database.

The cart is working well... items can be added, updated and removed. However when trying to access the orders stored in the database from the admin section of the application, I am getting an "invalid record count" error.

I have no idea what this means. I've tried looking it up online, but so far no luck.

The orders are being stored in two tables connected via a foreign key.
I have no problem writing to the tables, it's just a problem reading the tables. I have dropped the two tables twice and rebuilt them, but I get the same error each time.

Any thoughts on what is causing this error?

Thanks,

-MichaelSince this is a sql server forum, maybe some sql would help...

Sounds more like an interface problem though.../|||Originally posted by Brett Kaiser
Since this is a sql server forum, maybe some sql would help...

Sounds more like an interface problem though.../

It is definitely starting to look more and more like an interface problem.
I just checked my SQL server logs and their are not database errors associated with this application.

Thanks,

Michaelsql

Invalid object name while reading data out of an SQL Database

Hi all,
I'm a complete newbie on ASP.Net.

I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.

When I try to open the site, I always get the same error:
Invalid object name 'Members'

I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.

Here is the Code of the page so far:

<%@. Page Language="VB" Debug="True" Strict="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQlClient" %>
<script runat="server">
Sub Page_Load (ByVal Sender As Object, _
 ByVal E As EventArgs)
 Dim connStr As String
 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
 connStr += "database=tempdb;"
 connStr += "Truster_Connection=yes"
 Dim conn As New SQLConnection(connStr)
 conn.Open()
 Dim sql As String
 sql = "SELECT COUNT (*) FROM Members"
 Dim cmd As New SQLCommand(sql, conn)
 Dim ergebnis As String
 ergebnis = cmd.ExecuteScalar().toString()
 Dim t As String
 t = "Die Tabelle Members hat " & _
 ergebnis & " Zeilen. <br>" & _
 "Das Kommando lautet: " & _
 cmd.CommandText & "<br>" & _
 "Der Kommandotyp ist: " & _
 cmd.CommandType
 ausgabe.innerHTML = t
End Sub
</script>
<html><head><title>
Demo zu SQLCommand.ExecuteScalar
</title></head>
<body>
<h3>Demo zu SQLCommand.ExecuteScalar</h3>
<p runat="server" id="ausgabe" />
</body></html>

Thanks for your help an sorry for my english.

Greets
Flash_Prince

The problem is objects in tempdb is valid for limited time so your object the Member table does not exist. Microsoft provided the tempdb for SQL Server to be used to proccess complex queries and test queries so when you create a table if you did not drop it SQL Server will drop it at some point. Try the links below to download sample databases you can create and use with SQL Server 2005. The databases comes with tables but you can create your own tables so you can add the member table to any of the three database. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

|||

The problem is, that i want to use the database without entering the path of the database file in the sourcecode. When I use the nordwind database, all the sql queries are working very well. But if I use the Nordwind Database, I only know to connect by OleDB.

Can you tell me how to use a database in the way decribed above with a sql connection?

I hope, this is described understandable, cause my english is not the best.

|||

You need to create connection string and most of the information you need is covered in the two links below. Hope this helps.

http://www.connectionstrings.com

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||The problem is in your connection string. You have a typo: "Truster_Connection=yes" should be "Trusted_Connection=yes".

Invalid character value for cast specification/other errors

We are on SQL Server 2000 SP3
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and move it
into a centralized database. Of the 108 tables/articles I have, only one is
causing me a problem. It appears to be on a text defined column(we have
other text columns that are fine). The column can contain HTML tags, code,
etc. From what I see, the system is encountering some character(s) in the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in just fine.
The errors on the bcp insert are:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
Any ideas/suggestions on how to get around this? I tried putting the data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was to not
call this particular S.P. (don't recall it's name right now). But, if I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug
can you verify that the publisher and subscriber have the same mdac level.
If not there is probably something in your data which is an invalid value.
Can you query your date data to ensure that all values are valid.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
> We are on SQL Server 2000 SP3
> We are trying to get to SQL 2005, but cannot do that until we consolidate
> our databases so the upgrade will complete in a reasonable amount of time.
> I am trying to use bcp to copy data out from each database and move it
> into a centralized database. Of the 108 tables/articles I have, only one
> is
> causing me a problem. It appears to be on a text defined column(we have
> other text columns that are fine). The column can contain HTML tags,
> code,
> etc. From what I see, the system is encountering some character(s) in the
> file that is treating it a an end-of-line. If I adjust the first record
> being processed to just have 'normal' text in it, it will load in just
> fine.
> The errors on the bcp insert are:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 01000, NativeError = 4836
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
> BCP copy in failed
> Any ideas/suggestions on how to get around this? I tried putting the data
> into a temporary table, but received the same error. With bcp are the
> merge triggers fired? I didn't think so as nothing goes into
> MSmerge_contents. I had read about a stored procedure that may be getting
> executed that tries to parse the data. They said the solution was to not
> call this particular S.P. (don't recall it's name right now). But, if I'm
> not executing any merge S.P., then any other S.P.s the system may be using
> for the bcp are out of my control aren't they?
> Thanks for any help,
> Doug
>
|||Hillary,
The bcp out & in are both occurring on the server/publisher. At this
point, there is no subscription involvement.
What is valid verses invalid for a text data type? The users can put
basically whatever they want into this data type. It is usually HTML and
text, but I suppose end-of-line, tabs, other HTML formatting can be in there.
We really don't want the system to interpret any of the data in this column,
just want it to move it. My guess is the problem is it's moving out to a
data file. I tried using a temporary table, hoping that would help, but it
didn't.
Looks like the MDAC level on the server is 2.82.1830.0
Doug
"Hilary Cotter" wrote:

> can you verify that the publisher and subscriber have the same mdac level.
> If not there is probably something in your data which is an invalid value.
> Can you query your date data to ensure that all values are valid.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
>
>
|||Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.
[vbcol=seagreen]
It is possible that there is something in the text data which it is
interpreting as an end of column or end of row character which causes it to
try to insert text into a datetime column. I notice you have exactly 10
failure messages before a final give up message. How many columns are there
in the table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...[vbcol=seagreen]
> Hillary,
> The bcp out & in are both occurring on the server/publisher. At this
> point, there is no subscription involvement.
> What is valid verses invalid for a text data type? The users can put
> basically whatever they want into this data type. It is usually HTML and
> text, but I suppose end-of-line, tabs, other HTML formatting can be in
> there.
> We really don't want the system to interpret any of the data in this
> column,
> just want it to move it. My guess is the problem is it's moving out to a
> data file. I tried using a temporary table, hoping that would help, but
> it
> didn't.
> Looks like the MDAC level on the server is 2.82.1830.0
> Doug
> "Hilary Cotter" wrote:
|||Hilary,
I too think there is something the bcp is interpreting as
end-of-line...something. But it appears to me it is on creating the out
file. I've created .txt, .dat, etc files (don't know that the format of the
output file matters to bcp). When I open the output file, I see part of the
text column has been put on the second line and even some has gone to the
third line.
I'm thinking the creation of the output file is where the problem really is.
It hasn't kept each record's data to its own line. When it tries to
populate a column, it is picking up data from another column which records is
the data type errors.
I have 11 records in the output. I had 'adjusted' the first record before
extracting it so it didnt have any special code in it,just some text. This
line loaded okay. The 10 errors you mention are probably because of the
other 10 records, not so much the number of columns. There are 12 columns in
the table.
It seems the bcp is trying to interpret the data as the output file is
being created. Is there either some way to stop that or some options that
would allow it to properly read this text column? Again, about anything
can be in it. I know if I do an INSERT INTO table1 .......SELECT col1,
col2, etc FROM this_table that works fine. I'm trying to avoid the
INSERT as I don't want the MSmerge_contents to be populated as the subscriber
already has the data. We're just trying to consolidate publisher dbs.
Thanks,
Doug
"Hilary Cotter" wrote:

> Anything but binary should be acceptable for text, however it is complaining
> about bcp'ing into the datetime column, and a numeric column.
>
> It is possible that there is something in the text data which it is
> interpreting as an end of column or end of row character which causes it to
> try to insert text into a datetime column. I notice you have exactly 10
> failure messages before a final give up message. How many columns are there
> in the table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...
>
>
|||Can you try to bcp the data out, and then bcp it into a table with an
identical structure as the published table. Using the firstrow and lastrow
options you should be able to figure out where the problem data is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I too think there is something the bcp is interpreting as
> end-of-line...something. But it appears to me it is on creating the out
> file. I've created .txt, .dat, etc files (don't know that the format of
> the
> output file matters to bcp). When I open the output file, I see part of
> the
> text column has been put on the second line and even some has gone to
> the
> third line.
> I'm thinking the creation of the output file is where the problem really
> is.
> It hasn't kept each record's data to its own line. When it tries to
> populate a column, it is picking up data from another column which records
> is
> the data type errors.
> I have 11 records in the output. I had 'adjusted' the first record before
> extracting it so it didnt have any special code in it,just some text.
> This
> line loaded okay. The 10 errors you mention are probably because of the
> other 10 records, not so much the number of columns. There are 12 columns
> in
> the table.
> It seems the bcp is trying to interpret the data as the output file is
> being created. Is there either some way to stop that or some options that
> would allow it to properly read this text column? Again, about anything
> can be in it. I know if I do an INSERT INTO table1 .......SELECT col1,
> col2, etc FROM this_table that works fine. I'm trying to avoid the
> INSERT as I don't want the MSmerge_contents to be populated as the
> subscriber
> already has the data. We're just trying to consolidate publisher dbs.
> Thanks,
> Doug
>
> "Hilary Cotter" wrote:
|||Hilary,
fyi...... I had used the -c option for creating the output file and
reading it in.
I changed this to use the -n option and then the process worked okay.
The out file just won't be as easy to look at for later reference, but at
least the process of moving the data to the consolidated database works,
which is most important at this point.
Thanks for your help,
Doug
"Hilary Cotter" wrote:

> Can you try to bcp the data out, and then bcp it into a table with an
> identical structure as the published table. Using the firstrow and lastrow
> options you should be able to figure out where the problem data is.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...
>
>

Interview question

Hi,
I had a job interview yesterday and they gave me a small test to complete.
One of the questions was the following... I was not sure what to answer...
If you have the following table:
CREATE TABLE [Customers] (
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL
) ON [PRIMARY]
And all the queries that you will have for this table are like these:
1. LastName ='Simpson'
2. FirstName ='John' and LastName='Smith'
3. LastName='Parker' and FirstName like 'J%'
4. LastName ='Owen'
5. FirstName ='Danny' and LastName='Jackson'
6. LastName='Owen' and FirstName= 'Michael'
7. LastName like 'A%'
.
(with different values for FirstName and LastName):
What kind of index (only one) do you think it would be more effective for
running those queries faster? Please, explain why.
Any ideas?
Thanks!!On Thu, 10 Mar 2005 10:07:23 -0500, Star wrote:
(snip)
>What kind of index (only one) do you think it would be more effective for
>running those queries faster? Please, explain why.
Hi Star,
A composite index on (lastname, firstname) would be best. Since there
are no other columns in this table, might as well make it clustered.
Reason: all queries presented include at least the last name (or the
start of the last name); some include (part of) the first name as well,
so each query can use this index to immediately jump to the part of the
index where matches are.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Clustered index on (lastname, firstname).
The common column in all queries is Lastname so it should appear first in
the index. Clustered because if you have only one index it usually makes
sense to cluster it.
David Portas
SQL Server MVP
--|||create nonclustered index ix_nc_customers_lastname_firstname on
customers(lastname, firstname)
go
- nonclustered
- composite
- first column should be LastName in order to allow 1, 4 and 7
AMB
"Star" wrote:

> Hi,
> I had a job interview yesterday and they gave me a small test to complete.
> One of the questions was the following... I was not sure what to answer...
> If you have the following table:
> CREATE TABLE [Customers] (
> [FirstName] [varchar] (50) NOT NULL ,
> [LastName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
> And all the queries that you will have for this table are like these:
> 1. LastName ='Simpson'
> 2. FirstName ='John' and LastName='Smith'
> 3. LastName='Parker' and FirstName like 'J%'
> 4. LastName ='Owen'
> 5. FirstName ='Danny' and LastName='Jackson'
> 6. LastName='Owen' and FirstName= 'Michael'
> 7. LastName like 'A%'
> ..
> (with different values for FirstName and LastName):
>
> What kind of index (only one) do you think it would be more effective for
> running those queries faster? Please, explain why.
>
> Any ideas?
> Thanks!!
>
>|||An Index on LastName, FirstName would be the most optimal as there is no
occurance of
FirstName column only.
The above index will be used by both the criterias which have
LastName,FirstName and just
LastName.
Gopi
"Star" <nospam@.nospam.com> wrote in message
news:e88xfLYJFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I had a job interview yesterday and they gave me a small test to complete.
> One of the questions was the following... I was not sure what to answer...
> If you have the following table:
> CREATE TABLE [Customers] (
> [FirstName] [varchar] (50) NOT NULL ,
> [LastName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
> And all the queries that you will have for this table are like these:
> 1. LastName ='Simpson'
> 2. FirstName ='John' and LastName='Smith'
> 3. LastName='Parker' and FirstName like 'J%'
> 4. LastName ='Owen'
> 5. FirstName ='Danny' and LastName='Jackson'
> 6. LastName='Owen' and FirstName= 'Michael'
> 7. LastName like 'A%'
> .
> (with different values for FirstName and LastName):
>
> What kind of index (only one) do you think it would be more effective for
> running those queries faster? Please, explain why.
>
> Any ideas?
> Thanks!!
>|||Thank you, guys!
It helped a lot... now I know for the next time :(|||this interview wasn't in Orlando (or Celebration) Florida was it ?
Greg Jackson
PDX, Oregon|||definately Clustered on LastName, FirstName
GAJ