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 SQL Server Connect using VB Studio?

I've read all of the related questions and none answer my situation. I am using Visual Studio 2005. I have not been able to connect to the remote SQL database at all. I have attempted to write a SMO to make the connection however, the info I have been able to get to on MSDN didn’t have enough information to carry me through.

Here is the code I found it on MSDN and have been attempting to use.

Module Module1

Sub Main()

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common

'Declare a ServerConnection object variable to specify SQL authentication, login and password.

Dim conn As New ServerConnection

conn.LoginSecure = False

conn.Login = xxxx

conn.Password = xxxxx

'Connect to the local, default instance of SQL Server.

Dim srv As Server

srv = New Server(conn)

'The actual connection is made when a property is retrieved.

Console.WriteLine(srv.Information.Version)

'The connection is automatically disconnected when the Server variable goes out of scope.

End Sub

End Module

The code executes however, nothing happens. I don’t know where the IP address comes into play. Thanks in advance.

· SQL Server 2005 Standard

· TCPIP

· Server not starting (I’m able to connect using SQL Server Management Studio

· I don’t know whether SQL Browser is enabled enabled. I use the IP address when using SQL Server Management Studio

· I’m not sure however, I believe it runs under Network Service with the Host provider

· I have not made a firewall exception

· Windows XPSP2

· Running Norton AntiVirus 2007

Thanks for any help or advice.

The conn object will takt the ip address if you want to connect with one of that to the server, then you probably do not nee the login and the password specified in the code as you can pass it with the connection string. (See connectionstrings.com for more information) If you want to specify the login and password within your application you can use the new Server("10.10.10.10") (as 10.10.10.10 is the IPAddress)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank yousql

Remote Connection to SQL Server 2005 on Windows 2003

Hi Fellows!
I am trying to connect a SQL Server 2005 Server setup on Windows Server 2003 and failing. I have checked few other related posts but i think so i am still unable to find the solution.
My configuration and problem is following;
I want to enable access for my SQL Server to my network as well as through internet using our Fix IP address server.
Configurations:
Server: Win XP Server 2003 along with VS 2005 and SQL Server 2005
Clients : WinXP Laptops (home addition) installed SQL Server Connectivity tools and Express addition and also community edition of Management Studio for Express edition.
I am able to connect the server when i try to use my servername but failed when i try to use ipaddress in the connection.
i tired with telnet, the same problem occurs as i can login by using the network machine name but unable to login by using the local or external ip address.
I have checked with my router configuration and i have allowd both the standard ip and the ip i have given to my named instance and also the port which i have seen in the server log , so i am still unable to find why i am not able to connect it.
Can some one will guide me, i will be really thankfull to him.

start>program>microsoft sqlserver 2005>configuration tools>sql server surface area configuration

>configuration for service and connection>remote connections

>check local and remote

>check both tcp/ip and named pipes

|||Thanks for your reply, i tried to use Surface area tool for my client but it is unable to connect to that client. Especially it also becomes difficult when i am gona operate across the network . (Actually server is in our headquarter and client is in other office).|||

hi,

kindly check if port 1433 is left opened in your firewall

regards,

joey

|||

Try adding sqlservr.exe (present in \MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh

Remote Connection to SQL Server 2005 on Windows 2003

Hi Fellows!
I am trying to connect a SQL Server 2005 Server setup on Windows Server 2003 and failing. I have checked few other related posts but i think so i am still unable to find the solution.
My configuration and problem is following;
I want to enable access for my SQL Server to my network as well as through internet using our Fix IP address server.
Configurations:
Server: Win XP Server 2003 along with VS 2005 and SQL Server 2005
Clients : WinXP Laptops (home addition) installed SQL Server Connectivity tools and Express addition and also community edition of Management Studio for Express edition.
I am able to connect the server when i try to use my servername but failed when i try to use ipaddress in the connection.
i tired with telnet, the same problem occurs as i can login by using the network machine name but unable to login by using the local or external ip address.
I have checked with my router configuration and i have allowd both the standard ip and the ip i have given to my named instance and also the port which i have seen in the server log , so i am still unable to find why i am not able to connect it.
Can some one will guide me, i will be really thankfull to him.

start>program>microsoft sqlserver 2005>configuration tools>sql server surface area configuration

>configuration for service and connection>remote connections

>check local and remote

>check both tcp/ip and named pipes

|||Thanks for your reply, i tried to use Surface area tool for my client but it is unable to connect to that client. Especially it also becomes difficult when i am gona operate across the network . (Actually server is in our headquarter and client is in other office).

|||

hi,

kindly check if port 1433 is left opened in your firewall

regards,

joey

|||

Try adding sqlservr.exe (present in \MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh