Re: Syntax

Some time during the development phase I received some errors regarding the
use of the Char(39). I am not getting those now but I am still confused
about why the double quotes are used at all in SSIS when working with the SQL
Server connections. They are understandable in the context of the Access
database strings but in the case of Access, the connection string is actually
just the filename and path and if the mapping from the SQL Server portion is
accurate and is correctly formed, the information should flow smoothly from
one to the other.

So, you mention to be careful regarding the "'s (single quotes) which, is
actually my question. I am unable to determine whether to double quote the
entire line of the sql query or procedure (either will work fine), and
whether the variable is @TargeDB or @[User::TargetDB] or whether the
Replace(@[User::TargetDB],"mytext","myreplacement") is double or single
quoted or left without double and single quotes. The syntax is
extraordinarily unclear. The errors seem to be ambiguous.

For example:
The connection string format is not valid. It must consist of one or more
components of the form X=Y, separated by semicolons. This error occurs when a
connection string with zero components is set on database connection manager.

Is the above error due to the inability to acquire the connection to the
Access database because I have syntax errors in my SQL Server information and
thus, being non-well-formed, the string format causes the connection manager
to fail? I still do not see why the expression @[User::TargetDB] - the for
each variable of my container, is being quoted and then there is the
complaint regarding the ConnectionString not being written to the property -
wouldn't that be the "VALUE" portion of the @[User::TargetDB] loop?

I will try this all again later - I gave up again this week and ran through
the 250 database, with the 16 clicks required to run the wizard once for each
of the 250 plus Access databases by hand. The goal is to give my arm a rest.
It takes about 6 hours to run through it. It would seem a no-brainer that
if I can run the entire process in a wizard environment 250 times without
incident that the wizard should easily automate the process.

Thanks much for posting though. Just hearing from someone else is helpful.
--
Regards,
Jamie


"jhofmeyr@xxxxxxxxxxxxxx" wrote:

Hi Jamie,

My understanding is that you have a SQL Server database with a table
of values related to MDB files, and that you are having trouble
getting a stored proc to run against this (SQL Server) database to
retrieve some details. Please correct me if I'm wrong.

Based on the error messages you've posted, it looks as if the
Expression is not being evaluated correctly. There are a number of
pointers:
1) ... " the expression "EXEC CREATECOUNTRYTABLE
@[User::TagetDBNames]" failed. " - This indicates that the variable
name (not the variable value) is being substituted into the
expression. Check that you have the "'s in the right place.
2) ... " Description: "Incorrect syntax near '='.". " - This indicates
a syntax error. Look again at the expression you posted:
<quote>
"select left(abc,110)abc,left(cityname,90)cityname from
fips.dbo.encryptedcities where
cc1="+REPLACE( UPPER(RIGHT(@[User::TargetDB],6)),".MDB","")
</quote>
There should be a ' after the "=" in that expression, and another one
in the REPLACE statement (inside the ""). So:
"select left(abc,110)abc,left(cityname,90)cityname from
fips.dbo.encryptedcities where
cc1='"+REPLACE(UPPER(RIGHT(@[User::TargetDB],6)),".MDB","'")

The error messages in your last post look like they're related to the
Access database connection string, and not the Expression issue you're
having with the stored proc execution. Maybe once we have the sproc
returning correctly we can address the Access connection manager
settings.

Good luck!
J

.

Relevant Pages

  • Re: [SOLVED] Cannot display provider-specific login prompt
    ... scenario where the program wouldn't have to know about the database, ... ConnectionStringBuilder up to a PropertyGrid for the end user to populate), ... details of the connection, for the sake of the program being able to connect ... information about the connection string that I will need. ...
    (microsoft.public.dotnet.framework.adonet)
  • Complicated Connection Problems bewteen ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.access.adp.sqlserver)
  • Complicated Connection Problem between ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.sqlserver.connect)
  • Re: trying to automate a process we perform manually
    ... loan files) that's about 220mb large. ... I then rename it to a text file and import it into an Access database. ... connection to your data source. ...
    (microsoft.public.sqlserver.dts)
  • Re: Problems in loading data from an access database into an array
    ... Suppose your database has a table called which has columns called ... Dim oleCSB As New OleDbConnectionStringBuilder ... 'associate the connection string just built with the ole connection ...
    (microsoft.public.dotnet.framework.adonet)
Loading