Mars Connection Problem

I am trying to make a connection to an SQL database held on myserver I am able to connect through a machine data source using access using the credentials as below the attempt fails at con.open with following error:

System.Runtime.InteropServices.COMException was caught
ErrorCode=-2147467259
Message="Named Pipes Provider: Could not open a connection to SQL Server [53]. "
Source="Microsoft SQL Native Client"
StackTrace:
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
at Client_Access.JobRequest.AddJob_Click(Object sender, EventArgs e) in C:\Documents and Settings\Robert\My Documents\Visual Studio 2005\Projects\ Client Access\ Client Access\JobRequest.vb:line 392

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sPassword As String, sUserID As String
sPassword = "abcde"
sUserID = "cClient"
Try
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(myserver);" _
& "Database=transportRecs;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "UID=" & sUserID & ";" _
& "PWD=" & sPassword & ";" _
& "MARS Connection=True;"
Dim mySQL As String

mySQL = "SELECT * FROM dbo_jobitem " '& _
'" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset
With rst
.ActiveConnection = con
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.Open(mySQL)
.MoveLast()
.MoveFirst()
.MoveLast()
.MoveFirst()
Debug.Print(.RecordCount)
End With

Catch ex As Exception
MsgBox(ex.ToString)
Finally
If (con.State = ConnectionState.Open) Then con.Close()
End Try

Can anyone help.

Regards,
Joe

The connection string is pooched. Use a UDL to write the connection string for you.

1. Open notepad

2. Save the blank file as test.udl to your desktop

3. Open the UDL and create and test your connection string.

4. Close and open in notepad

5. Copy the generated connection string

Adamus

|||

Hi Adamus Turner

I have follwed the instructions supplied. The connection is good using the connection string below:

con.ConnectionString = "Provider=MSDASQL.1;Password=abcde;Persist Security Info=True;User ID=cClient;Mode=ReadWrite;Extended Properties= DSN=TransportDB_Comp1;Description= ;UID= cClient;PWD=abcde;APP=Microsoft? Windows? Operating System;WSID=MIDLAPTOP2; transportRecs;Network=DBMSSOCN" & _

"MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT dwjobitemid FROM dbo_jobitem"

'";" '" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset

With rst

.ActiveConnection = con

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenStatic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

.MoveLast()

.MoveFirst()

.MoveLast()

.MoveFirst()

MsgBox(.RecordCount)

End With

When the connection attemps to open a record set (.open(mySQL) an error is called, Invalid object name? I know the table exists?

Regards

Joe

|||

Is the table name dbo_jobitem or dbo.jobitem?

It's a strange convention to use an underscore.

Adamus

|||

Also, do not post logins and passwords to databases.

Moderator, please remove login and password from connection string.

Adamus

|||

Hi Adamus

I have managed to sort out the problem, the issue was not with the connection string after I had used your advice the connection was good. The database tables when viewed on the sql server all had names starting “dbo_” I had attempted to look at the database schema.tables using “select * from information_schema.tables” out of vbnet but could not work out how you returned recordset!table_name in vb.net> I did managed to achieve this using vba. The table names returned were all shown minus the “dbo_” prefix I altered my sql to match and bingo.

Any user names, passwords etc used in my examples are all fictitious. And have been used for illustration only. Many thanks for your assistance with my postings

Regards

Joe

Mapping Source and Destination columns

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

I think you have to to be a bit more specific to get answers that will help you.

Mapping Package Variables to a SQL Query in an OLEDB Source Component

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

Not sure what your problem is; but the solution is to create a variable to hold your query, let's say [User::SQLStatement] and use as value your query. Then set EvaluateAsExpression property of the variable to true. In the expression property, create an expression that will be evaluate at run time where you concatenate your query with the [User::Date_BeginningYesterday] variable. Back in your OLE DB Component you need to choose 'SQL Statement from variable' and then choose [User::SQLStatement] from the list.

Notice that you need to cast the value of [User::Date_BeginningYesterday] to string in the expression builder before concatenating its value to the sql statement.

Rafael Salas

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

|||

The question that I have is: Can one embed a package variable into a sql statement while selecting "SQL Statement" from the data access mode. If so how would would go about that?

...cordell...

|||

The short answer is no. you cannot reference a SSIS variable directly in your sql statement. You need to use '?' and then use the parameter mapping in your OLE DB source OR, to concatenate it within a second varibale as I described in the previous post.

Rafael Salas

sql

Mapping Columns Automatically?

New to SSIS...

I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?

Use the Export Data Wizard in SSMS.

-Jamie

|||Thats fine and dandy when starting from scratch. But if you have spent a lot of time building scripts and other actions in an existing package... it seems that it should be simple to add all columns to an existing text export. This seems like it would be such a common issue there has to be a solution.|||

You could replace your existing source adapter with a new one. The default behaviour is to select all columns which by the sound of it is what you want.

The new columns will automatically appear in the metadata of downstream components.

-Jamie

|||Thanks.. I will try that and see how it goes.

Mapping Column Headers from Source to Rows in a spreadsheet

Hello,

I am trying to do the following:

I have been given an MS Access Database that has a table with columns

I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).

Any suggestions?

Where the problem is?

Manually changing flat file source has no effect

