How to create sql server login for workgroup computer?

How can I add a new sql server login using windows authentication where the login id is from a user belonging in a workgroup?

No domain connection. Just a workgroup account will have a login in the sql server using windows authentication. Thanks
Thats not possible, WIndows authentication can be used if the server can prove your identity. I a (non-trusted) workgroup, this is not possible, because everyone can join the workgoup and could spoof another ones identity.

You can only use the local accounts of the SQL Server to do so. In those enviroments you can impersonate the user with a remote account on the SQL Server machine programmatically (like in C# to run under a remote account of the SQL Server box).

There is another way of choosing the same name and the same password on the SQL Server as well as on the client bix, but I don′t think and stated that several times, that this is not a proficient solution.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to create multiple connection in T_SQL script

How to create multiple connection in the same T-SQL script and apply this in a query.

*I can do that?

Exemple like that (is not working...!)
========================
Server Name:ServerName1
User:user_srv1
Password : PW_srv2
Server Name:ServerName2
User:user_srv2
Password : PW_srv2

SELECT a.FieldID, b.FieldID FROM
ServerName1.DatabaseName.dbo.TableName a,
ServerName2.DatabaseName.dbo.TableName b
WHERE a.FieldID=b.FieldIDCreate server1 and server2 as link servers, then you can execute the query.

How to create internet connection?

Hi everybody,
Is there a client side approach of sending and receiving data from a sql database without a web service or a server component (servicedcomponent and server app)? How can you simulate a network connection over the internet that could send and receive data from and to database?

How can you create a class that would do the same task as a network connection using internet communication as medium? Please point me to topics and documents about this matter.

Thanks.

denpsia

SQLServer provides this feature.

I move this post.

|||

In .NET you would use the HttpWebRequest class, here is a simple bit of sample code to send a SOAP packet.

private void soapRequest()
{
Uri soapUri = new Uri("http://" + soapServer + "/clear_integrated");
string soapRequest =
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<SOAP-ENV:Envelope xmlns:SOAP-ENV=\"http://schemas.xmlsoap.org/soap/envelope/\" " +
"xmlns:sql=\"http://schemas.microsoft.com/sqlserver/2004/SOAP\">" +
"<SOAP-ENV:Body>" +
"<sql:sqlbatch>" +
"<sql:BatchCommands>" +
"select * from authors" +
"</sql:BatchCommands>" +
"</sql:sqlbatch>" +
"</SOAP-ENV:Body>" +
"</SOAP-ENV:Envelope>";

System.Text.UTF8Encoding utfEncoder = new UTF8Encoding();
byte [] requestBytes = utfEncoder.GetBytes(soapRequest);

// Do the work.
for(int i=1; i<=loopCount; i++)
{
try
{
HttpWebRequest webRequest = (System.Net.HttpWebRequest)WebRequest.Create(soapUri);
webRequest.Credentials = System.Net.CredentialCache.DefaultCredentials;
webRequest.Timeout = timeout;
webRequest.KeepAlive = true;
webRequest.Headers.Add("SOAPAction", "\"http://schemas.microsoft.com/sqlserver/2004/SOAPsqlbatch\"");
webRequest.ContentType = "text/xml;charset=\"utf-8\"";
webRequest.Accept = "text/xml";
webRequest.Method = "POST";
webRequest.ContentLength = requestBytes.Length;
Stream requestStream = webRequest.GetRequestStream();
requestStream.Write(requestBytes, 0, requestBytes.Length);
requestStream.Close();
WebResponse webResponse = webRequest.GetResponse();

StreamReader responseStream =
new System.IO.StreamReader(webResponse.GetResponseStream(), Encoding.UTF8);

string stringResponse = responseStream.ReadToEnd();
responseStream.Close();
webResponse.Close();

if (1445 != stringResponse.Length) failCount++;

}
catch (Exception ex)
{
logex(ex);

}
}
}

|||

Hi Matt,

This topic is quite old. Thanks for the reply, at that time I post this topic, I am working on windows application on a tablet pc that needs internet connection and remote access to a remote MySQL database. This code of yours is for asp.net and how exactly would the data be transmitted? What protocol or utility would handle the actual sending of data, in my work then we uses VPN to establish remote database connection and ras dialup to establish internet connection. Thanks for the code, it might proved useful next time.

den2005

How to create internet connection?

Hi everybody,
Is there a client side approach of sending and receiving data from a sql database without a web service or a server component (servicedcomponent and server app)? How can you simulate a network connection over the internet that could send and receive data from and to database?

How can you create a class that would do the same task as a network connection using internet communication as medium? Please point me to topics and documents about this matter.

Thanks.

denpsia

SQLServer provides this feature.

I move this post.

|||

In .NET you would use the HttpWebRequest class, here is a simple bit of sample code to send a SOAP packet.

private void soapRequest()
{
Uri soapUri = new Uri("http://" + soapServer + "/clear_integrated");
string soapRequest =
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<SOAP-ENV:Envelope xmlns:SOAP-ENV=\"http://schemas.xmlsoap.org/soap/envelope/\" " +
"xmlns:sql=\"http://schemas.microsoft.com/sqlserver/2004/SOAP\">" +
"<SOAP-ENV:Body>" +
"<sql:sqlbatch>" +
"<sql:BatchCommands>" +
"select * from authors" +
"</sql:BatchCommands>" +
"</sql:sqlbatch>" +
"</SOAP-ENV:Body>" +
"</SOAP-ENV:Envelope>";

System.Text.UTF8Encoding utfEncoder = new UTF8Encoding();
byte [] requestBytes = utfEncoder.GetBytes(soapRequest);

// Do the work.
for(int i=1; i<=loopCount; i++)
{
try
{
HttpWebRequest webRequest = (System.Net.HttpWebRequest)WebRequest.Create(soapUri);
webRequest.Credentials = System.Net.CredentialCache.DefaultCredentials;
webRequest.Timeout = timeout;
webRequest.KeepAlive = true;
webRequest.Headers.Add("SOAPAction", "\"http://schemas.microsoft.com/sqlserver/2004/SOAPsqlbatch\"");
webRequest.ContentType = "text/xml;charset=\"utf-8\"";
webRequest.Accept = "text/xml";
webRequest.Method = "POST";
webRequest.ContentLength = requestBytes.Length;
Stream requestStream = webRequest.GetRequestStream();
requestStream.Write(requestBytes, 0, requestBytes.Length);
requestStream.Close();
WebResponse webResponse = webRequest.GetResponse();

StreamReader responseStream =
new System.IO.StreamReader(webResponse.GetResponseStream(), Encoding.UTF8);

string stringResponse = responseStream.ReadToEnd();
responseStream.Close();
webResponse.Close();

if (1445 != stringResponse.Length) failCount++;

}
catch (Exception ex)
{
logex(ex);

}
}
}

|||

Hi Matt,

This topic is quite old. Thanks for the reply, at that time I post this topic, I am working on windows application on a tablet pc that needs internet connection and remote access to a remote MySQL database. This code of yours is for asp.net and how exactly would the data be transmitted? What protocol or utility would handle the actual sending of data, in my work then we uses VPN to establish remote database connection and ras dialup to establish internet connection. Thanks for the code, it might proved useful next time.

den2005

sql

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1sql

how to create a socket/IP connection using SQL?

I just want to know how to create a socket/IP connection using SQL prodecure or function

please help..

thanx

Hi,

SQL Server does not provide an explicit way to create a socket. Could you please explain why you need this? Are you trying to establish a TCP/IP connection to SQL Server?

Thanks,

Il-Sung.

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.
But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.

What is the best way to do this inside a CLR procedure?

Thanx a lot

Hi!

Create another connection to the server and create a transaction on this second connection. Please note that since your main connection is inproc (“context connection=true” in the connection string), the second connection should be non-inproc (network. Of cause, you can connect to the same server.).

|||Thank you,

I tried this already. But I got an error when I call BeginTransaction on the second connection. When I remember correctly it as "Transcation already in use by another session". I call the procedure from t-sql inside a begin tran block

Any idea about that?

I will try to build a small example an post it here.|||

Hello,
I made a small example to reproduce the error.

CLR procedure
<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub Test(ByVal sConnectString As String)

Dim t As SqlClient.SqlTransaction = Nothing

Dim c As SqlClient.SqlConnection = Nothing

c = New SqlClient.SqlConnection(sConnectString)

c.Open()

t = c.BeginTransaction()

t.Commit()

c.Close()

End Sub

T-SQL caller:
begin tran
execute Test 'Server=(local);database=cpwerx;user Id=sa;password=xyz'
commit tran

Error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Cas':
System.Data.SqlClient.SqlException: Transaction context in use by another session.
System.Data.SqlClient.SqlException:
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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

|||

Hi!

Modify your connection string so that it has "Enlist=false" in it. Otherwise the connection inherits current transaction.

More on "Enlist" option is in doc on SqlConnection.ConnectionString property

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a datasource which Connection type is SQL Server Anlysis Service in rss script?

I am making a rss script to deploy the reports and datasources.
I need to create a datasource which Connection type is SQL Server Anlysis Service. Thanks.

Does anyone know that? Or I am asking a stupid question?|||

I assume this has already been resolved but for anyone else;

Create a Datasouce in VS (or BIDS) of the Connection Type that you require and then open up the resulting .rds to

view the resulting connection type code.

i.e. For Analysis Services Connection Types

<Extension>OLEDB-MD</Extension> = Extension Code is "OLEDB-MD"

How to create a datasource which Connection type is SQL Server Anlysis Service in rss script?

I am making a rss script to deploy the reports and datasources.
I need to create a datasource which Connection type is SQL Server Anlysis Service. Thanks.

Does anyone know that? Or I am asking a stupid question?|||

I assume this has already been resolved but for anyone else;

Create a Datasouce in VS (or BIDS) of the Connection Type that you require and then open up the resulting .rds to

view the resulting connection type code.

i.e. For Analysis Services Connection Types

<Extension>OLEDB-MD</Extension> = Extension Code is "OLEDB-MD"

How to create a connection to Access Database

Hi guys,

I want to create a report from Access Database. Can anybody tell me how to connect and create a data source from Access data source. A step by step guide is preferable. I am more concerned on the connection string.

Thanks.

There are two standard data providers that may be used to connect to Access databases:The JET 4.0 .NET OLE DB provider and Access ODBC driver. The former is newer and generally more efficient than the latter.

In the "Connection Properties" dialog,

1) pick "OLE DB" under "Data Source"

