Odd Snapshot Behavior

I'm encountering some behavior in a Snapshot publication I just can't figure
out.
I have a publication consisting of 124 tables. These tables are all lookup
tables so the data isstatic and so are the respcetive table schemas.
I dropped all subscriptions to this publication, dropped the publication,
and disabled replication. I then configured the server for replication. I
recreated the publication with the required articles. I created a new
subscriotion and chose to create the snapshot immediately.
The snapshot gets created just fine, but when the distribution agent
attempts to apply the snapshot, I get the following error:
Cannot drop the table 'dbo.ReportTypeLookup' because it is being used for
replication
The odd thing is that I'm not trying to drop that table at all!! As soon as
I remove that particular command from MSrepl_commands, another error occurs,
only this time it's trying to drop another table.
I am 100% sure no one is trying to drop any tables in production...
If anyone has any insight into this I would truly appreciate it.
Thank You!!
It sounds like the subscriber has been previously set up as a publisher of
these tables. Try running sp_removedbreplication on the subscriber which
should reset the replication flags at the article level and then
synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

ODBC insert of SQL_TYPE_TIMESTAMP

Hi,
I'm trying to insert a time stamp into SQL Server (2005) and can't figure
out the right combination of column type and arguments to SQLBindParameter()
.
My C code currently looks like this:
rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 10, 0,
(SQLPOINTER)&tstamp, 0, NULL);
I've tried different values for the ColumnSize and DecimalDigits parameters,
including 10, 19, 22, 23, 26, and SQL_TIMESTAMP_LEN.
As for the table definition, I've tried DateTime, char(10), char(19), etc.
Different combinations either give me return codes of 22008 or 42S02.
I definitely need to insert into a single column, as I'm trying to maintain
compatibility with an equivalent DB2 database (which, if it helps, uses the
DB2 TIMESTAMP data type, and has no problem with the above code).
Info on the web for this problem is surprisingly scarce. I've found a few
things but nothing concrete - so I'd really appreciate any help.
Thanks in advance!
Regards,
Josh"LDJ" <LDJ@.discussions.microsoft.com> wrote in message
news:37FA6F9F-2352-4F86-B79C-6C262D8099A0@.microsoft.com...
> Hi,
> I'm trying to insert a time stamp into SQL Server (2005) and
> can't figure
> out the right combination of column type and arguments to
> SQLBindParameter().
> My C code currently looks like this:
> rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT,
> SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 10, 0,
> (SQLPOINTER)&tstamp, 0, NULL);
> I've tried different values for the ColumnSize and
> DecimalDigits parameters,
> including 10, 19, 22, 23, 26, and SQL_TIMESTAMP_LEN.
> As for the table definition, I've tried DateTime, char(10),
> char(19), etc.
> Different combinations either give me return codes of 22008 or
> 42S02.
This is probably a little more than you want to see, but ...
We use our own DB objects to encapsulate ODBC access to Oracle,
DB2 and SQL Server (2000). Therefore, some extra code you won't
recognize. But, I think you're just looking for the Bind.
Please see below.
////////////////////////////////////////////////////////////////////////////
/
//
void VParameter::PutDateTime( VDateTime dateTime )
{
SQLRETURN rc;
USHORT msec;
m_LenInd = sizeof( m_timeStamp );
dateTime.DecodeDate( m_timeStamp.year, m_timeStamp.month,
m_timeStamp.day );
dateTime.DecodeTime( m_timeStamp.hour, m_timeStamp.minute,
m_timeStamp.second, msec );
m_timeStamp.fraction = 0;
rc = SQLBindParameter( m_hStmt, m_paramNumber,
SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
sizeof( "yyyy-mm-dd hh:mm:ss" ), 0,
&m_timeStamp, sizeof( m_timeStamp ), (SQLLEN *) &m_LenInd);
CHECK_STMT( m_hStmt, rc );
}
m_timeStamp is just the SQL timestamp struct. A VDateTime is our
own object, based on midnight Dec. 30, 1899 (that's a standard).
m_* are class variables.
Good luck,
- Arnie|||Hi,
This is what I used for my SQLBindParameter and it's working, so hope it'll
help you!
SQLBindParamenter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP,
0, 0, (SQLPOINTER)&value, 0, &cbSize);
Note: columnSize and bufferLength are ignored when the type is
SQL_TIMESTAMP, cbSize is just an SQLINTEGER but asyou know it's also ignored
for timestamps...
I'm not sure whether it makes a difference if you use SQL_C_TIMESTAMP as
opposed to SQL_C_TYPE_TIMESTAMP, but I've been using the former
Hope this helps,
Isabella
P.S. you can check out the following on the function if you haven't already:
http://msdn.microsoft.com/library/d...ndparameter.asp
"Arnie" wrote:

