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

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

sql

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

Marking posts to get faster attention from SSIS Team

Here is one practical initiative for improving the service SSIS team provides to this forum:

We asked our MVPs to mark posts that need attention from the SSIS team and built a system to fetch those messages and send them directly to our internal distribution list. The report will contain all currently marked messages in non-answered threads.That way, forum posts that MVPs target to us will be much easier for us to find, and the response time for them will be shorter.

The MVPs have accepted to help us with this, and we started extracting messages marked by selected users and sending daily reports to the team.

Please let us know what you think about this action and keep an eye on how we are doing with providing answers on marked threads.

Thank you,

Bob Bojanic, SSIS Team

Well done. I think this is a good idea. Is there any difference between this approach and the SSIS MSDN manged forum?|||

Thanks, Larry.

I am not sure what you are referring to as "MSDN managed forum".

|||http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts|||

Pardon, I meant managed newsgroup.

http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

|||

Oh, I see.

This seems to be a web client to our newsgroup discussions. We kind of pre-oriented into using this new forum (forums.microsoft.com) in the course of the last couple of years. The newsgroups seem to be still pretty active but there is no as many MS answerers as you can find here. The groups appear to be self-sustained though, and if you prefer less traffic and more focused discussion you should keep visiting them.

This initiative of marking threads to be looked at by the SSIS team is going to be limited to this forum. We do not have access to the server where the newsgroup posts are kept.

Thanks.

|||

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

|||

Gary Watson wrote:

Sounds like a good idea, but how about getting the list more than once a day, maybe once an hour?

Gary

Well, likely because the Microsoft guys actually have work to do! Once a day is appropriate, I think. Microsoft employees are under no obligation to answer any question on this forum; certainly in no set time frame.|||

Phil is right; answering posts on this forum is not going to give us a good excuse for not finishing our daily tasks.

This initiative is an attempt to get the questions from here closer to potential answerers. Usually, when we (SSIS team members) read these threads we try to use our time efficiently; so if we do not know the answer or do not have time to do some follow up investigation we will just skip the question hoping somebody else would answer it. If a right person does not come across those hard yet important questions in a few days, they might be left unanswered.

Our tool will find such questions, if marked appropriately, and make them visible to the entire SSIS team. There is no time limit when we have to answer them. They are listed in our report until answered. Most of those questions get answered in 2-3 days (it depends on the time it gets into the report, time zones, weekends, etc). Some of them might even require more time for investigations.

It would not make sense to send the report every hour if we cannot commit to the turnaround time on the same scale. The most important aspect is to get them marked so they do not get lost under new piles of posts.

Thanks,

Bob

|||

Hi Bob,

I did not understand before the use of this forum, thanks for clearing that up for me.

I am grateful that you guys can help out whenever you can.

Regards

Gary

Marking Multiple Records?

I have a crystal report that I have been asked to modify for a new process.

This report will take a look at our scheduling system and display which Dies are needed for each order. The Problem I am running into is I need a way to be able to denote that a Die is at another operation if the die number is previously listed in the report. This would be fine if I could sort by Die number, but the report has to be sorted by time so our Die room staff knows when a die is due to a machine and are able to just run through the list without having to search through the report for the next order.

This report is currently grouped by run date (so we can keep shifts straight) and sorted by Run Start Time and then by machine number.
This is the order is has to be in, and I cannot deviate from that.

Attached is an example of the current layout of the report

My issue is that I need some way to Denote that the die for the last order 278381-1 is at another machine (the First 2 orders 278385-1) so our Die Room staff are not searching for the Die on Rack 51 when they go to get the dies for the the last order.

I have thought long and hard about this, and I cannot figure out a way to get through this issue without reordering the report, which I cant do.

Any assistance or direction would be greatly appreciatedProblem has been resolved
The solution involved creating a Sub-Report and filtering out the extra records that did not match and then adding a Formatting to the Field to denote a Duplicate record.

Marking copied records