2) pick "Microsoft JET 4.0 OLE DB Provider"

3) enter the full path of Access file under "Server or File Name"

4) the connection string would be like

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb"

5) in the "Credentials", use "Admin" as user name and leave password blank

One thing to be aware of is that RS must have the necessary security access to open the Access DB file. Make sure that the account running RS has the privileges to open the Access database file and its containing folder.

HTH,

|||

Thanks buddy, I really appreciate your help.

How to create a connection into Sybase Database

Hi, i already create a connection to sybase(ASA) with odbc from ssis (from "data sources" section). but when i try to make data flow source it cann't. so i try to make a connection using "data source views" but when the process comes to end, it becomes error. from my analysis, the Sybase db cannot accept sql command with "[]", because in the fase "data source views" i have to select one or more table, and the default microsoft is using "[]", like: select * from [dbo].[customer], but sybase db cannot handle this. does anyone have the solution for this? Thanks.

Sincerely

Yugicm

I am not clear why you need to use a DSV. If you just entered the SQL into the source it should work. You can then choose the syntax required.

If using the OLE-DB Source, look at the Access mode option, and try "SQL command". This allows you to manually enter the SQL which is often a better idea anyway. Specifying columns is good practice, and generally results in faster packages as you can reduce the amount of data you extract and then handle in the pipeline.

