Connection Manager problem in ScriptComponent

Hi,

I am using OleDB source as well as Script component in my package. Both these component are using same database. But as of now I am using two connections for each of this comp as I am unable to make use of the OLE DB connection in script component.

How can I solve this. I mean how can I use the the OLE DB conn which I used in OLEDB Source comp in the Script Component.

Any help will be greatly appreciated.

Thanks

For script component, create and use ADO.NET connection manager.

OLEDB connection is not meant to be used from .NET. If you really need to use it, you should create a custom component in C++, rather than using script component.

Connection manager or Data Source

For seting up the connection should we use Connection Manager or New Connection From Data Source?

The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.

But I don't have enough explanation to give it to my team, that why I am not using Data Source.

We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?

I would use connection managers. (Actually, I DO use connection managers). Using package configurations, you can make the connections "dynamic" so-to-speak.|||

Paarul wrote:

For seting up the connection should we use Connection Manager or New Connection From Data Source?

The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.

But I don't have enough explanation to give it to my team, that why I am not using Data Source.

We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?

Both SSIS and SSAS can use data sources; but in SSIS they are optional. On my first SSIS projects I started using data sources until I realized SSIS checks the connection manager properties against the DS definition and gives annoying warnings about they being out of sync (usually the order of the parameters in the connection string)...and that happened every time you open a package. So, now I am creating the connection managers without using DS.

sqlsql

Connection Manager ignores expressions with script component

I have written a script source component and attached a flat file connection. The connection string of which is defined by an expression.

However when I get the connectionstring from the connection in the script it has the default filename value of flat file not the value of the expression. This is proved by passing in the filename variable, and comparing the 2.

The flat file has an expression on the ConnectionString of @.[User::filename]

Can someone confirm this is a bug.

Is this any help to you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295220&SiteID=1

-Jamie

|||Looks like it, needs some clarification though.

Connection Issues with Oracle Data Source

We are getting a recurring connection problem when connecting to an Oracle 9i
database. The error we get is ORA 12154 - Cannot create a connection to data
source. This issue only occurs on the Report Server and not in the
development environment.
This is an intermittent problem although it occurs more often than not.
Some individuals never have any issues while others have it with a high
degree of frequency.
We have tried recreating the data source, using both a Shared Data Source
and a direct data source with no change in the problem.
Can anyone shine some light on this issue?I am getting the same kind of error. It is very strange. I have 2
ReportServers running on a 2000 machine and a 2003 machine. The 2000 machine
runs every report great, but on 2003 I get random conneciton errors like you
do. I would love to know why this is happening, and if this is a known
issue. Thanks.
"Marty" wrote:
> We are getting a recurring connection problem when connecting to an Oracle 9i
> database. The error we get is ORA 12154 - Cannot create a connection to data
> source. This issue only occurs on the Report Server and not in the
> development environment.
> This is an intermittent problem although it occurs more often than not.
> Some individuals never have any issues while others have it with a high
> degree of frequency.
> We have tried recreating the data source, using both a Shared Data Source
> and a direct data source with no change in the problem.
> Can anyone shine some light on this issue?|||I'm having similar problems however it seems as if some clients never have
issues while others do. We are trying to get the reports setup for our
helpdesk staff to use, however they have problems running reports that
communicate to Oracle.
We've tried everything, however nothing seems to keep it consistent.
"Marty" wrote:
> We are getting a recurring connection problem when connecting to an Oracle 9i
> database. The error we get is ORA 12154 - Cannot create a connection to data
> source. This issue only occurs on the Report Server and not in the
> development environment.
> This is an intermittent problem although it occurs more often than not.
> Some individuals never have any issues while others have it with a high
> degree of frequency.
> We have tried recreating the data source, using both a Shared Data Source
> and a direct data source with no change in the problem.
> Can anyone shine some light on this issue?

Connection is busy ...

Hi all,

I use VC++ 6 and MFC, with SQL Express 2005.

source code sample :

CRecordset *rec1 = new CRecordset(machine->pDB);
rec1->Open(...,szSQL,...);
while(!rec1->IsEOF())
{
CDBVariant var1,var2;
rec1->GetFieldValue("COL1",var1);
rec1->GetFieldValue("COL2",var2);

int val1 = GET_INDEX(var1);
int val2 = GET_INDEX(var2);
var1.Clear();
var2.Clear();

CRecordset *rec2 = new CRecordset(machine->pDB);
szSQL.Format("SELECT COL3,COL4 FROM TB1 WHERE COL3 = %i AND COL4 = %i",val1,val2);
rec2->Open(...,szSQL,...);
while(rec2->IsEOF())
{
//Something
rec2->MoveNext();
}
rec2->Close();
delete rec2;
rec1->MoveNext();
}
rec1->Close();
delete rec1;
On the red line, i catch a CBDException "Connection is busy with results for another command"

