Create and manage Store Procedures from inside VS

With a local conn to SQL2000 I can edit sprocs no problem, but if I change the connection to a remote computer (to which I have all permissions) I cannot create or manage stored procedures from within VS IDE? Is there a work around?

I noticed that a procedure written for the localhost accepts CREATE PROCEDURE and changes it to ALTER PROCEDURE. When I script the sprocs from my development machine to the server, those scripts with ALTER in them do not work. I change them to CREATE and they work fine.

I haven't found anything yet on MSDN about this, but will continue to look.

Thanks in advance,

_EHi _E

You may have better luck with this question in the SQL Server Tools - General forum.
Allan|||Thanks, I'll try there. Didn't know if it was an IDE or SQL thing.

_E

create a flat file on a remote server

Hi,

In the new flat file connection dialog box, can i create a flat file on a remote server? thanks.

Yes, if you can access that server through a network share. Try using the UNC path, e.g.

\\ServerName\ShareName\Folder\File.txt

\\DGPC\C$\Temp\File.txt

sql

Create 1 connection to execute multiple Packages

I want to create 1 connection because I have to execute multiple packages (2005). But when I go into the execute package task editor, the dropdown only ever has "New Connection". Is this a bug?Connections are package local. One package cannot see another package's connection managers.
We, unfortunately, don't have the notion of a file data source which it sounds like you're looking for. Basically, a way to create a connection that you can share across multiple packages.
You'll need to create a new connection per package.
K|||Kirk, not sure if I explained correctly. I'm trying to have 1 master package that executes child packages all on the same "ETL server". So I want 1 connection to be the "ETL Server" such that it can be changed in 1 place (actually, I would just have a runtime setting change it based on a config) when moving from DEV (each developer would have their own instance) to QA to PROD.

The reason I want many packages is so that multiple ETL developers can work on a component and build it without having to wait. Additionally, if everything was in 1 package, it would be huge.

Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?|||OK, a few questions to clarify.

What kind of connection do you want the one connection to be SQL, file?
You have one package that you want to call multiple subpackages?
You want the subpackages to use the one connection?
I'm not sure what you mean by "1 connection to be the 'ETL Server' "

"Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?"
I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?

I think what's confusing me here is that your description has a lot of moving parts. Can you simplify the problem into two or three root issues. I have a simple mind and can't deal with all the complexity. :)

|||Let me offer a scenario and you can tell me how it doesn't meet your needs.

You can create a parent package. It can call all the other sub-packages. When the subpackages execute, the parent packages connection is available to them. However, not at design time. I think this is the issue you're having. The sub-package doesn't have areference to the parent package at design time, so it doesn't have the connections either.

What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server".

Is this what you're after?|||see responses below, thanks for taking the time to respond.

Q. What kind of connection do you want the one connection to be SQL, file?
A. The connection of the SSIS server where the subpackages reside.

Q. You have one package that you want to call multiple subpackages?
A. YES

Q. You want the subpackages to use the one connection?
A. YES

Q. I'm not sure what you mean by "1 connection to be the 'ETL Server' "
A. If I store all the subpackages on SQL Server, I'll bound to have different servers for different environments, etc.

Q.I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?
A. sub-packages

I'm not after the connections inside the subpackage from the parent packaeg, nor the connections in the parent package from the subpackage.

What I am looking for is to create 1 connection and tie it to where the subpackages are stored since it needs to be dynamic without having to spin through every execute package task in the master package and change the sql server it resides on at runtime.

Alternatively, I could create a new connection for every execute child package in my master package all pointing to the same SQL Server that is storing them, but its extremely redundant and would only be useful if I'm pointing to (LOCAL) and the user and password never changes.

Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection? I thought that was the reason for the "connection manager" abstraction layer, so that a physical connection can be changed in one place, and all logical connections are automatically redirected?

Any chance of implementing this? Alternatively, implementing a FOR EACH connection enumerator (although, this would be hokey)?

Followup question would be this: If I'm executing a package from another package, what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server? I'm assuming that if its not stored in sql server, it needs to be brought in from the file system and compiled on its 1st use?

My goal is to create an ETL where multiple developers can work on different components at the same time. The easiest way to do this would be to create 1 package per table. A master package would call all the child packages, but the master package needs to know which ETL server all the child packages are stored in, which changes from environment to environment.
|||

"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?

"what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server"
No performance difference between the two storage methods once loaded. Packages aren't compiled. They're read into the object model.

