Error on query execution in VBA Excel using ADODB on SQL Server

We're trying to upsize from MS Jet (Access) db to SQL Server and am having a
major problem. Because we've not seen this error with the MS Jet db, I
suspect it's related to the user configuration in SQL Server.
The servername is "sqlbox" and the database name is "TCR". We use WinNT
authentication on the db and have a user profile for all our domain users.
The connection string executes, but the query does not. If the VBA Project
is unlocked, an error stating "Application Error...Unspecified Error" occurs
at the line noted below (--> ). When the VBA Project is locked (normally),
the error states "Invalid object name" and then lists the database table (in
this example, "Version").
Here's the pertinent code:
strConn = "Provider=SQLOLEDB;Data Source=sqlbox;" & _
"Persist Security Info=False;Initial Catalog=TCR;" & _
"Integrated Security=SSPI"
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = strConn
adoConn.Open
strSQL = "SELECT * from Version WHERE Version.Filename = '" & strFname & "';
"
' open a recordset from Version table
Set adoRec = New ADODB.Recordset
adoRec.CursorLocation = adUseClient
--> adoRec.Open strSQL, adoConn <-- Error occurs here
I can run the application without problems; perhaps it is because I am the
dbowner (which implicitly grants me full access to the tables). However, I
have granted full access to the Domain User profile without success by any
other user.
Unless I find a way to solve this, there will be no migration! Thanks in
advance!Hi Glenn
You posted in the wrong place. There is a dedicated new group forACCESS/SQL
SERVER/ADP Integration. You will get better answers there as they are
specialized in that.
Regards
R.D
"Glenn Ray" wrote:

> We're trying to upsize from MS Jet (Access) db to SQL Server and am having
a
> major problem. Because we've not seen this error with the MS Jet db, I
> suspect it's related to the user configuration in SQL Server.
> The servername is "sqlbox" and the database name is "TCR". We use WinNT
> authentication on the db and have a user profile for all our domain users.
> The connection string executes, but the query does not. If the VBA Projec
t
> is unlocked, an error stating "Application Error...Unspecified Error" occu
rs
> at the line noted below (--> ). When the VBA Project is locked (normally)
,
> the error states "Invalid object name" and then lists the database table (
in
> this example, "Version").
> Here's the pertinent code:
> strConn = "Provider=SQLOLEDB;Data Source=sqlbox;" & _
> "Persist Security Info=False;Initial Catalog=TCR;" & _
> "Integrated Security=SSPI"
> Set adoConn = New ADODB.Connection
> adoConn.ConnectionString = strConn
> adoConn.Open
> strSQL = "SELECT * from Version WHERE Version.Filename = '" & strFname & "
';"
> ' open a recordset from Version table
> Set adoRec = New ADODB.Recordset
> adoRec.CursorLocation = adUseClient
> --> adoRec.Open strSQL, adoConn <-- Error occurs here
> I can run the application without problems; perhaps it is because I am the
> dbowner (which implicitly grants me full access to the tables). However,
I
> have granted full access to the Domain User profile without success by any
> other user.
> Unless I find a way to solve this, there will be no migration! Thanks in
> advance!

Error Msg

String or binary data would be truncated.
The statement has been terminated.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

Source Error:

Line 32: Line 33: Dim add As New SqlCommand(change, conn2)Line 34: add.ExecuteNonQuery()Line 35: conn2.Close()Line 36: Response.Redirect("ThankUReg.aspx")

hi there,

I have this error msg when i try to execute my register page..

What do they mean by string or binary data will be truncated? i've set my database value type to varchar(MAX).

still this error appears.. Looking forward for ur reply.

Yini

well it Does in fact mean that you are trying to insert data into a column that is not big enough for the data. Without knowing your db structure or seeing the SQL your actually using to insert i can't offer much more help. debug the process and maybe copy and paste your values into excel or something so you can run a LEN Function on them and see which one is too big.

hth,
mcm

|||

yinibabe:

i've set my database value type to varchar(MAX).

Have you set the data type to be varchar (max) for every string type column ? My advice is to again check all the columns for their data types, I think you are missing one column which may not allow the amount of data you are trying to insert for that column.

Hope this will help.

Error messages logging and copying.

Hi:

How can I copy the error messages when I execute a SSIS package?. The Progress tab or the Execution results tab both dont have the means to copy the results. I would like the errors to be output to a text file under a directory on my drive system (some thing like C:\SSIS\ExecResults.txt). Is this possible?. If so how do I configure my package to output the package execution results to a text file?.

Any help/suggestions/comments highly appreciated.

Thanks

AK

Right click the package control flow and select Logging. Create and configure text log provider to output events that you want to the text file.

Books Online has detailed instructions on this. By the way, we just release July update to BOL, it is worth download time:
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en