"amish" wrote:

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

"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
>
>

"Ade" wrote:

> 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
> >
> >