"The easiest way to do this would be to create 1 package per table. "
Really? Why is this easiest. Why limit it to one package per table. Why not just have developers develop package files and check them into sourcesafe? Then when they're done developing them, deploy to SQL Server? Not sure I follow this logic.

Please keep the threads cogent by only asking only one question per thread.

K

|||Hi,
I'm coming into this late and I think I may be misunderstanding it all but let me have a punt anyway.

You have 1 master package which is intended to call multiple child packages.
You want 1 connection manager in the master package to be used to call all child packages.
You want to cycle through a list of all the child packages and execute them using 1 Execute Package Task
If that is all correct then I think what you want to do is possible using property expressions.

Have I understood correctly?

-Jamie|||"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?

Whew, thats all i need, maybe its a bug - using June CTP. I create a new execute package task and then create a new connection for it. Then I create a 2nd execute package task, and click on the connection dropdown, but all that is available is New Connection.

|||

Ah! You're using SQL Connections. Dah!
Yes, this is a bug!
Thanks, great find. Please file a bug.
K

Crate connection in T-SQL

Helo everybody

How to crate differente connection in the same script and apply this in a query. I use Query analyser. I can do that?

If you ave something about that ... internet link...

Exemple like that (is not working...!)
========================
Server Name:ServerName1
User:user_srv1
Pass:pass_srv1
Server Name:ServerName2
User:user_srv2
Pass:pass_srv2

SELECT a.FieldID, b.FieldID FROM
ServerName1.DatabaseName.dbo.TableName a,
ServerName2.DatabaseName.dbo.TableName b
WHERE a.FieldID=b.FieldID
=========================

THANKSYou should be creating linked server to access the database on a remote server. You could create the linked server and the login on the fly and drop them after you are done with your code. check out BOL for creating the linked server.

-cbarus

CR Formula

Hello

I have a little problem with formula in CR10!
I have a report designed on a table (ex: table1).
At runtime, i change the database connection to connect my report to another db and table (ex : table2), all in SQL.
Table 1 and table have exactly the same structure but not the same name.
My report have a formula refercing a db field (ex : {table1.Type}). When I want the preview the report with data from table2, the viewer send the following error : "This field name is not known"
I've tried to change the formula at runtime but no way, it doens't works ! :mad:
Can anybody help me ? thanks a lotYou need to place those columns in your report and delete old columns

Covert Connection from Access to SQL

---

Edited by haidar_bilal - Please place your code within< code > and < /code > tag. Thank you.

---

I've got to convert the following functions so it connects to the SQL server that uses this connection string: ("server=(local);database=BCA;Trusted_Connection=yes")

The code below works fine to an Access db. I know I've got to change all the Ole objects below to SQL objects to connect to SQL server. But try as I might, I'm still not getting them right. Can anyone help by showing me how to change the code? Please just take the code and substitute the SQL connection strings for the Access string. That way even I could understand it. Sigh.

Public Class Functions
Public Function GetCommandObject(ByVal strCommand As String, ByVal strPath As String, ByVal ExecuteIt As Boolean) As Data.OleDb.OleDbCommand
Dim objConnection As Data.OleDb.OleDbConnection = GetConnectionObject(strPath)
objConnection.Open()
Dim objCommand As New Data.OleDb.OleDbCommand(strCommand, objConnection)
If ExecuteIt = False Then
Return objCommand
Else
objCommand.ExecuteNonQuery()
End If
objConnection.Close()
End Function
Public Function GetConnectionObject(ByVal strPath As String) As Data.OleDb.OleDbConnection
Dim objConnection As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath)
Return objConnection
objConnection.Close()
End Function
Public Function GetReaderObject(ByVal strCommand As String, ByVal strPath As String) As Data.OleDb.OleDbDataReader
Dim objCommand As Data.OleDb.OleDbCommand = GetCommandObject(strCommand, strPath, False)
Dim objReader As Data.OleDb.OleDbDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return objReader
objReader.Close()
End Function
End Class

www.connectionstrings.com show the connection strings for SQLClient:

Standard Security:

"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
- or -
"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
(booth connection strings produces the same result)

Trusted Connection:

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
- or -
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
(booth connection strings produces the same result)

(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Connect via an IP address:

"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Declare the SqlConnection:

C#:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString="my connectionstring";
oSQLConn.Open();

VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()

Which one you use depends upon how you want to login. You can use a username and password, or you can use a Trusted Connection, in which case, the ASPNET user on the Web Server must be a user in SQL Server (this is awkward if you are running SQL Server on a machine other than the Web Server.