one matrix above another doesn't export properly to Excel

A newb question: I've got a report with a table leftmost in the body,
with a matrix immediately to the right of the table. Immediately
below the table are 2 textboxes displaying totals from the table.
Immediately below the first matrix is another matrix displaying totals
from the first matrix:
Table Matrix1
Textboxes Matrix2
All is well in preview, HTML view, and PDF export.
After Excel export, the sheet is arranged thus:
Table Matrix1
Matrix2 Textboxes
I've tried putting the 2 matrices in a rectangle, and even inside
nested rectangles, with no effect on the incorrect Excel layout.
The report is designed this way because the users want empty space
between the detail data (in the Table and Matrix1) and the totals.
The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
expand in step with Matrix1.
I think. Help? RDL available if anybody wants it.
Thanks
MatrixLoverI am running into similar problem. I have one matrix over another and report
logic hide / unhide matrix. Every work like charm in reports, export to pdf,
but having trouble in excel export. When export to excel, the hidden matrix
also appears just below the active matrix. Any help appreciated.
"MatrixLover" wrote:
> A newb question: I've got a report with a table leftmost in the body,
> with a matrix immediately to the right of the table. Immediately
> below the table are 2 textboxes displaying totals from the table.
> Immediately below the first matrix is another matrix displaying totals
> from the first matrix:
> Table Matrix1
> Textboxes Matrix2
> All is well in preview, HTML view, and PDF export.
> After Excel export, the sheet is arranged thus:
> Table Matrix1
> Matrix2 Textboxes
> I've tried putting the 2 matrices in a rectangle, and even inside
> nested rectangles, with no effect on the incorrect Excel layout.
> The report is designed this way because the users want empty space
> between the detail data (in the Table and Matrix1) and the totals.
> The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
> expand in step with Matrix1.
> I think. Help? RDL available if anybody wants it.
> Thanks
> MatrixLover
>

one control loop with Substring to correct first normal column violation

given a table from an excel spreadsheet that contains a column that violates
first normal ;
Area Code TimeZone
787/939 4
212/646/718/917 5
I would like to create one procedure that loops through n possible values of
the area codes and creates a separate row for each value.
The Substring(s) below would have to start from positions
1,5,9,13,17,21,25,29 to capture the 3 character codes.
Right now I am manually creating each table with separate Substring
select/inserts with a union of all the tables and a select distinct.
The 65,000 rows of the original table end up deduped into about 245 rows of
distinct area codes per time zones.
Here are the first two sets of queries (there are 7 total) followed by the
consolidation with UNION ALL
-- create row with only first area code of '/' delimited values
CREATE TABLE first_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO first_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
-- create row with only second area code of '/' delimited values
CREATE TABLE second_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO second_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
insert into combined_areacode
SELECT * FROM first_areacode
UNION ALL
SELECT * FROM second_areacode
UNION ALL
SELECT * FROM third_areacode
UNION ALL
etc.
.
Thank you for ideas to help automate this,
-Greghazz (hazz@.sonic.net) writes:
> I would like to create one procedure that loops through n possible
> values of the area codes and creates a separate row for each value. The
> Substring(s) below would have to start from positions
> 1,5,9,13,17,21,25,29 to capture the 3 character codes. Right now I am
> manually creating each table with separate Substring select/inserts with
> a union of all the tables and a select distinct. The 65,000 rows of the
> original table end up deduped into about 245 rows of distinct area codes
> per time zones.
> Here are the first two sets of queries (there are 7 total) followed by the
> consolidation with UNION ALL
> -- create row with only first area code of '/' delimited values
> CREATE TABLE first_areacode
> (AreaCode varchar(50),
> TimeZone varchar(50))
> INSERT INTO first_areacode
> (AreaCode, TimeZone)
> select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
> from AreaCodeTimeZone
As long as you can rely on that the area codes are exact three
characters, no extra spaces etc, this could work:
SELECT substring(AreaCode, n, 3), TimeZone
FROM tbl
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION ALL
SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
..) AS n
WHERE len(AreaCode) <= n
By the way, which version of SQL Server are you using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL 2005.
Thank you Erland !
-Greg
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns977F34D4AFD2Yazorman@.127.0.0.1...
> hazz (hazz@.sonic.net) writes:
> As long as you can rely on that the area codes are exact three
> characters, no extra spaces etc, this could work:
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
> By the way, which version of SQL Server are you using?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I used this, the AreaCode column was blank although the TimeZone column
was populated.
I just reposted this with working sql that I would still like to condense
into an algorithm. Thanks, -Greg

> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n

On export to excel

I have list within an list and we have section 1 thru 7. We display the list based the parameters seleted from report for some result section might start from 3 thru 6 . In such case when try to export to excel i get huge gaps as i am missing section 1 thru 2 in the report.

How do we avoid those spaces when exporting to excel,

What I could understand from your description is that each section is a list, and all 7 lists are within another list. You have set the visibility of these lists based on some parameter value.

If that is the case, try putting each list inside a rectangle and set the visibility expression for the rectangles, instead of the lists. That should resolve your problem.

-Aayush

On excel export

We have couple of group in table on one group when we try to export to excel
it supress the data visible to the user.
Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
as the size of it is as such.
What property can we provide to dynamically increase the row height when we
export.On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.
It sounds like you are dealing with a merged cells issue in Excel once
the report is exported. The best ways to avoid this is to do the
following:
- Make sure that, if you have multiple tables/controls/matrices above
each other, that they are all touching each other.
- Make sure that the last table/etc control on the report (in Layout
view) is touching the bottom of the report border with no extra space
available.
- Make sure that all controls touch the right-side border of the
report and if you have smaller width controls (in particular
textboxes) in the report, make sure to extend the textbox controls out
to the full width of the report and same width as the widest control
on the report (normally, the table/matrix controls).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||It didnt slove the issue,
I have list within an list and we have section 1 thru 7. We display the list
based the parameters seleted from report for some result section might start
from 3 thru 6 . In such case when try to export to excel i get huge gaps as i
am missing section 1 thru 2 in the report.
How do we avoid those spaces when exporting to excel,
"EMartinez" wrote:
> On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> > We have couple of group in table on one group when we try to export to excel
> > it supress the data visible to the user.
> >
> > Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> > as the size of it is as such.
> > What property can we provide to dynamically increase the row height when we
> > export.
>
> It sounds like you are dealing with a merged cells issue in Excel once
> the report is exported. The best ways to avoid this is to do the
> following:
> - Make sure that, if you have multiple tables/controls/matrices above
> each other, that they are all touching each other.
> - Make sure that the last table/etc control on the report (in Layout
> view) is touching the bottom of the report border with no extra space
> available.
> - Make sure that all controls touch the right-side border of the
> report and if you have smaller width controls (in particular
> textboxes) in the report, make sure to extend the textbox controls out
> to the full width of the report and same width as the widest control
> on the report (normally, the table/matrix controls).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I am getting the same issue, I have a mixture of TextBoxes and images in the
row and if I take the image cells away the row heights amend themselves
automatically but when the images are in the row they dont resize.
Any ideas? Any Help? Anything?
Anyone
"NAVIN.D" wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.|||I am having the same problem on exporting a report containing matrixes, and I
don't know what to try next. This report needs to go into production very
soon.
I inserted a rectangle containing textboxes for column headers in the left
corner of the matrix as instructed. When the matrix is exported to Excel,
there are 2 separate cells where ONE extra pixel, .08in, is inserted between
2 cells, causing a merged cell. There is no apparent reason. I have checked
the alignment and width of the columns and textboxes. I tried it with and
without borders on the textboxes. Nothing is working. When I exported the
matrix after I removed the rectangle and textboxes from the left corner the
export worked correctly. Are there any solutions?

OmitDocumentMap