> "LDJ" <LDJ@.discussions.microsoft.com> wrote in message
> news:37FA6F9F-2352-4F86-B79C-6C262D8099A0@.microsoft.com...
> This is probably a little more than you want to see, but ...
> We use our own DB objects to encapsulate ODBC access to Oracle,
> DB2 and SQL Server (2000). Therefore, some extra code you won't
> recognize. But, I think you're just looking for the Bind.
> Please see below.
> //////////////////////////////////////////////////////////////////////////
///
> //
> void VParameter::PutDateTime( VDateTime dateTime )
> {
> SQLRETURN rc;
> USHORT msec;
> m_LenInd = sizeof( m_timeStamp );
> dateTime.DecodeDate( m_timeStamp.year, m_timeStamp.month,
> m_timeStamp.day );
> dateTime.DecodeTime( m_timeStamp.hour, m_timeStamp.minute,
> m_timeStamp.second, msec );
> m_timeStamp.fraction = 0;
> rc = SQLBindParameter( m_hStmt, m_paramNumber,
> SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
> sizeof( "yyyy-mm-dd hh:mm:ss" ), 0,
> &m_timeStamp, sizeof( m_timeStamp ), (SQLLEN *) &m_LenInd);
> CHECK_STMT( m_hStmt, rc );
> }
>
> m_timeStamp is just the SQL timestamp struct. A VDateTime is our
> own object, based on midnight Dec. 30, 1899 (that's a standard).
> m_* are class variables.
> Good luck,
> - Arnie
>
>
>|||Oops sorry LDJ
My columnsize is set to sizeof(TIMESTAMP_STRUCT), not 0... and i was
incorrect, ColumnSize is not ignored for SQL_TIMESTAMP!
Sorry for the confusion.
Isabella
"Isa" wrote:
[vbcol=seagreen]
> Hi,
> This is what I used for my SQLBindParameter and it's working, so hope it'l
l
> help you!
> SQLBindParamenter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAM
P,
> 0, 0, (SQLPOINTER)&value, 0, &cbSize);
> Note: columnSize and bufferLength are ignored when the type is
> SQL_TIMESTAMP, cbSize is just an SQLINTEGER but asyou know it's also ignor
ed
> for timestamps...
> I'm not sure whether it makes a difference if you use SQL_C_TIMESTAMP as
> opposed to SQL_C_TYPE_TIMESTAMP, but I've been using the former
> Hope this helps,
> Isabella
> P.S. you can check out the following on the function if you haven't alread
y:
> http://msdn.microsoft.com/library/d...ndparameter.asp
>
> "Arnie" wrote:
>|||Apologies to Josh and Arnie! I realised the reason why my code worked was
because I was just inserting a date with no time...
Josh, Arnie was correct, the column size should be 19 as it actually
represents the number of bytes needed to display the yyyy-mm-dd hh:mm:ss
format.. If you wanted to show the fraction, you should use 20 + s as the
columnSize, where s is the precision
http://msdn.microsoft.com/library/d.../>
mn_size.asp
Sorry for my unhelpful posts b4
Isabella
"Isa" wrote:
[vbcol=seagreen]
> Oops sorry LDJ
> My columnsize is set to sizeof(TIMESTAMP_STRUCT), not 0... and i was
> incorrect, ColumnSize is not ignored for SQL_TIMESTAMP!
> Sorry for the confusion.
> Isabella
> "Isa" wrote:
>