Error: Data source name not found and No default driver specified

Hello all,
I have an application which was written in VB6. I used
DSNless connection for the odbc connection to access sql 8.0. The
application works fine on one machine and when I try to run the appl.
on another machine I got this error. Any idea why' Here is my
connection string:
DSNName = "Driver={SQL Server};" & _
"Server=dummy;" & _
"Database=sql_data;" & _
"Uid=crystal"
The user id does not require a password.. Thank you.
SherryThis is a question for a VB group, not a SQL server group, but it sounds
like your DSNName is empty. Are you getting this value from an INI file,
the registry, or is it hardcoded in your app? You will most likely find
that this value is missing, wherever you define it.
Put in a message box that shows DSNName just prior to creating the
connection, and confirm that it is actually populated (I don't think it is).
<sunpalozzi@.gmail.com> wrote in message
news:1138811193.427214.282490@.g43g2000cwa.googlegroups.com...
> Hello all,
> I have an application which was written in VB6. I used
> DSNless connection for the odbc connection to access sql 8.0. The
> application works fine on one machine and when I try to run the appl.
> on another machine I got this error. Any idea why' Here is my
> connection string:
> DSNName = "Driver={SQL Server};" & _
> "Server=dummy;" & _
> "Database=sql_data;" & _
> "Uid=crystal"
> The user id does not require a password.. Thank you.
> Sherry
>|||Maybe the other machine doesn't have MDAC installed?
http://www.aspfaq.com/2057
Once you have the most recent version installed, an OLEDB connection string
will be better in most cases:
ConnectionString = _
"Provider=SQLOLEDB.1; " & _
"Data Source=dummy; " & _
"Initial Catalog=sql_data; " & _
"User ID=crystal; " & _
"Password="
<sunpalozzi@.gmail.com> wrote in message
news:1138811193.427214.282490@.g43g2000cwa.googlegroups.com...
> Hello all,
> I have an application which was written in VB6. I used
> DSNless connection for the odbc connection to access sql 8.0. The
> application works fine on one machine and when I try to run the appl.
> on another machine I got this error. Any idea why' Here is my
> connection string:
> DSNName = "Driver={SQL Server};" & _
> "Server=dummy;" & _
> "Database=sql_data;" & _
> "Uid=crystal"
> The user id does not require a password.. Thank you.
> Sherry
>|||thank you for all replies.. I did post in the vb group as well.. The
wire part is the same application is work on one pc but not the
another... I will try to find out if there a MDAC install on the
problem pc.. thanks.
Sherry

ERROR: "Subquery returned more than 1 value."

I've got a big procedure written by a contractor. I'm trying to
execute it (against a test db until I get it worked out) and there is
one section of it that fails. I've isolated the section and run it in
Query Analyzer and it still fails. Here is the SQL:
DELETE FROM tblBedOccupancy WHERE (
ContactID IN (
SELECT ContactID FROM tblPeople WHERE Community IN (
SELECT DISTINCT BuildingID FROM lnkCompaniesBuildings WHERE CompanyID
<> 61
)
)
)
The error returned is:
Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
Line 18
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.
If I change DELETE to SELECT *, it runs fine. If I run the subqueries
they run fine. How can I get this to work?
Thanks,
Josh D> The error returned is:
> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated.
Look closely at the error message. Did you notice the name
"tg_DeleteOccupancyRecord". What does this refer to? Looks like a
trigger - and a poorly written one to boot. The problem is the code in the
trigger.|||I'd agree with Scott, sounds like you have trigger written by someone who
didn't realize that triggers fire once per statement, not once per row.

> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated.|||> I'd agree with Scott, sounds like you have trigger written by someone who
> didn't realize that triggers fire once per statement, not once per row.
...and if that was the contractor, this might constitute breach of contract
.
:)
ML|||> ...and if that was the contractor, this might constitute breach of
> contract.
> :)
Well, if the contractor's side of the agreement doesn't explicitly state, "I
know what I'm doing"...|||> Well, if the contractor's side of the agreement doesn't explicitly state, "Id">
> know what I'm doing"...
In continental law the "I know what I'm doing" part is presumed (praesumptio
iuris), and the contractor can only limit his own liability by stating the
opposite.
ML

Error while executing a a query string using EXEC statement

Hi,

I have written a stored proc to bulk insert the data from a data file.

I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query

Code Snippet

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category

SELECT MSISDN, @.LastUpdate FROM OPENROWSET( BULK '\\remotemachine\datafile.txt',

FORMATFILE = '\\remotemachine\FormatFile.fmt',

FIRSTROW = 2) AS a

To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below

Code Snippet

@.Category - Will be passed as a parameter to the stored proc

DECLARE @.vsBulkSQL VARCHAR(MAX)

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @.vsBulkSQL ='INSERT INTO '+ @.Category + ' SELECT MSISDN, ''' + @.LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\\remotemachine\datafile.txt'+ ''''+ ' ,' +

+ ' FORMATFILE ' + '=' + ''''+ '\\remotemachine\FormatFile.fmt'+ ''''+ ',' +

' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @.vsBulkSQL - This prints the folliwing statement

INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\DataFile.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a

Exec @.vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\Second.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.

Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

Does it work if you alias the @.LastUpdate value?

|||

I suspect you need to execute the query with

EXEC (@.vsBulkSQL)

You have left out the parentheses, and when you do that, EXEC expects the variable to hold a procedure name, not a query string.

Steve Kass

Drew University

www.stevekass.com

|||

Steve,

Good catch. Its working now if i have the string etween ( and ).

Thanks a lot

~Mohan