Do SQL Server 2005/CLR Triggers support .config Files?

Does anyone know if it's possible to use the standard .config file within a CLR Trigger to read properties via the System.Configuration namespace

I guess it's not possible because the CLR Trigger needs to be compiled as an assembly which is hosted by SQL Server

Thanks

Jason

Hi,

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6d635c2cd23fed7

CLR stuff in SQL Server does not work like those know from "normal" applications. Although you can use the Configuration class to load a configuration somewhere stored on disk, you have to keep in mind, that every namespace / class that you use with are not approved as safe will lower down your security, because you have to mark them as unsafe / external access.

Perhaps any other posters has some other experiences about that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

Do I need /PAE to enable AWE memory?

Hi All
I am running Windows 2000 Advanced Server with SQL 2K
Enterprise. I now have 8 GB of RAM and am using the /3GB
switch in the BOOT.INI file. I want to enable AWE within
SQL but some documentation says that the /PAE switch is
also required within the BOOT.INI file. Is this correct?
I don't want to use the /PAE unless I have to.
Will the /3GB switch, together with enabling AWE in SQL
allow SQL to see all but 128MB of the 8GB of RAM (i.e.
no /PAE)?
Many thanks
H
Sample boot.ini for 8GB memory usage:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microso ft Windows 2000 Advanced Server" /fastdetect /PAE
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microso ft W2K 2000 Advanced Server" /fastdetect /PAE
JBandi
|||No. Without PAE the server only sees up to 4GB.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Do I need /PAE to enable AWE memory?

Hi All
I am running Windows 2000 Advanced Server with SQL 2K
Enterprise. I now have 8 GB of RAM and am using the /3GB
switch in the BOOT.INI file. I want to enable AWE within
SQL but some documentation says that the /PAE switch is
also required within the BOOT.INI file. Is this correct?
I don't want to use the /PAE unless I have to.
Will the /3GB switch, together with enabling AWE in SQL
allow SQL to see all but 128MB of the 8GB of RAM (i.e.
no /PAE)?
Many thanks
HSample boot.ini for 8GB memory usage:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition
(1)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINN
T="Microsoft Windows 2000 Advanced S
erver" /fastdetect /PAE
multi(0)disk(0)rdisk(0)partition(2)\WINN
T="Microsoft W2K 2000 Advanced Serve
r" /fastdetect /PAE
JBandi|||No. Without PAE the server only sees up to 4GB.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

dmp file to mdb

Can anyone possibly tell me how I would go about importing a database from a dmp file?

What is the source of the dmp file?

If it's a SQL backup, you just do a restore.

Otherwise, I'll need to know more about where the file came from.

|||

Hi Kevin,

As far as I am aware the file is a SQL .dmp file but how do I go about doing the restore. I tried yesterday with the restore tool supplied with SQL and recieved an error as a result?

Thanks for your help,

Graham

|||

Hi, can you give more info:

[1] WINDOWS VERSION

[2] SQL VERSION/PATCH

[3] dmp exact filename

[4] Exact steps you perform

[5] Complete error description

also do you know with what version/patch was the dmp file made?

|||

Hi JD,

I have figured out how to add the dmp so no need to worry.

Thanks anyway!!!

Graham Turk

DMO restore problem

I have a C++ app that is using SQLDMO with SqlServer2000. I am Restoring a database using only the .bak file, moving the .mdf and .ldf files. The following is the code:

#ifdef USE_SQLDMO
try
{
if(m_cpServer == NULL)
return E_FAIL;

_RestorePtr cpRestore;
CheckError(cpRestore.CreateInstance(_T("SQLDMO.Restore")));
cpRestore->Action = SQLDMORestore_Database;
cpRestore->Database = _bstr_t(sDbName);
cpRestore->Files = _bstr_t(sBUFile);
cpRestore->LastRestore = false;
cpRestore->ReplaceDatabase = true;
cpRestore->PercentCompleteNotification= 10;
RestoreSinkPtr cpSinkPtr;
//get data path for current server
GetSQLDataPath(sDataPath);
QueryResultsPtr cpFileList;
cpFileList = cpRestore->ReadFileList( m_cpServer);
//get info to change for move from cpFileList and move items
for(int i = 1; i <= cpFileList->Rows; i++)
{
_bstr_t bstrLogicalName = cpFileList->GetColumnString(i,1);
sLogicalName = (LPCTSTR)bstrLogicalName;
sLogicalName.Trim();
_bstr_t bstrPhysicalName = cpFileList->GetColumnString(i,2);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
sPhysicalName.MakeUpper();
if(sPhysicalName.Find(".MDF") > 0)
{
sPhysicalName = sDataPath + "\\" + sDbName;
sFiles.Format("%i",nDatafiles);
if(nDatafiles == 0)
sPhysicalName += ".mdf";
else
sPhysicalName += "_" + sFiles + ".mdf";

nDatafiles++;
}
else if(sPhysicalName.Find(".LDF") > 0)
{
sFiles.Format("%i",nLogFiles);
sPhysicalName = sDataPath + "\\" + sDbName;
if(nLogFiles == 0)
sPhysicalName += ".ldf";
else
sPhysicalName += "_" + sFiles + ".ldf";

nLogFiles++;
}
sNew += "[" + sLogicalName + "],[" + sPhysicalName + "],";
}
sNew = sNew.Left(sNew.GetLength()-1);
sPhysicalName = (LPCTSTR)bstrPhysicalName;

cpRestore->SQLRestore( m_cpServer);
cpRestore->SQLVerify(m_cpServer);
cpRestore = NULL;
bSuccess = true;
}
catch(_com_error& err)
{
PrintComError(err);
PrintProviderError(m_cpServer);
hr = err.Error();
}
#endif

The problem is that the functions appears to work. The Restore command does not return and error and neither does the verify. When I open enterprise manager, the database created but is greyed out and shows a status of loading but never completes.
Any ideas/help would be greatly appreciated.

Sandy

I haven't tried your script, but I suspect that the LastRestore=false is the culprit, as this indicates that this is not the last backup on the restore chain and keeps the database in a loading state as it is expecting a final log backup will be applied.

DMO restore problem

I have a C++ app that is using SQLDMO with SqlServer2000. I am Restoring a database using only the .bak file, moving the .mdf and .ldf files. The following is the code:

#ifdef USE_SQLDMO
try
{
if(m_cpServer == NULL)
return E_FAIL;
_RestorePtr cpRestore;
CheckError(cpRestore.CreateInstance(_T("SQLDMO.Restore")));
cpRestore->Action = SQLDMORestore_Database;
cpRestore->Database = _bstr_t(sDbName);
cpRestore->Files = _bstr_t(sBUFile);
cpRestore->LastRestore = false;
cpRestore->ReplaceDatabase = true;
cpRestore->PercentCompleteNotification= 10;
RestoreSinkPtr cpSinkPtr;
//get data path for current server
GetSQLDataPath(sDataPath);
QueryResultsPtr cpFileList;
cpFileList = cpRestore->ReadFileList( m_cpServer);
//get info to change for move from cpFileList and move items
for(int i = 1; i <= cpFileList->Rows; i++)
{
_bstr_t bstrLogicalName = cpFileList->GetColumnString(i,1);
sLogicalName = (LPCTSTR)bstrLogicalName;
sLogicalName.Trim();
_bstr_t bstrPhysicalName = cpFileList->GetColumnString(i,2);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
sPhysicalName.MakeUpper();
if(sPhysicalName.Find(".MDF") > 0)
{
sPhysicalName = sDataPath + "\\" + sDbName;
sFiles.Format("%i",nDatafiles);
if(nDatafiles == 0)
sPhysicalName += ".mdf";
else
sPhysicalName += "_" + sFiles + ".mdf";

nDatafiles++;
}
else if(sPhysicalName.Find(".LDF") > 0)
{
sFiles.Format("%i",nLogFiles);
sPhysicalName = sDataPath + "\\" + sDbName;
if(nLogFiles == 0)
sPhysicalName += ".ldf";
else
sPhysicalName += "_" + sFiles + ".ldf";

nLogFiles++;
}
sNew += "[" + sLogicalName + "],[" + sPhysicalName + "],";
}
sNew = sNew.Left(sNew.GetLength()-1);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
cpRestore->SQLRestore( m_cpServer);
cpRestore->SQLVerify(m_cpServer);
cpRestore = NULL;
bSuccess = true;
}
catch(_com_error& err)
{
PrintComError(err);
PrintProviderError(m_cpServer);
hr = err.Error();
}
#endif