I think, I can't use the same database connection with severals Recordset simultaneous but why and it's normal, what is wrong in my source code or in the database server configuration ?

Please help me ...

By default, the connection you made to server doesn't support MARS(Multiple Active ResultSet). Which means, that you can only open one result set against server per connection. You need to close one result set before you open another result set.

But you can make this work my turning on MARS. You can refer to the following blog for information on how to turn on MARS in your connection string. Please note that you need to use SNAC to be able to use MARS feature.

http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Please let me know if you need more help on this.

Connection is busy ...

Hi all,

I use VC++ 6 and MFC, with SQL Express 2005.

source code sample :

CRecordset *rec1 = new CRecordset(machine->pDB);
rec1->Open(...,szSQL,...);
while(!rec1->IsEOF())
{
CDBVariant var1,var2;
rec1->GetFieldValue("COL1",var1);
rec1->GetFieldValue("COL2",var2);

int val1 = GET_INDEX(var1);
int val2 = GET_INDEX(var2);
var1.Clear();
var2.Clear();

CRecordset *rec2 = new CRecordset(machine->pDB);
szSQL.Format("SELECT COL3,COL4 FROM TB1 WHERE COL3 = %i AND COL4 = %i",val1,val2);
rec2->Open(...,szSQL,...);
while(rec2->IsEOF())
{
//Something
rec2->MoveNext();
}
rec2->Close();
delete rec2;
rec1->MoveNext();
}
rec1->Close();
delete rec1;
On the red line, i catch a CBDException "Connection is busy with results for another command"

I think, I can't use the same database connection with severals Recordset simultaneous but why and it's normal, what is wrong in my source code or in the database server configuration ?

Please help me ...

By default, the connection you made to server doesn't support MARS(Multiple Active ResultSet). Which means, that you can only open one result set against server per connection. You need to close one result set before you open another result set.

But you can make this work my turning on MARS. You can refer to the following blog for information on how to turn on MARS in your connection string. Please note that you need to use SNAC to be able to use MARS feature.

http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Please let me know if you need more help on this.

connection failed - ODBC from remote SQL SERVER

hello,
im trying to create an ODBC data source from a SQL SERVER 2005 database located in a remoted pc.
Client---->PC1 : win 2000 sp4, not sql server
Remote server-->PC2 : win 2000 sp4, sql server 2005 express

From the Command Prompt, im typing "ping PC2" successfully.
After, in the ODBC datasource administrator-->System DNS tab, i choose "sql server" as a new data source , and in the server box , i'm typing PC2/SQLEXPRESS.

In the next steps, im typing the user-passwords.
The error :
connection failed
...
[microsoft][odbc sql server driver][named pipes]ConnectionOpen(CreateFile()).
Connection failed:
SQLState: '08001'
..
Client unable to establish connection

What goes wrong?
I follow the same steps locally, and everything is OK.

Sorry for my bad english:cool:Here's an article (http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx) on troubleshooting connectivity issues on SQL Server Express.|||SQL 2005 does not support the Named Pipes protocol by default. I would choose a different protocol, preferably TCP/IP. You can set the protocol using the Client Configuration button on the second screen of the DSN definition in the ODBC DSN applet.

-PatP

Connection failed

Hello, I'm trying to set up a New Data Source to SQL Server through ODBC and
am getting the following error message after trying to supply correct SQL
Login ID and Password:
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'xxxxxxxxx'
Any help would be appreciated. Thanks.
Just for kicks, try to connect to SQL Server via Query Analyzer using that
login and password
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:ujWBaRJWIHA.5816@.TK2MSFTNGP06.phx.gbl...
> Hello, I'm trying to set up a New Data Source to SQL Server through ODBC
> and am getting the following error message after trying to supply correct
> SQL Login ID and Password:
> Connection failed:
> SQLState: '28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'xxxxxxxxx'
>
> Any help would be appreciated. Thanks.
>
|||Kind of strange in that after a while with really no changes it ended up
working. We were trying it from a wireless laptop first which then we
physically plugged in the network cable into the laptop and still didn't
work...but after about 10 minutes later the same steps did allow the ODBC
setup to finish logging in successfully. Kind of weird but I'm not
complaining.
Thanks a bunch for your speedy reply Kevin. Much appreciated. Take cares
:-)
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:u1L2GKTWIHA.2304@.TK2MSFTNGP06.phx.gbl...
> Just for kicks, try to connect to SQL Server via Query Analyzer using that
> login and password
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:ujWBaRJWIHA.5816@.TK2MSFTNGP06.phx.gbl...
>

Connection error to As 2000 cube data source

