remove columns created by replication process

One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :

select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'

Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :

...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?

Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.

Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!

MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?

Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.

-PatP

Remote Connections error

While trying to transfer my website project to another computer I am encountering an error when tryng to run the solution. It gives me a "Does not allow remote connections". Why is this when on the original computer everything worked fine.

Walter

Does the error has anything to do with SQL server?

This error is common when there is a connection problem to SQL server.

Verify your connection string.Make sure SQL server is running. Make sure you are able to connect to the database.

Regards,

David

|||

I think I had an error similar to this before and therefore I'll assume that your problem is like mine and is associated with SQL Server. (I thinkneutrino is of the same persuasion.)

Now if I understand you correctly, you're trying to run a project on one pc and connect to a database on another pc. Therefore I think the problem is that SQL Server is rejecting your remote connection because it's not configured to accept it! You need to allow remote connections to that server.

See this article (How to configure SQL Server 2005 to allow remote connections ).

Now.. if you're problem isn't related to SQL Server ... well.. Ignore all of the above.

Remote Connection to SQL Server Express

Hi ,

After reading the SQLexpress weblog , I tried the solution and its still not working

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

The client computer gives me this:
Run-Time error -2147217843 (80040e4d)

Log-in failed for user "Kit\Guest"

The connection string in the ADO object in my testing VB6 program is:

my_connection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=kit;Initial Catalog=AdelaideMushroom;Data Source=KIT\SQLEXPRESS"

I am confused with the error massage as my conncection string is set to connect on UserID = Kit

I am not sure what is gone wrong. The same VB6 program runs perfectly on host machine

I am not sure is there anything to do with SQL Config. Manager's SQL server 2005 service's Log-ON setting:

Log On as - Build-in account: Network service

could Anyone who managed to get remote connection work give me some advise? I am desperate. have read that weblog many times and do not know how to get it work

If you are using WIndows Authentication (with specifying the SSPI part in the connection string) UserId will be ignored, because this is the SQL Server Authentication. The problem that you are experiencing with the Guets user is based on the setting that you probably have Windows XP and enabled the "Simply File and Printer Sharing". You can disable that by navigating through Explorer > Tools > Folder Options > View > Scroll down to the end , deactivate "Simple file sharing"

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thank you for the answer

I will give it a try when I get back to office tomorrow

|||

Sorry.... but I still get the same error message

I have firewall of host computer disabled, it connects ok with this command

C:\Documents and Settings\Kit>sqlcmd -E -S KIT\SQLEXPRESS,2301

I have simple file sharing disabled, I no longer see any folder visible on my computer (execept printer & fax and Scheduled Tasks) in Map Network drive.

Seems that the client computer has found KIT\SQLEXPRESS but somehow the host computer direct the client computer to Kit\Guest

The host computer User Account shows there are 3 accounts: KIT (myself), SQLDEBUGGER and Guest. I cannot get rid of Guest tough

I've tried turn firewall off on both computer. Same result

Any idea ?