The problem is that the functions appears to work. The Restore command does not return and error and neither does the verify. When I open enterprise manager, the database created but is greyed out and shows a status of loading but never completes.
Any ideas/help would be greatly appreciated.

Sandy

I haven't tried your script, but I suspect that the LastRestore=false is the culprit, as this indicates that this is not the last backup on the restore chain and keeps the database in a loading state as it is expecting a final log backup will be applied.

DMO and .sql

hi
I juzz wanted to know if i can execute an .sql script using SQL-DMO. The .sql file is generated using Generate SQL Scripts from the console mode of SQL Server.... if so.. how do i proceed .
thankxHave you looked at methods ExecuteWithResults and/or ExecuteImmediate in BOL. I've used these in the past, I had to open up the script file and read it into a variable. Then pass the variable to one of these 2 functions. If using any of these be careful with 'GO' within the script file. In the past it would error on this, I don't know about 2000.

DLL missing or ??

I have SQL server 2000 and access 2003 adp file always disconnect
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
run-time error 429
ActveX component can't create object
How repair this without reinstall all'Hi
This seems to get asked quite a lot http://tinyurl.com/9aypy
Some suggested solutions may be
http://support.microsoft.com/kb/q189366/
http://tinyurl.com/aofef
http://tinyurl.com/eyt7r
John
"Valentin Albastroiu" wrote:

> I have SQL server 2000 and access 2003 adp file always disconnect
> Dim cnn As ADODB.Connection
> Set cnn = New ADODB.Connection
> run-time error 429
> ActveX component can't create object
> How repair this without reinstall all'
>
>|||The PC on which you install the application must have the same version or
greater of MDAC (Microsoft Data Access Components) that was on the
development PC when the application was compiled. If this error is occurring
on the development PC, then perhaps the MDAC components or configuration got
corrupted somehow. Either way, consider downloading and running the latest
verison of MDAC on both the development and end user PCs.
http://msdn.microsoft.com/library/d...mdacinstall.asp
"Valentin Albastroiu" <vali_albastroiu@.hotmail.com> wrote in message
news:uzl%23tZByFHA.736@.tk2msftngp13.phx.gbl...
>I have SQL server 2000 and access 2003 adp file always disconnect
> Dim cnn As ADODB.Connection
> Set cnn = New ADODB.Connection
> run-time error 429
> ActveX component can't create object
> How repair this without reinstall all'
>

dling xml file over SSIS possible?

i need to download a file over HTTP and get a xml file then upload it into SSIS.

Only 2 methods are available in SSIS: FTP and web method. How come there is no HTTP transfer task in SSIS? pretty strange.

then i found a script at

http://www.sqljunkies.com/howto/49e823fd-d126-4134-893d-1fd8bd3bd3ba.scuk

What kind of SSIS tasks do i need to perform this operation? I got the script above but I don't know where to key it in.

It goes in a script task.

-Jamie

|||ok i did the script task but how do i pass the variables remoteuri and the local filename to store to the script task?|||

Put them into the ReadOnlyVariables property of the script task.

http://www.google.co.uk/search?hl=en&q=task+readonlyvariables&btnG=Search&meta=

-Jamie

|||

the readonly variables i shld put

"remoteurl, localfilename"

then how i should set the remote url to http://yahoo.com/test1.xml

localfilename to "c:\test1.xml"?

i look at the urls but not much of help....make me more confused.

dividing a large flat file into small files

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

See this post by John:
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx|||

prashant550806 wrote:

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

This should help as well:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

-Jamie

Divide a 5 GB DB to FTP