How to copy tables from local machine to remote SQL server

We currently have a PPTP connection set up for our developers to
access our development SQL server through a VPN tunnel. When they
need to copy tables up to the dev SQL from their local machine they
simply do a DTS copy.

However, we are now moving to a thin client solution where they will
be working on a terminal server. They will have access to the
development SQL servers and SQL tools such as EM and QA. However,
they will not have access to their local SQL server and, therefore,
will not be able to directly perform DTS copies. We have explored
several possibilities such as exporting tables to a .csv or .mdb file
and then importing them on the development SQL server but this is not
ideal because things are lost in that process (e.g. primary keys,
field names, data types, etc.)

My question is this: Is there a way to export and then import SQL
tables without losing dependent objects such as primary keys and data
types in the process? If any of you are working with a similar
situation I would really like to hear how your remote users copy
objects from their remote location to your SQL servers. Thanks!

Ryan

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict211310.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630I'm not sure how your developers will get their files to the server
(FTP?), but in any case they can easily create object definition
scripts from Enterprise Manager or Query Analyzer.

Assuming they're maintaining their SQL code in a source control system,
then they will be creating these scripts anyway. So they could recreate
(or perhaps just ALTER) a table with a script, then reload the data
from a csv file. Or perhaps make a copy of the original table, modify
it, insert the data from the copy, and then drop the copy.