I have a data flow that reads from a flat file source, goes through one data transformation component to change from unicode to normal text and writes the data to a SQL Server table. This has been working fine throughout development using a specific source file as input. I have now manually changed the path and name of the input source file in the connection manager to point to a new file and the task continues to process the old text file.

I open the connection manager and check its properties and preview the data and it all looks fine - it is finding the new file. I also edit the flat file source component and preview the data and it shows the data from the new file. I run the data flow by right-clicking and selecting Execute Container and it continually reads the old file and processes it! (I do the right-click thing because this is just one small part of a larger package.)

This has got to be a bug, but just where I wonder. Anyone ever see this before? I'm going to try to run the entire package in debug mode, instead of right-clicking, next and see if that's any different. Anyone have any ideas on how to force a refresh of the necessary internal components to make it read the new file? All the external properties point to the new file, but it's not being read.

Joe

Update:

I ran the entire package and found no difference in execution - it's still reading the wrong input file. I then deleted and recreated the connection manager, again specifying the new file to read. It still continued to read the old file. I deleted the flat file source component and recreated it, specifying the latest connection manager (twice, since I must have pointed it at the wrong one the first time and it read a completely different file). I still have the same problem of it reading the wrong input file. I don't know what else to recreate that would have any effect. Does anyone have any ideas? I need to change the pointer to different files multiple times and have it read several different input files. This has got to work somehow. Any help is appreciated.

Joe

Perhaps you have a Package Configuration enabled that you have forgotten about? Have you ever run the package configuration wizard on this package? Do you see anything in the log or progress report to suggest that a config is being loaded? What do you see in the progress window?

When you deleted and recreated the connection manager, did you create a new name for the connection manager, and then change the source adapter to use the new connection manager? That would throw off any configurations.

Finally, you could try renaming the original source file - the package may fail, but the specific point of failure may tell you more.

Donald

|||

Thanks for your comments, Donald. I ran it again this morning and got the same error, but, as suggested, I checked the Output log and did indeed see a reference to a configuration file. I would have said that I had no configuration set up for this package, since I only started looking into configurations a couple of weeks ago. However, I was out of the office for a week and I must have gotten further than I thought, because there was a configuration set up for this package. My memory is already starting to deteriate.

Sorry for taking your time, but I sure do appreciate your help.

Joe

Manually change report

Hello,

I use Reporting Services in my solution. The data i have as source is very uncertain. Sometimes i manually have to delete a record from the report (not the database itself). I woluld like to have a checkbox or something simular to delete from report and totals, grafs would be updated. At the same time I would like the report to make a comment that a record was taken away, alternative let the user make a comment. Is this possible? DO i have to make a own application for that?

Thank you for your help!

Best regards,

Luskan

If you want my personal opinion, I don't think RS would be the best application for this.

Personally I would return the data from SQL to something like Excel using ADO/VBA or even MSQuery

and base your report / charts on the data then in Excel.

Deleting/changing the data will then have no effect on the data in your SQL database and if

constructed correcly, your charts/reports in excel will be updated automatically...

|||

Well, thank you for your opinion!

But i think I got a Solution. Besides exporting to Excel you can use cascading report parameters in order to to achive the same thing. The only problem i have know is that "Value field" and "label field" does not work as it is inteded to do.

Best regards,

Luskan

Management Studio support for Foundation Server Source Control

Does the SQL 2005 Management Studio support source Control from Team
Foundation Server. All the documentation refers to VSS.
I have the TFS client installed and is working with Visual Studio but is not
a visable option in SQL Management Studio.
Is this a managed news group?
"PeterT" wrote:

> Does the SQL 2005 Management Studio support source Control from Team
> Foundation Server. All the documentation refers to VSS.
> I have the TFS client installed and is working with Visual Studio but is not
> a visable option in SQL Management Studio.
|||It's managed if you created a no spam alias with one of the five provided
domains. This will tell you how to create that no spam alias.
https://msdn.microsoft.com/subscript...scription.aspx
"PeterT" wrote:
[vbcol=seagreen]
> Is this a managed news group?
> "PeterT" wrote:
|||Thanks for the info. I will try again
I have also spoken to MS Online Concierge Chat and even they cannot find the
answer to this post!!!
"olc@.online.nospam" wrote:
[vbcol=seagreen]
> It's managed if you created a no spam alias with one of the five provided
> domains. This will tell you how to create that no spam alias.
> https://msdn.microsoft.com/subscript...scription.aspx
> "PeterT" wrote:

Management Studio support for Foundation Server Source Control

2nd Post on this as I didi not have a NoSpam alias!!
Does the SQL 2005 Management Studio support source Control from Team
Foundation Server. All the documentation refers to VSS.
I have the TFS client installed and is working with Visual Studio but is not
a visable option in SQL Management Studio.
Does any one know of a TFS Plug in for SQL 2005 Managament Studio?
Hi Peter,
Thanks for your posting.
From your descriptions, I understood you would like to know whether it is
possible to use Team Foundation Server for SQL Server 2005 source control.
If I have misunderstood your concern, please feel free to point it out.
First of all, since TFS has not been released, we do not have much document
on this.
Based on my knowledge, unfortunately, TFS cannot work with SQL Server 2005
now. You may also post your question in the newsgroup below for more
information
MSDN - Visual Studio Team System
http://forums.microsoft.com/MSDN/def...pid=5&siteid=1
Last but not the least, other communities might share their invaluable
experience here.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.