I need to divide a 5 GB database into two parts so that
it does reach the 4 GB to winzip a file.
Please help me with this task.
Thank You,
DeanYou can backup a DB to multiple backup files. This is normally done for
striping backup of large DB's (and 4G isn't one...) across multiple disks,
but it will certainly meet your need.
Simply use the SQL Enterprise Manager backup GUI and put two files in the
list instead of one...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...
> I need to divide a 5 GB database into two parts so that
> it does reach the 4 GB to winzip a file.
> Please help me with this task.
> Thank You,
> Dean|||To add to Brian's response, you can use SqlLitespeed
<http://www.dbassociatesit.com/products.asp?prod=1> to compress during the
backup. This reduces both time and space and doesn't have the 4GB size
limit.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...
> I need to divide a 5 GB database into two parts so that
> it does reach the 4 GB to winzip a file.
> Please help me with this task.
> Thank You,
> Dean|||Dan,
I second that motion.
SQLLiteSpeed ROCKS!
But, it does beg the question - why can't MS do it?
James Hokes
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O%234CLTx2DHA.2888@.tk2msftngp13.phx.gbl...
> To add to Brian's response, you can use SqlLitespeed
> <http://www.dbassociatesit.com/products.asp?prod=1> to compress during the
> backup. This reduces both time and space and doesn't have the 4GB size
> limit.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dean" <anonymous@.discussions.microsoft.com> wrote in message
> news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...
> >
> > I need to divide a 5 GB database into two parts so that
> > it does reach the 4 GB to winzip a file.
> >
> > Please help me with this task.
> >
> > Thank You,
> >
> > Dean
>|||> But, it does beg the question - why can't MS do it?
Send your product enhancements requests to sqlwish@.microsoft.com.
I guess it's mostly a matter of prioritizing features based on development
resources. We'd never see a new version if they tried to squeeze everything
into the next release.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:OJ6tMNy2DHA.556@.TK2MSFTNGP11.phx.gbl...
> Dan,
> I second that motion.
> SQLLiteSpeed ROCKS!
> But, it does beg the question - why can't MS do it?
> James Hokes
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:O%234CLTx2DHA.2888@.tk2msftngp13.phx.gbl...
> > To add to Brian's response, you can use SqlLitespeed
> > <http://www.dbassociatesit.com/products.asp?prod=1> to compress during
the
> > backup. This reduces both time and space and doesn't have the 4GB size
> > limit.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Dean" <anonymous@.discussions.microsoft.com> wrote in message
> > news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...
> > >
> > > I need to divide a 5 GB database into two parts so that
> > > it does reach the 4 GB to winzip a file.
> > >
> > > Please help me with this task.
> > >
> > > Thank You,
> > >
> > > Dean
> >
> >
>|||Third-party vendors need to eat, too!
I third the motion -- SQLLiteSpeed has benefited me in a tremendous way.
I'm currently working with DBAssociates on a case study of our deployment...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23Y5WEjy2DHA.1720@.TK2MSFTNGP10.phx.gbl...
> > But, it does beg the question - why can't MS do it?
> Send your product enhancements requests to sqlwish@.microsoft.com.
> I guess it's mostly a matter of prioritizing features based on development
> resources. We'd never see a new version if they tried to squeeze
everything
> into the next release.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "James Hokes" <noemail@.noway.com> wrote in message
> news:OJ6tMNy2DHA.556@.TK2MSFTNGP11.phx.gbl...
> > Dan,
> >
> > I second that motion.
> > SQLLiteSpeed ROCKS!
> >
> > But, it does beg the question - why can't MS do it?
> >
> > James Hokes
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:O%234CLTx2DHA.2888@.tk2msftngp13.phx.gbl...
> > > To add to Brian's response, you can use SqlLitespeed
> > > <http://www.dbassociatesit.com/products.asp?prod=1> to compress during
> the
> > > backup. This reduces both time and space and doesn't have the 4GB
size
> > > limit.
> > >
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Dean" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...
> > > >
> > > > I need to divide a 5 GB database into two parts so that
> > > > it does reach the 4 GB to winzip a file.
> > > >
> > > > Please help me with this task.
> > > >
> > > > Thank You,
> > > >
> > > > Dean
> > >
> > >
> >
> >
>

Divide a 5 GB DB to FTP

I need to divide a 5 GB database into two parts so that
it does reach the 4 GB to winzip a file.
Please help me with this task.
Thank You,
DeanYou can backup a DB to multiple backup files. This is normally done for
striping backup of large DB's (and 4G isn't one...) across multiple disks,
but it will certainly meet your need.
Simply use the SQL Enterprise Manager backup GUI and put two files in the
list instead of one...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...

quote:

---

> I need to divide a 5 GB database into two parts so that
> it does reach the 4 GB to winzip a file.
> Please help me with this task.
> Thank You,
> Dean

---

|||To add to Brian's response, you can use SqlLitespeed
<http://www.dbassociatesit.com/products.asp?prod=1> to compress during the
backup. This reduces both time and space and doesn't have the 4GB size
limit.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...

quote:

---