I have a report on reporting server that I need to email as excel file weekly.
As default it comes with document map as the first excel tab when exported
which I need to stop from happening.
I have found on the internet that I can use this
<DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
but I dont know where to put it, I tried putting it in different places in
the xml but no good it keeps coming back with errors such as
Deserialization failed: The element 'Report' in namespace
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
has invalid child element 'DeviceInfo'
So please can anyone tell me exactly where does the device info need to go?bump
"jl45" wrote:
> I have a report on reporting server that I need to email as excel file weekly.
> As default it comes with document map as the first excel tab when exported
> which I need to stop from happening.
> I have found on the internet that I can use this
> <DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
> but I dont know where to put it, I tried putting it in different places in
> the xml but no good it keeps coming back with errors such as
> Deserialization failed: The element 'Report' in namespace
> 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
> has invalid child element 'DeviceInfo'
> So please can anyone tell me exactly where does the device info need to go?
>

OmitDocumentMap

I have a report on reporting server that I need to email as excel file weekly.
As default it comes with document map as the first excel tab when exported
which I need to stop from happening.
I have found on the internet that I can use this
<DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
but I dont know where to put it, I tried putting it in different places in
the xml but no good it keeps coming back with errors such as
Deserialization failed: The element 'Report' in namespace
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
has invalid child element 'DeviceInfo'
So please can anyone tell me exactly where does the device info need to go?HTML Viewer Commands (rc:)
You use HTML Viewer commands to tell SSRS 2005 how to render the report. You
can use the commands to control how the viewer appears to the user, as well
as to control certain aspects of how the report appears in the viewer.
-- Shows or hides the report document map.
rc:DocMap=true
Reeves
"jl45" wrote:
> I have a report on reporting server that I need to email as excel file weekly.
> As default it comes with document map as the first excel tab when exported
> which I need to stop from happening.
> I have found on the internet that I can use this
> <DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
> but I dont know where to put it, I tried putting it in different places in
> the xml but no good it keeps coming back with errors such as
> Deserialization failed: The element 'Report' in namespace
> 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
> has invalid child element 'DeviceInfo'
> So please can anyone tell me exactly where does the device info need to go?
>

Oleddb dropdown list is empty in Excel

Hello,

I have an issue with connecting Excel to an analysis Services 2005 database. The computer is running Windows XP Sp2 and Office XP. I installed the oledb driver 9.0 but when I tried to connect with the pivot table and I want to create the connection, the dropdown list of the data provider is empty. I tried on another computer with Excel XP and the system is working fine.
Is there somebody that could help?

Thank you

It sounds like it might be a connection/networking issue? On the computer, try making an ODBC Data Source. You can do this by choosing Start->Control Panel->Administrative Tools->Data Sources (ODBC).

From here, on the User DSN tab, press the Add... button. On the list of providers, you should see "SQL Native Client" or "SQL Server". Choose one of these. On the next dialog, you should see 3 fields, Name, Description, and Server. Use the drop down list of "Server" to see if you can find the server that you're looking for. If you can, then it's something else. If you can't, then talk to your system administrator about troubleshooting why that server can't be seen from your computer. Either way, you can cancel and close these dialogs without making any changes to your computer. This should only test the visibility of the server to your computer.

Hope this helps. Please follow up if this doesn't help you.

|||

Thanks for the help but it doesn't work. If I try to create an ODBC connection the system is working fine. It seems to me that the problem is related only to the OleDb component for Analysis Services. I am going to try to uninstall and install back office to see it this will fix something.

Thanks again!

OleDB Connection to Excel

Hi,

My application connects to excel files.
I am using this code:

dc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=YES;\"");

It works fine on Excel 2000 on all computers. Also it works on some computers with Excel 2002/XP, but on some computers with Excel 2002/XP it doesn't work.

How can I solve it?

Thank's
AlexeiHi,
I used above code sample for my program. but i did not worked.can u help me. I want to do ,take excel information to one record set or data set.I can't understand how to do it.I send u my connection coding.please check it send me wht is the correct syntax or correct code and tell me wht is the componts want. this program using the .net
following has my codes:

MyConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=D:\Oven Sheet (Track)\Data For Symix_23082005-Tracks.xls;Extended Properties=\Excel 8.0")

