Prev: The file on device 'tape' is not a valid Microsoft Tape Format backup set.
Next: SQLSERVERAGENT - Alert Engine - 322
From: Soren on 19 Jan 2006 04:29
Hi,

I am trying to connect to SQL Server 2005 via Excel/ODBC. (ODBC Test IS
successfull) and get the following error after entering Login ID and Password:

Connection failed:
SQLState: '37000'
SQL Server Error: 2571
......does not have permission to run DBCC TRACEON

Security setup on SQL Server seems to be fine (I can connect to SQL Server
from Reporting Services on same Login/Password).

Any idears?

Best regards,

Soren
From: amish on 19 Jan 2006 05:26

To run DBCC Traceon you requires membership in the sysadmin fixed
server role.


Regards
Amish

From: Soren on 19 Jan 2006 07:59
That did it! Thanks !!!

"amish" wrote:

>
> To run DBCC Traceon you requires membership in the sysadmin fixed
> server role.
>
>
> Regards
> Amish
>
>
From: preddy on 31 Jan 2006 13:56
I had the same problem, but there are multiple users who use that
spreadsheet using Windows authentication. I did not want any of them to
be sysadmins. They only had SLECT permissions to certain tables. In my
case, deleting the Application name (from the SQL Server Login dialog)
fixed it. You need to click the Options button on the dialog to see
this. The application name seems to be an issue if it has certain
characters (like ® in Microsoft® Query). Depending on the MS Office
version, the default value may be Microsoft® Query/MS Office XP/??.

You can also programmatically change the connection string (and even
the command text if necessary). Here is a sample.

'ChangeConnection
Sub ChangeConnection()

Dim sh As Worksheet
Dim qt As QueryTable

Dim sConnection As String

For Each sh In ActiveWorkbook.Sheets
For Each qt In sh.QueryTables

'Show current connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
& vbCr & qt.Connection)
'Show current query
MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
& qt.CommandText)

'Change Connection
qt.Connection = "ODBC;DRIVER=SQL
Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"

'Change Qry text (the owner for instance)
qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
"DB.Me")

qt.SavePassword = False

'Show new connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
qt.Connection)
'Show new query
MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
qt.CommandText)

Next qt
Next sh

End Sub

From: Ade on 1 Feb 2006 05:16
I have the exact same problem using Excel 97/MS-Query8. I tried your
suggestion, it did not work. I am still getting the error.
--
Ade


"preddy" wrote:

> I had the same problem, but there are multiple users who use that
> spreadsheet using Windows authentication. I did not want any of them to
> be sysadmins. They only had SLECT permissions to certain tables. In my
> case, deleting the Application name (from the SQL Server Login dialog)
> fixed it. You need to click the Options button on the dialog to see
> this. The application name seems to be an issue if it has certain
> characters (like ® in Microsoft® Query). Depending on the MS Office
> version, the default value may be Microsoft® Query/MS Office XP/??.
>
> You can also programmatically change the connection string (and even
> the command text if necessary). Here is a sample.
>
> 'ChangeConnection
> Sub ChangeConnection()
>
> Dim sh As Worksheet
> Dim qt As QueryTable
>
> Dim sConnection As String
>
> For Each sh In ActiveWorkbook.Sheets
> For Each qt In sh.QueryTables
>
> 'Show current connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
> & vbCr & qt.Connection)
> 'Show current query
> MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
> & qt.CommandText)
>
> 'Change Connection
> qt.Connection = "ODBC;DRIVER=SQL
> Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"
>
> 'Change Qry text (the owner for instance)
> qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
> "DB.Me")
>
> qt.SavePassword = False
>
> 'Show new connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
> qt.Connection)
> 'Show new query
> MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
> qt.CommandText)
>
> Next qt
> Next sh
>
> End Sub
>
>
 |  Next  |  Last
Pages: 1 2
Prev: The file on device 'tape' is not a valid Microsoft Tape Format backup set.
Next: SQLSERVERAGENT - Alert Engine - 322