> I need to divide a 5 GB database into two parts so that
> it does reach the 4 GB to winzip a file.
> Please help me with this task.
> Thank You,
> Dean

---

|||Dan,
I second that motion.
SQLLiteSpeed ROCKS!
But, it does beg the question - why can't MS do it?
James Hokes
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O%234CLTx2DHA.2888@.tk2msftngp13.phx.gbl...

quote:

---

> To add to Brian's response, you can use SqlLitespeed
> <http://www.dbassociatesit.com/products.asp?prod=1> to compress during the
> backup. This reduces both time and space and doesn't have the 4GB size
> limit.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dean" <anonymous@.discussions.microsoft.com> wrote in message
> news:043d01c3db04$1f9dc010$a501280a@.phx.gbl...
>

---

|||> But, it does beg the question - why can't MS do it?
Send your product enhancements requests to sqlwish@.microsoft.com.
I guess it's mostly a matter of prioritizing features based on development
resources. We'd never see a new version if they tried to squeeze everything
into the next release.
Hope this helps.
Dan Guzman
SQL Server MVP
"James Hokes" <noemail@.noway.com> wrote in message
news:OJ6tMNy2DHA.556@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Dan,
> I second that motion.
> SQLLiteSpeed ROCKS!
> But, it does beg the question - why can't MS do it?
> James Hokes
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:O%234CLTx2DHA.2888@.tk2msftngp13.phx.gbl...
the[QUOTE]
>

---

|||Third-party vendors need to eat, too!
I third the motion -- SQLLiteSpeed has benefited me in a tremendous way.
I'm currently working with DBAssociates on a case study of our deployment...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23Y5WEjy2DHA.1720@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Send your product enhancements requests to sqlwish@.microsoft.com.
> I guess it's mostly a matter of prioritizing features based on development
> resources. We'd never see a new version if they tried to squeeze

---

everything

quote:

---

> into the next release.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "James Hokes" <noemail@.noway.com> wrote in message
> news:OJ6tMNy2DHA.556@.TK2MSFTNGP11.phx.gbl...
> the
size[QUOTE]
>

---

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

distribution database size and other file questions

I think my distribution database blew up in size a long time ago for a
specific problem, and I don't know if its large size (34 GB) will cause
other problems. Is there a way I can flush out old irrelevent data and then
shrink it to a smaller size? The published DB is about 110 GB and has pull
subscriptions to 2 other servers.
Also, what types of RAID disks should the distribution database be placed
on? What about the temp db, log files, and main published database files?
The published database has a separate .ndf for non clustered indexes. Are
these better to be on the same disk or different disks?
Is there anywhere someone can point me to in order to find out more
information about this stuff? I have searched high and low, and can't find a
good place to research this info and apply the information to our particular
setting.
Thanks in advance
--Kristy
As the distribution database involves high write activity it should be raid
10. temp db and logs should be on raid 10 as well. If the database has over
20% of its io being write, it should be raid 10, otherwise make it raid 5.
Ideally the ndf will be on a separate physical disk on a separate array
(raid 5 as its high read activity normally).
Can you shrink the distribution db as it is? Also you might want to issue
the following select * from distribution.dbo.msreplication_status to tell
you how many commands are in the queue. If its small you should be able to
shrink the db, if it is large you have to work on getting these commands to
the subscriber database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
>I think my distribution database blew up in size a long time ago for a
> specific problem, and I don't know if its large size (34 GB) will cause
> other problems. Is there a way I can flush out old irrelevent data and
> then
> shrink it to a smaller size? The published DB is about 110 GB and has pull
> subscriptions to 2 other servers.
> Also, what types of RAID disks should the distribution database be placed
> on? What about the temp db, log files, and main published database files?
> The published database has a separate .ndf for non clustered indexes. Are
> these better to be on the same disk or different disks?
> Is there anywhere someone can point me to in order to find out more
> information about this stuff? I have searched high and low, and can't find
> a
> good place to research this info and apply the information to our
> particular
> setting.
> Thanks in advance
> --Kristy
>
|||Thanks for the info. Is there a place you can point me to that will teach me
how to determine this on my own? I have your Transactional and Snapshot
book; is it in there?
We only have RAID 10 and RAID 1 set up on our server. But I am moving the
files around because I see a lot of things that indicate file placement
causing performance problems.
I check on distribution.dbo.msreplication_status. I know I tried shrinking
it many months before, but nothing worked.
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> As the distribution database involves high write activity it should be
raid
> 10. temp db and logs should be on raid 10 as well. If the database has
over
> 20% of its io being write, it should be raid 10, otherwise make it raid 5.
> Ideally the ndf will be on a separate physical disk on a separate array
> (raid 5 as its high read activity normally).
> Can you shrink the distribution db as it is? Also you might want to issue
> the following select * from distribution.dbo.msreplication_status to tell
> you how many commands are in the queue. If its small you should be able to
> shrink the db, if it is large you have to work on getting these commands
to[vbcol=seagreen]
> the subscriber database.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
pull[vbcol=seagreen]
placed[vbcol=seagreen]
files?[vbcol=seagreen]
Are[vbcol=seagreen]
find
>
|||My mistake it is msdistribution_status.
There are some white papers on the Microsoft web site - try this one
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%238I%2302OBHHA.2276@.TK2MSFTNGP03.phx.gbl...
> Thanks for the info. Is there a place you can point me to that will teach
> me
> how to determine this on my own? I have your Transactional and Snapshot
> book; is it in there?
> We only have RAID 10 and RAID 1 set up on our server. But I am moving the
> files around because I see a lot of things that indicate file placement
> causing performance problems.
> I check on distribution.dbo.msreplication_status. I know I tried shrinking
> it many months before, but nothing worked.
> --Kristy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> raid
> over
> to
> pull
> placed
> files?
> Are
> find
>