|||Are you sure you have the right version of Jet on the machine? Check this by right clicking on the desktop and create a new text file, call it demo.udl. This is a datalink file. If you double click on the file it should show you a list of OLE DB Providers, check that Jet 4.0 is installed.

If it is installed then try changing \Excel 8.0 to plain Excel 8.0

OLE DB Source & Excel Destination

Hi,

My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.

Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.

SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.

xlsQuery

CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO

sqlQuery

Exec Report.MyReport 1

Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.

Please Help

Thanks

Shafiq

If the metadata of the sources changes then you cannot use the same data-flow task. Its as simple (or as difficult) as that.

-Jamie

|||

Is it possible to add a conditional splitter in my For Each Loop container to go to different Data-Flow Tasks based on package variable?

Or is there any thing which can refresh the meta data during runtime?

Thanks

Shafiq

|||

shafiqm wrote:

Is it possible to add a conditional splitter in my For Each Loop container to go to different Data-Flow Tasks based on package variable?

Yes, except they're not called conditional splitters. The correct nomenclature is conditional precedence constraints. Loads of good info here: http://www.sqlis.com/default.aspx?306

shafiqm wrote:

Or is there any thing which can refresh the meta data during runtime?

No! Well, actually there is a horrible workaround which involves editing a .dtsx package from another .dtsx package. I have never done it and I certainly never intend to - steer well clear of it.

Using precedence constraints to decide which data-flow to execute is absolutely the right way to go.

HTH

-Jamie

|||

I am going to use the conditional precedence constraints. The next question is do I have to use different OLE DB source / Excel File connection Manager for each data-flow task or they can be changed dynamically.

I was trying to only use one OLE DB and I got error message VS_NEEDSNEWMETADATA

Thanks

|||

You can use the same connection manager across different data-flows and change it dynamically.

You can not use an OLE DB Source component in different data-flows.

-Jamie

|||

It looks like I can't use same Excel File connection Manager as during the design time, If I change the file the mapping of data-flow task then previously defined Data-flow tasks goes wrong and I get the message

Excel desitnation needs VS_NEEDSNEWMETADATA

Note: Each excel file will have different columns depending upon the report

I think same excel file connection manager only work if all the files have the same number of columns

It looks like I am doing an automatic job manually.

Thanks

|||

OK, here's the deal. Once you change the connection manager to point to a file with differrent metadata then of course the data-flow tasks will fail to validate because they are expecting one thing and seeing another (that's what's causing the message you are getting).

Get around this by setting DelayValidation=TRUE on all the data-flows. THis means that they won't get validated until they are executed by which time your connection manager connection string should be set up correctly.

-Jamie

|||

Thanks very much for your prompt response and it help me a lot. One last thing. As I am deleting the excel file and re-creating every time the package runs, Is there way to format the excel file using SSIS

e.g.

Format a column to show 2 decimal places or format as currency

Do any subtotal or run a macro?

I know I did this using ActiveXScript task in SQL 2000, Is there any other way to do this?

Thanks

|||

If Excel has an API (which I assume it does) then I assume you can manipulate it via that API. You would need to ask someone that knows about Excel.

-Jamie

OLE DB error

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Its strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?
Any help would be appreciated.
P.S. I'm running SQL Server 2000
SChi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here
?
"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows a
nd
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine canno
t
> open the file ''. It is already opened exclusively by another user, or yo
u
> need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
> It?|s strange, I think, that there is no file in the error message... B
TW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
> Any help would be appreciated.
>
> P.S. I'm running SQL Server 2000
>
> SC
>
>|||here. thanks.
CREATE PROCEDURE convert_data
@.userid varchar(8)
as
BEGIN TRANSACTION UpdateAll
DELETE table1
INSERT INTO table1
SELECT id, name, cat, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')
DELETE table2
INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')
DELETE table3
INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')
DELETE table4
INSERT INTO table4
SELECT code, num, description, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')
COMMIT TRANSACTION UpdateAll
GO
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@.microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
> "Squirrel" wrote:
>

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..