Hello...
When i send a report in a subscription (in excel format - or any other) the
file is created and send as a file with the name of the report. How can i
change the name of this file ?
ThanksOne solution for you might be to create linked reports and to create
subscriptions on those.
However if you are looking to create something where you can configure the
name of the report when the report is executed - say append a date and time
to the report's name - then you are going to either have to create a Custom
Delivery Extension, or you are going to have to build your own component
that you schedule independantly from Reporting Services that extracts the
Report via the SOAP interface, names the output file and delivers it to your
user.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Penker" <Penker@.discussions.microsoft.com> wrote in message
news:89B65C23-7EDF-4B47-B259-7BB370FFB98C@.microsoft.com...
> Hello...
> When i send a report in a subscription (in excel format - or any other)
> the
> file is created and send as a file with the name of the report. How can i
> change the name of this file ?
> Thanks
Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Changing the command buffer size?
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!Originally posted by rmcat
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.|||Originally posted by kbk
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.
When I run the stored proc with exec output turned on, I get the following error...
"Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||Originally posted by rdjabarov
What do you put for the "query hints"? INDEX=xxxxx ?
Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...
bcp "select...." queryout <filename> <sql user id> <sql password> <server> <file format>
The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger...|||Originally posted by Brett Kaiser
You're playing with dynamic sql...right?
Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!
Success!!!!
You guys rock!
Thank you!!!
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!Originally posted by rmcat
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.|||Originally posted by kbk
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.
When I run the stored proc with exec output turned on, I get the following error...
"Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||Originally posted by rdjabarov
What do you put for the "query hints"? INDEX=xxxxx ?
Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...
bcp "select...." queryout <filename> <sql user id> <sql password> <server> <file format>
The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger...|||Originally posted by Brett Kaiser
You're playing with dynamic sql...right?
Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!
Success!!!!
You guys rock!
Thank you!!!
Monday, March 19, 2012
Changing SQL server IP in Excel file?
An Excel file uses an SQL Server table to which it connects using MS
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
--
Michael SalemHi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.com...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem|||I wrote:
Many thanks to imad koussa, who responded:
[vbcol=seagreen]
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am usi
ng
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it ask
s
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
.XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
--
Michael Salem
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
--
Michael SalemHi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.com...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem|||I wrote:
Many thanks to imad koussa, who responded:
[vbcol=seagreen]
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am usi
ng
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it ask
s
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
.XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
--
Michael Salem
Changing SQL server IP in Excel file?
An Excel file uses an SQL Server table to which it connects using MS
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
Michael Salem
Hi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.c om...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem
|||I wrote:
[vbcol=seagreen]
Many thanks to imad koussa, who responded:
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am using
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it asks
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
..XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
Michael Salem
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
Michael Salem
Hi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.c om...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem
|||I wrote:
[vbcol=seagreen]
Many thanks to imad koussa, who responded:
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am using
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it asks
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
..XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
Michael Salem
Sunday, February 12, 2012
Changing DataTypes in an Excel Data Source
Hello.
I'm importing some data from an excel file to sql server 2005.
I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.
If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:
Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".
Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".
If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].
Is there any way to get arround this?
Thanks in advance,
Hugo Oliveira
Hi,
By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.
|||Hello.
I've tried your tip and it's wotking well now.
Thanks,
Hugo Oliveira
|||Adding IMEX=1 to the connection string made the following Error message to appear.
"Coulnd not find Installable ISAM"
I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.
Did anyone had the same problem ?
|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||Hi Thiru_ and Hugo
I added IMEX=1 in connection string!
When i have a column with simple data, for example:
A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.
Some idea for this problem?
Thanks!
Andr Rentes
Brazil
PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com
|||Hi,
I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.
Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.
I don't find a way either to read a column in twice, once with one data type and once with another.
Does anybody no a way around this?
Many thanks,
Jan
Changing DataTypes in an Excel Data Source
Hello.
I'm importing some data from an excel file to sql server 2005.
I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.
If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:
Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".
Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".
If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].
Is there any way to get arround this?
Thanks in advance,
Hugo Oliveira
Hi,
By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.
|||Hello.
I've tried your tip and it's wotking well now.
Thanks,
Hugo Oliveira
|||Adding IMEX=1 to the connection string made the following Error message to appear.
"Coulnd not find Installable ISAM"
I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.
Did anyone had the same problem ?
|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||Hi Thiru_ and Hugo
I added IMEX=1 in connection string!
When i have a column with simple data, for example:
A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.
Some idea for this problem?
Thanks!
Andr Rentes
Brazil
PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com
|||Hi,
I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.
Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.
I don't find a way either to read a column in twice, once with one data type and once with another.
Does anybody no a way around this?
Many thanks,
Jan
Changing DataTypes in an Excel Data Source
Hello.
I'm importing some data from an excel file to sql server 2005.
I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.
If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:
Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".
Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".
If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].
Is there any way to get arround this?
Thanks in advance,
Hugo Oliveira
Hi,
By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.
|||Hello.
I've tried your tip and it's wotking well now.
Thanks,
Hugo Oliveira
|||Adding IMEX=1 to the connection string made the following Error message to appear.
"Coulnd not find Installable ISAM"
I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.
Did anyone had the same problem ?
|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||Hi Thiru_ and Hugo
I added IMEX=1 in connection string!
When i have a column with simple data, for example:
A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.
Some idea for this problem?
Thanks!
Andr Rentes
Brazil
PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com
|||Hi,
I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.
Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.
I don't find a way either to read a column in twice, once with one data type and once with another.
Does anybody no a way around this?
Many thanks,
Jan
Changing DataTypes in an Excel Data Source
Hello.
I'm importing some data from an excel file to sql server 2005.
I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.
If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:
Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".
Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".
If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].
Is there any way to get arround this?
Thanks in advance,
Hugo Oliveira
Hi,
By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.
|||Hello.
I've tried your tip and it's wotking well now.
Thanks,
Hugo Oliveira
|||Adding IMEX=1 to the connection string made the following Error message to appear.
"Coulnd not find Installable ISAM"
I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.
Did anyone had the same problem ?
|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||Hi Thiru_ and Hugo
I added IMEX=1 in connection string!
When i have a column with simple data, for example:
A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.
Some idea for this problem?
Thanks!
Andr Rentes
Brazil
PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com
|||Hi,
I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.
Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.
I don't find a way either to read a column in twice, once with one data type and once with another.
Does anybody no a way around this?
Many thanks,
Jan
Friday, February 10, 2012
Changing Cube source in a EXCEL pivot table
Hi
I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.
is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)
thanks
Steve
By the way - this is SQL Analysis Server 2000 and Excel 2003|||There are 2 techniques I know of to change the data source for an Excel pivot table.
The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.
The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:
thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"
|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||Thanks to all.
I checked out the XML and the VBA solutions - at least I have options now.
Changing Cube source in a EXCEL pivot table
Hi
I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.
is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)
thanks
Steve
By the way - this is SQL Analysis Server 2000 and Excel 2003|||There are 2 techniques I know of to change the data source for an Excel pivot table.
The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.
The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:
thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"
|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||Thanks to all.
I checked out the XML and the VBA solutions - at least I have options now.
Subscribe to:
Posts (Atom)