Distribution Database Log File Growth

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.

Distribution Database Log File Growth

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.

Distribution Database

My distribution Database is set to Simple mode , but the log file is filling
up every few days. the error is as follow
The log file for database 'Distribution_CCMSQL' is full. Back up the
transaction log for the database to free up some log space..
what is going on how can I backup the transaction log when my database is in
Simple Mode.
Thanks,
run a dbcc opentran in there to see if there is an orphaned transaction.
Other than that I would put it into full and dump it every 15-20 minutes.
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
<msnews.microsoft.com> wrote in message
news:O4h99l7xHHA.3724@.TK2MSFTNGP06.phx.gbl...
> My distribution Database is set to Simple mode , but the log file is
> filling up every few days. the error is as follow
> The log file for database 'Distribution_CCMSQL' is full. Back up the
> transaction log for the database to free up some log space..
> what is going on how can I backup the transaction log when my database is
> in Simple Mode.
> Thanks,
>

Distribution Data File Growth

Recently rebuilt Windows 2003 OS, SQL Server 2000 sp3, 3 publishers of
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
Michelle,
have a look at the msrepl_commands table and see if this is the cause of the
large size. If it is, it could be that you have a subscriber who hasn't
synchronized in a while, or the distribution cleanup agent is disabled, or
you have an anonymous subscriber, so the commands remain until the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||Results from msrepl_commands table:
publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454
Ran distribution clean up agent job (has been running successfully, every 10
minutes):
publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467
The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).
I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.
Thanks,
Michelle
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution Agent - Stored Procedure Error Logging

I know how I can set-up my distribution agent jobs to log them
to a file and up the verbosity of the procedure.
I am in the process of creating snap-shot's of my stored procedures
for replication. The Snapshot agent works just fine, it is
when I use the distribution agent to send it to the subscriber
that it will stop on the first error it finds.
Is there a way to have it run through all the Stored Procedures
so that I don't have to constantly re-create the publication
by removing the first problem sp?
It helps me give the sp's to our developers in one shot
rather than one at a time.
Dave
I think your best bet is to create a separate publication for each stored
procedure. This way the only procs which fail to be replicated are the
problem ones - the remainder will be replicated.
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
"David Gresham" <gresham@.panix.com> wrote in message
news:d62hq1$mar$1@.reader1.panix.com...
> I know how I can set-up my distribution agent jobs to log them
> to a file and up the verbosity of the procedure.
> I am in the process of creating snap-shot's of my stored procedures
> for replication. The Snapshot agent works just fine, it is
> when I use the distribution agent to send it to the subscriber
> that it will stop on the first error it finds.
>
> Is there a way to have it run through all the Stored Procedures
> so that I don't have to constantly re-create the publication
> by removing the first problem sp?
> It helps me give the sp's to our developers in one shot
> rather than one at a time.
>
> Dave
>