Here is my setup: I have Reporting Service and SQL Server 2000 in one box
(Box A), and IIS and Analysis Service in another box (Box B), which has
couple of cubes based on the database in Box A, using SQL Authentication and
Allow save password. Create AS2000 data source at Reporting Project, then
create a report, I can preview the report fine in VS.2003. But I got the
following error after deploying the report to the reportserver and browse the
report:
Cannot create a connection to data source 'Cube'. (rsErrorOpeningConnection)
Get Online Help
Database 'Cube' does not exist.
Do I miss anything?
Thanks
DonAre the cubes on the same domain with your report server?
If not, it won't connect.
"Don" <Don@.discussions.microsoft.com> wrote in message
news:26BEED36-14C5-48A1-87DA-E751CB391888@.microsoft.com...
> Here is my setup: I have Reporting Service and SQL Server 2000 in one box
> (Box A), and IIS and Analysis Service in another box (Box B), which has
> couple of cubes based on the database in Box A, using SQL Authentication
> and
> Allow save password. Create AS2000 data source at Reporting Project, then
> create a report, I can preview the report fine in VS.2003. But I got the
> following error after deploying the report to the reportserver and browse
> the
> report:
> Cannot create a connection to data source 'Cube'.
> (rsErrorOpeningConnection)
> Get Online Help
> Database 'Cube' does not exist.
> Do I miss anything?
> Thanks
> Don
>|||Chris,
Thanks for the quick response. Yes, my OLAP server and Report Server on the
same domain, and my domain user account on both boxes.
"Chris" wrote:
> Are the cubes on the same domain with your report server?
> If not, it won't connect.
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:26BEED36-14C5-48A1-87DA-E751CB391888@.microsoft.com...
> > Here is my setup: I have Reporting Service and SQL Server 2000 in one box
> > (Box A), and IIS and Analysis Service in another box (Box B), which has
> > couple of cubes based on the database in Box A, using SQL Authentication
> > and
> > Allow save password. Create AS2000 data source at Reporting Project, then
> > create a report, I can preview the report fine in VS.2003. But I got the
> > following error after deploying the report to the reportserver and browse
> > the
> > report:
> >
> > Cannot create a connection to data source 'Cube'.
> > (rsErrorOpeningConnection)
> > Get Online Help
> > Database 'Cube' does not exist.
> > Do I miss anything?
> >
> > Thanks
> >
> > Don
> >
>
>

Connection Error

I have an asp page that opens up a crystal report...however; randomly
I either get the report or I get this error:

ADO Error Code: 0x80004005 Source: Microsoft OLE DB Provider for SQL
Server Description: Connection failure SQL State: 08S01

and its happening alot...

Where should I begin looking to solve this..I dont see anything
particular in the event log in both client or server...

thanks

-JimJim (jim.ferris@.motorola.com) writes:
> I have an asp page that opens up a crystal report...however; randomly
> I either get the report or I get this error:
> ADO Error Code: 0x80004005 Source: Microsoft OLE DB Provider for SQL
> Server Description: Connection failure SQL State: 08S01
> and its happening alot...
> Where should I begin looking to solve this..I dont see anything
> particular in the event log in both client or server...

08S01 - Communication Link Failure. Often indicates that some accident
happened on the SQL Server, which forced SQL Server to sever the
connection. Usually accompanied by stack dumps in the SQL Server error log.
(That is not the eventlog.) Such crashes are due to bugs in SQL Server, and
should not occur in an ideal world.

But a flaky network could also be the cause.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connecting VSS to Query Analyzer

Anyone know of a way to connect Visual Source Safe to query analyzer? I love the functionality of Query analyzer but would like to use some source control with my code. Any help would be greatly appreciated.
Thanks in advance.
VSS has a CLI that can be used to integrate with SQL QA. Ken Henderson's
book "The Guru's Guide to SQL Server Stored Procs, XML & HTML" has a section
dedicated on how to set this up (Pg 113 I think). If you've got a copy of
that book, check it out - this way, you can check in & out of VSS directly
from the Query Analyser. Ken also provides a tool called "GGSQLBuilder" that
lets you build versioned db object scripts from VSS as well. If you haven't
got access to the book, post back & I can send you more instructions if you
want to do this..
Regards,
Greg Linwood
SQL Server MVP
"TCG_GILBERT" <TCGGILBERT@.discussions.microsoft.com> wrote in message
news:83BD4718-38EC-4F9B-ADE9-082B7EE902E0@.microsoft.com...
> Anyone know of a way to connect Visual Source Safe to query analyzer? I
love the functionality of Query analyzer but would like to use some source
control with my code. Any help would be greatly appreciated.
> Thanks in advance.
>
|||I you can, get a copy of "the Guru's Guide to SQL Server Stored Procedures..." by Ken Henderson. It has several pages about using source control with QA.
Basically you use the Q.A. tools/customize option to drive the VSS ss.exe program.
For example:
Menu Name: Set Project Path
Command: ss.exe
Args: cp $/path_to_your_project
Menu Name: Set Working Folder
Command: ss.exe
Args: workfold $(FileDir)
Menu Name: Check in Current File
Command: ss.exe
Args: checkin $(FileName) $(FileExt)
InitDir: $(FileDir)
Q.A. Will repace the tokens below when the command is run
$(FilePath) full path to current file (save first or you will get file name with *)
$(FileName) filename without extension
$(FileExt) file ext with leading "."
$(FileDir) dir path of the current file
HTH
-Kevin
"TCG_GILBERT" wrote:

> Anyone know of a way to connect Visual Source Safe to query analyzer? I love the functionality of Query analyzer but would like to use some source control with my code. Any help would be greatly appreciated.
> Thanks in advance.
>
|||Thanks a bunch Greg, I'll go try to get my hands on a copy of this book. If I can't I might ask for a little more help.
Josh
"Greg Linwood" wrote:

> VSS has a CLI that can be used to integrate with SQL QA. Ken Henderson's
> book "The Guru's Guide to SQL Server Stored Procs, XML & HTML" has a section
> dedicated on how to set this up (Pg 113 I think). If you've got a copy of
> that book, check it out - this way, you can check in & out of VSS directly
> from the Query Analyser. Ken also provides a tool called "GGSQLBuilder" that
> lets you build versioned db object scripts from VSS as well. If you haven't
> got access to the book, post back & I can send you more instructions if you
> want to do this..
> Regards,
> Greg Linwood
> SQL Server MVP
> "TCG_GILBERT" <TCGGILBERT@.discussions.microsoft.com> wrote in message
> news:83BD4718-38EC-4F9B-ADE9-082B7EE902E0@.microsoft.com...
> love the functionality of Query analyzer but would like to use some source
> control with my code. Any help would be greatly appreciated.
>
>
|||Thanks a bunch Kevin, I'll go try to get my hands on a copy of this book. If I can't I might ask for a little more help.
Josh
"googleqand@.yahoo.com" wrote:
[vbcol=seagreen]
> I you can, get a copy of "the Guru's Guide to SQL Server Stored Procedures..." by Ken Henderson. It has several pages about using source control with QA.
> Basically you use the Q.A. tools/customize option to drive the VSS ss.exe program.
> For example:
> Menu Name: Set Project Path
> Command: ss.exe
> Args: cp $/path_to_your_project
> Menu Name: Set Working Folder
> Command: ss.exe
> Args: workfold $(FileDir)
> Menu Name: Check in Current File
> Command: ss.exe
> Args: checkin $(FileName) $(FileExt)
> InitDir: $(FileDir)
> Q.A. Will repace the tokens below when the command is run
> $(FilePath) full path to current file (save first or you will get file name with *)
> $(FileName) filename without extension
> $(FileExt) file ext with leading "."
> $(FileDir) dir path of the current file
> HTH
> -Kevin
>
>
> "TCG_GILBERT" wrote:

Connecting to SQL Server 2005 using vb codes.

The data resides in app_data.

In my webconfig file I have this.

<addname="vau"connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|vau.mdf"providerName="System.Data.SqlClient"/>

I understand that there are several different ways to connect and manage information to the database. I think I want to use the behind the codes (.aspx.vb) and on the top of the page I have this.Imports System.Data.

That is where I am stuck. Can you push me in the direction by either explaing to me or post a link that has more information about this.

Thanks.

1st, add a reference to System.Configuration (Assuming VS.Net 2005)

VB.Net code:

Dim cnAs New SqlConnection(ConfigurationManager.ConnectionStrings("vau").ConnectionString)Try cn.Open()Dim cmdAs New SqlCommand() cmd.CommandText ="Select * from SomeTable" cmd.Connection = cnDim rdrAs SqlDataReader = cmd.ExecuteReaderWhile rdr.Read'Do stuffEnd While Catch exAs Exception'Handle errors hereFinally If cn.State = ConnectionState.OpenThen cn.Close()End Try

|||

Whoops, you need another Imports statement:

Imports System.Data.SqlClient

|||

That worked well for select statement but I will also use insert as well as update sql statement. Can you give me examples for those as well. Thanks a ton.

|||

Dim cnAs New SqlConnection(ConfigurationManager.ConnectionStrings("vau").ConnectionString)Try cn.Open()Dim cmdAs New SqlCommand() cmd.CommandText ="Update SomeTable Set SomeField=SomeValue"'Alternatively cmd.CommandText = "Insert Into SomeTable (fields) values (values)" cmd.Connection = cnDim rowsUpdatedAs Integer = cmd.ExecuteNonQuery()Catch exAs Exception'Handle errors hereFinally If cn.State = ConnectionState.OpenThen cn.Close()End Try