I have a big problem now. I need to write a SQL statement to copy some
records from a table to another table. At the process of copying, I want to
update a field in both table. The field is to identify whether the record is
the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in runnin
g
sequence. There should be repeating numbers. The reason for doing this is if
a user modifies a record in the new table in the future, I will still know
how it originally was by referring back by that number.
Do you get what I mean? I have no idea whether SQL can do that. And whether
it can be settled in a statement. Can someone help me? Give me some guide?
Thank you.You could do this by adding a DateCopied (datetime -default getdate() )
column to the tables -perhaps even adding a WhoChanged (varchar(50) -default
system_user) column.
Other options include a Sequence (timeStamp datatype) Column.
Either of these choices would allow you to always restructure the sequence
of data changes.
Then you just add a TRIGGER to the primary table to copy the old version to
the archive table whenever there is a data change.
You might google "SQL Server" and "Audit Trail". Here's an article to get
you started:
http://expertanswercenter.techtarge...i980058,00.html
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:8D4B4729-8170-436E-BE05-D87933758E59@.microsoft.com...
>I have a big problem now. I need to write a SQL statement to copy some
> records from a table to another table. At the process of copying, I want
> to
> update a field in both table. The field is to identify whether the record
> is
> the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in
> running
> sequence. There should be repeating numbers. The reason for doing this is
> if
> a user modifies a record in the new table in the future, I will still know
> how it originally was by referring back by that number.
> Do you get what I mean? I have no idea whether SQL can do that. And
> whether
> it can be settled in a statement. Can someone help me? Give me some guide?
> Thank you.

Marking a table as a System Table

Okay, most peoples answer to this may be "Gaaah. Why would you do
this?", or the like, but here's the question anyway:

Are there any stability issues if I mark one of my user tables as a
system table (by switching xtype in sysobjects from 'S' to 'U')?

I'm not doing this as "a cleved bit of security" or some such - my
actual reason for doing this is so that some of my automatic generation
tools do not process this particular table, and I want a method that
will not mean updating each of the tools if I ever add another table
like this.

It APPEARS to work, based on a quick trial, but has anybody got any
direct experience of this? Any horror stories like "Well, it worked
fine for two weeks, then it shot my co-workers, set fire to the company
accounts, and urinated in a corner. Then things got worse"

Also, yes, yes, yes, I do not necessarily expect this to work in future
releases of SQL Server (currently on 2000), but I should avoid naming
conflicts by the fact that the owner isn't dbo.
Thanks in advance for any insights."Damien" wrote:

> Okay, most peoples answer to this may be "Gaaah. Why would you do
> this?", or the like, but here's the question anyway:
> Are there any stability issues if I mark one of my user tables as a
> system table (by switching xtype in sysobjects from 'S' to 'U')?
> I'm not doing this as "a cleved bit of security" or some such - my
> actual reason for doing this is so that some of my automatic generation
> tools do not process this particular table, and I want a method that
> will not mean updating each of the tools if I ever add another table
> like this.
> It APPEARS to work, based on a quick trial, but has anybody got any
> direct experience of this? Any horror stories like "Well, it worked
> fine for two weeks, then it shot my co-workers, set fire to the company
> accounts, and urinated in a corner. Then things got worse"
> Also, yes, yes, yes, I do not necessarily expect this to work in future
> releases of SQL Server (currently on 2000), but I should avoid naming
> conflicts by the fact that the owner isn't dbo.
> Thanks in advance for any insights.

Well I changed a system table once and all the workers recovered, the
building only smoldered, no one noticed the puddle, and then things seemed
to clear up on their own :)

Seriously, though, why not use extended properties for this purpose? For
your table that you don't want to process, use...

exec sp_addextendedproperty
'DoNotProcess',
'1',
'USER',
'dbo',
'TABLE',
'TableThatIsntProcessed',
NULL,
NULL

...Then before you process any table you can...

if not exists (
select null
from ::fn_listextendedproperty(
'DoNotProcess',
'USER', 'dbo',
'TABLE', 'TableThatIsntProcessed',
NULL, NULL
)
where value = '1'
)
begin
print 'process table'
end

This way any table can be selectively set up for processing or not without
touching the system tables and you have a better chance of not getting
broken with succesive releases of SQL Server.

Craig|||Thanks, I'll look into that. I've not used extended properties before
(then again, we've only just upgraded from 7 (as in, last weekend), was
it available in 7?)|||"Damien" wrote:

<snip>
> was it available in 7?

<snip
http://www.microsoft.com/sql/techin...dproperties.asp

According to this link, no (the page above also contains links to articles
on using extended properties, although I didn't follow them to see if they
were still good). It's been a while since we upgraded from 7 to 2K, but
that also seems sync with my memory.

Craigsql