Publication Creating Bcp Files On Server
Mar 24, 2006using publication from server one to server two. On server one, I have several directories with bcp files. Can the older files and directories be deleted?
View 1 Replies
ADVERTISEMENT
Bug In MS Management Studio For SQL-Server 2005 While Creating A Publication!
Dec 21, 2006Hello,
I found a bug in Management Studio, which can be reproduced with following steps:
1. Create a new publication with the 'New Publication Wizard'
2. Select a database which contains at least 2 tables.
3. Select 'transactional publicatioon'
4. Select at least 2 tables as articles
5. Set article properties for all table articles
6. Change 'Statement Delivery' to 'Insert statement', 'Update statement' and 'Delete statement' and save the article properties.
7. If you now check the properties for a single article you find that the 'Statement Delivery' is set to 'Do not replicate...'. If you generate this publication and create a subscritpion you will see, that actual no replication take place.
It took me a while to find out why my replication doesn't work! :-((
Wolfgang Kunk
PS : Management Studio version is 9.00.1399.00
Can Not Delet A Publication In SQL Server 2005: The Publication Does Not Exist [SQL Server Error: 20026]
Sep 21, 2007Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
Creating A Publication
May 22, 2006hi
I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.
I read all the help in books online however id did not tell me how to bring that link there.
I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005
I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.
any help will be appreciated.
Creating A Publication
Aug 21, 2007Hi,
I've come across this one before in one of the threads, unfortunately for me the author managed to figure it out for his/her self and there for no answer was posted, so can anybody help PLEASE???
Creating Publication
- Creating Publication 'SQLMobile' (Error)
Messages
* SQL Server could not create publication 'SQLMobile'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid object name 'dbo.sysmergepublications'.
Publication 'SQLMobile' does not exist.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 208)
Its been created via UI
on SQL2005
the distributor is correctly installed
i also have not tried creating a publication on any other database.....this is the first.
Any Ideas?
Creating Publication Through C#.net Code
Oct 24, 2007I am using sql 2005 replication in my application. Anybody knows how to create publication through code??please help me...
Error On Creating Publication
Jul 19, 2006Hi all, when I create publication with New Publication Wizard. I get the following errors. Did someone meet this before? Can you help me? Thanks!
===================================
SQL Server could not create publication 'JK'. (New Publication Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn)
at Microsoft.SqlServer.Replication.ReplicationObject.CommonCreate()
at Microsoft.SqlServer.Replication.Publication.Create()
at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.CreatePublicationThreadMethod()
===================================
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Changed database context to 'Test'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=3933&LinkId=20476
------------------------------
Server Name: nxstudio-davidsql2k5_dc
Error Number: 3933
Severity: 16
State: 1
Procedure: sp_addmergepublication
Line Number: 1034
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
Problem Creating A New Transactional Publication
Oct 23, 2007Hi,
I'm creating a new transactional publication and when the process begins to add articles i got the follow error:
Messages
* SQL Server Management Studio could not create article 'Tbl_Menu'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Automatic identity range support is useful only for publications that allow updating subscribers.
Changed database context to 'sodimprumde4'. (Microsoft SQL Server, Error: 21231)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=21231&LinkId=20476
Thanks for any help, If anyone know how to resolve this problem.
Problem With Creating A Merge Publication
Jan 10, 2006Help!!! This is really frustrating me down!
I was trying to follow the MS official tutorial "Creating a mobile application with SQL server mobile" could be found in this link
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
In the step of creating a merge publication, as I noticed, the snapshot agent is started sucessfully. But very soon later (few seconds), it was shut down with an error message. I checked in the error history, the following message I found:
"... The replication agent encountered a failure. The step failed"
I also went to msdb.dbo.sysjobsteps and found "Detected nonlogged agent shutdown".
Anyone could help?
Allowing Transformations When Creating Publication For Replication
Dec 2, 2003I am at my wits end here. For Replication the Books Online clearly state:
"The option to allow transformations is set at the time you create a publication"
However, I cannot find any options that allow me to do this in the Create Publication Wizard.
Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.
Where am I going wrong?
Problem Creating New Publication For MERGE REPLICATION
Jan 3, 2008Hi,
I am stuck when trying to create a new publication. I fill all the wizard tabs but when finishing I get that message (6 times, one for every table):
Code Block
SQL Server Management Studio could not create article 'applicationModules'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
This article cannot use the 'subscriber_upload_options' feature because the publication compatibility level is less than 90. Use sp_changemergepublication to set the publication_compatibility_level of publication 'testMR' to '90RTM'.
Changed database context to 'test'.
Publication 'testMR' has the following property: 'compensate_for_errors'. SQL Server subscribers below version '8.00.0858' will ignore this setting. (Microsoft SQL Server, Error: 21522)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21522&LinkId=20476
The error seemed pretty clear, so I end up doing:
EXEC sp_changemergepublication
@publication = N'testMR',
@property = N'publication_compatibility_level',
@value = N'90RTM'
and running the wizard again.
But I got the same error.
The link on the message take me nowhere.
and by googling for error 21522 I couldn't find any help at all.
Please, could anyone help?
Cheers
Creating Full-text Indexes On A Table In A Publication
Sep 13, 2006I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
-mike
Error Creating Mdf - Connection To SQL Server Files (*.mdf) Require SQL Express 2005 To....
Apr 11, 2006Connection to SQL server files (*.mdf) require SQL express 2005 to function properly. please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
I AM GOING TO RIP MY HAIR OUT WITH THIS PROBLEM. I have reinstalled both sql server express 2005 and VWD about 5 times with the same problem. please, please, please someone throw me a bone here and help me resolve this problem.
When I create a new EMPTY website and I rightclick on my website in the solution explorer and choose add item, I chooe SQL Database, I give it a name 'database.mdf' and click add. I get the following message:
you are attempting to add a database to an asp.net application. for a database to be gfenerally consumable in your site, it should be placed inside the 'app_data' folder. would you like to place the database inside the 'app_data' folder? I click YES (I know this message is normal)
then I get the following message:
Connection to SQL server files (*.mdf) require SQL express 2005 to function properly. please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
I can add anything else but this damn mdf file.
thanks for all your help in advance.
Integration Services :: Creating Dynamic Server Tables Through SSIS As Per XML Data Files Metadata
Feb 15, 2011I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. Â load data on newly created table. Â
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
Creating Log Files
Aug 22, 2000Hello,
what is the procedure needed to create a log file that will log all events such as "what table was updated? when was it updated? who (nt user account) updated it? what record in the table was updated? what was the old value? new value?what caused an odbc error?....
I use access as a front end.
I would really appreciate an answer.
Thank you for your time,
Joe!
Creating TXT Files
Jan 22, 2004Can I use SQL Server to create a txt file on the c drive. Can sql server do nay kind of file handling. Is there a command that i can use in my background job to automatically create a txt file that will show data from a particular table.
Never done it before .. Any ideas...
Help is appreciated.
Thanks
SP__SaveDTS Not Creating Dts Files
Mar 11, 2005When I try and run SP__SaveDTS, all I get is page after page of this. Anyone know how to fix this?
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):
Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also specified)
/~R Repository Database Name <uses default if blank; loads package from repository database>
Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value <may quote entire string (including name:typeid)>
/~L Log file name
/~W Write Completion Status to Windows Event Log <True or False>
DTSRun action (default is to execute Package):
/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from Storage File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)>
Notes:
~ is optional; if present, the parameter is hex text of encrypted value (0x313233...)
Whitespace between command switch and value is optional
Embedded whitespace in values must be embedded in double-quotes
If an option is specified multiple times, the last one wins (except multiple /A)
NULL
Creating Text Files Using Bcp
Jan 23, 2004I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.
Help is appreciated.
I created a stored procedure with the below
declare @filename varchar(50),
@bcpcommand varchar(2000)
set @filename = 'c:
eportmedia.txt'
print @filename
set @bcpcommand = 'bcp "select * from table" queryout "'+ @filename -U -P'
exec master..xp_cmdshell @bcpCommand
Files Not Creating On Win2003
Oct 29, 2006I have created a package which extracts data from a table and creates several files. This works fine on my PC (WinXP SP2), but when i deploy it to the server it doesn't work. I have also tried to create the package from scratch on the server as the administrator and it still has the same problem. Anyone have any ideas?
View 7 Replies View RelatedDealing With .dll Files After Creating Assembly
Apr 10, 2006Hi
I want to use CLR for developing database object such as stored procedures.
I have read the "Getting Started with CLR Integration" from MSDN help and successfully create my first procedure.
I create an assembly in SQLServer2005 with this code:
CREATE ASSEMBLY helloworld from 'c:helloworld.dll' WITH PERMISSION_SET = SAFE
My questions are :
How should I deal with helloworld.dll after creating Assembly?
Can I delete this file?
What should I do for uploading my application on the webserver?
Should I upload any .dll file?
Integration Services :: Creating Files From Their Binary Representations In DB
Apr 29, 2015We run std 2008 r2, I need to recreate flat files from their varbinary(max) equivalents in our db. I have a mix of excel, pdf, word etc to recreate. Will ssis be a good tool for doing this?  I'm wondering what transform(s) would be involved.Â
Perhaps I need to cast to varchar 1st and then land the data but if I recall correctly there is a maximum record length in ssis destination flat file rows. And I'm thinking I would have to map the varbinary (or cast equiv) to a row in the destination once for each file created.  Â
How To Reboot A Publication Server ?
Jul 16, 2004We have to add some hardware and I was wondering if someone could provide a stepwise tip to do it .
It is a 3 Server Environment Pub/Dist/Sub
All three running under SQL2K
Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication
b. Wait for all replications to complete
c. Shut down Publisher ? Or do I have to do something else before that.
Please explain a little .
Thanks
Creating Date Time Stamped Files Names For Backup File
Nov 13, 2007Hi All,
any suggestion on creating date time stamped files names for backup file.
I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.
thanks,
Creating CSV Files Using BCP && Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@
Jan 24, 2008Hi all,
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
=========================================================================================
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
===================================================================
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
Thanks in advance,
Scott Chang
Create A Publication In SQL Server 2005 Express
Nov 20, 2006Hi,
is it possible to create a publication with SQL Server 2005 Express. I can´t seem to find it in Microsoft SQL Server Management Studio Express.
Do i have to install the full version? :(
Isn´t there any other option?
Thanks
SP
SQL Server 2005 Workgroup - No Local Publication Tab
Oct 26, 2007Hi
I've recently gone through installing and setting up merge replication on Server 2005 Standard. All working.
I am now setting up a different box with SQL 2005 Workgroup with SP2. When I go to the Replication tab in Management Studio there is only Local Subscriptions - No Local Publications.
I've re-run the installation but I can't see how Publications is not getting installed orwhy it is missing.
Any ideas much appreciated.
Thanks
Alistair
Linked Server To Text Files: Is Possible To Detect Changes Made To Those Files? (SQL Server 2005)
Sep 3, 2007Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
SQL Server 2008 :: Temporarily Stop Replication / What To Do When Publication Is Dropped
Aug 12, 2015I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
Transactional Publication With Updatable Subscriptions In SQL Server 2005, SvPk 2
Jul 31, 2007
Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.
I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)
The subscriber is unable to connect to the Distributor using the SQL Server login.
Following is the error message I get:
Creating Subscription(s)...
- Creating subscription for 'SQL3' (Warning)
Messages
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
MSDTC on server 'SQL3' is unavailable.
Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476
Please suggest procedure to end this dilemma. The link has no info available.
SQL Server 2008 :: Initialize Transactional Publication From Backup Loss Of Data
Mar 18, 2015I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.
Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.
Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?
SQL Server 2008 :: Tables Not Shown In Articles Window Of New Publication Wizard
May 26, 2015We have restored a database on new server without keeping replication settings. Now while creating the publication no tables are shown in new publication wizard window. In fact we have hundred of tables in database and included in replication on source server from where backup was taken.
What may be the reason that tables are not appearing?
Oracle 9i -&&> SQL Server 2005: Schema_option Parameter When Adding An Article To A Publication
Jul 13, 2006First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.
I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.
I've tried to specify the schema_option parameter as "@schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).
Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253
The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?
If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?
Thanks!
Best regards,
JB
SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups
Jan 9, 2015I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View Related