If this doesn't help, you might want to clarify what the issue is -
problems creating scripts, running them etc.

Simon|||"Simon Hayes" wrote:
> I'm not sure how your developers will get their files to the
> server
> (FTP?), but in any case they can easily create object
> definition
> scripts from Enterprise Manager or Query Analyzer.
> Assuming they're maintaining their SQL code in a source
> control system,
> then they will be creating these scripts anyway. So they could
> recreate
> (or perhaps just ALTER) a table with a script, then reload the
> data
> from a csv file. Or perhaps make a copy of the original table,
> modify
> it, insert the data from the copy, and then drop the copy.
> If this doesn't help, you might want to clarify what the issue
> is -
> problems creating scripts, running them etc.
> Simon

Thanks for your reply.

The developers can copy files from there local hard drives via their
terminal server session. The problem is that they cannot use DTS to
copy SQL objects because there are firewalls between their local
machine and the terminal server.

It would be possible for them to script the table in EM and then use
bcp to insert the data but I was hoping for a simpler solution with
fewer steps involved. Some of the developers are not so SQL saavy and
Im afraid that we will get stuck supporting them. The extra time
spent on these activities will also give them an excuse to bill us for
more hours (they are contractors).

I have been testing the export of data to .csv and .mdb files and it
is not always straightforward to do. If anyone has any experience
with this your advice is very welcome. Thanks!

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict211310.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=723562|||Here's a workaround solution if you'd like.

1. Create a backup of the database you want to send to the SQL Server.
Let's say your DBName is Performer.
2. Restore as another database...Performer1
3. Delete the tables/other objects you don't want to move up.
4. Do another backup of Performer1...maybe into Performer1.bak
5. Upload it to Production.
6. Restore as Performer1.
7. DTS the tables over or if you simply need to move the data, DTS only
the data to the existing tables.

...and 8. Good luck :o)

Hope this helps,

-Umar Farooq.|||1.DTS out to disk the table(s) you need (using DTS for this is much less
prone to error than BCP)
2. Upload files to production
3. DTS Back in (once again DTS is easier than BCP)

To avoid foreign key confilicts you need to DTS any parent data in before
child data (if you need to move new tables you will need to script and copy
them and recreate at other end before loading data)

You can make the task completely automatic by doing steps 1 and 2 in a DTS
package locally, then do step 3 on production and pole for the files, only
upload when they are there an then delete them (or move them) when the
import is complete.

This will mean that your contractors do nothing or just initiate an automate
process.

Hope this helps
Julian

"Umar Farooq" <UmarAlFarooq@.gmail.com> wrote in message
news:1112753008.047280.36140@.o13g2000cwo.googlegro ups.com...
> Here's a workaround solution if you'd like.
> 1. Create a backup of the database you want to send to the SQL Server.
> Let's say your DBName is Performer.
> 2. Restore as another database...Performer1
> 3. Delete the tables/other objects you don't want to move up.
> 4. Do another backup of Performer1...maybe into Performer1.bak
> 5. Upload it to Production.
> 6. Restore as Performer1.
> 7. DTS the tables over or if you simply need to move the data, DTS only
> the data to the existing tables.
> ...and 8. Good luck :o)
> Hope this helps,
> -Umar Farooq.