How to Change Precision and Scale in MS SQL Server 2000

Hello,

My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:

1. The date field has time in addition to the date. I don't want the
time in this field.

2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.

Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.

Any suggestions are welcome.

JoeHowdy,

Usually you can query a datetime column to extract just the date, so dont worry too much about that.

The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.

Cheers,

SG
( PS - theres nothing quite like a V8 Holden ute.....)|||Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?

Thanks again. I am still new to working with Query Analyzer.

Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.

Can someone assist?

Thanks again.

Joe|||Howdy,

Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.

Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)

Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34

If you xxtract using time the following code -

select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO

This gives -

17:23:34 ( but in varchar format).

Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.

Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.

Cheers,

SG.

how to change point label fillcolor

I have data and graphs that I have in Excel and am porting to sql 2005 in Excel I have option to change plot area back ground color and the label background color to white. Why isn't there a fillcolor selection capability in the EditChartValue\PointLabel\labelStyle dialog box?

is there a workaround with the format code textbox?

Thanks

There is a Style properties dialog for the plot area on the General tab of the chart properties dialog where you can change the fill color.

Currently, fill colors can not be set for data point labels.

Ian

How to change name of Excel attachment in data-driven subscription

Hello,
I have a data-driven subscription that sends out the report as an Excel
file. The file of the Excel spreadsheet is the name of the report. However,
a customer wants the name of the Excel file to be personalized. And I don't
see how to accomplish that and still use the subscription mechanism of
Reporting Services. If possible, I want to avoid writing my own subscription
routines.
Appreciates any hints on how to do this.
Best regards,
Vemund HagaI also would like to know how because I have to send some different copies of
the same template with different datas to the same customer. He will receive
some different copies of the Excel report with the same name.
Thank you,
Sophy G.
"Vemund Haga" wrote:
> Hello,
> I have a data-driven subscription that sends out the report as an Excel
> file. The file of the Excel spreadsheet is the name of the report. However,
> a customer wants the name of the Excel file to be personalized. And I don't
> see how to accomplish that and still use the subscription mechanism of
> Reporting Services. If possible, I want to avoid writing my own subscription
> routines.
> Appreciates any hints on how to do this.
> Best regards,
> Vemund Haga
>
>

How to change default format

By default reports are in HTML format.Can we change the default format to EXCEL

There is no way to change the default rendering format.

Incidently, how would you see this working? Would you expect report links from report manager to download as Excel files? Accessing reports via URL you can specify the rendering format. So why do you need to do this?

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

how to call Excel file in SSRS

Hi ,

I have one report in SSRS , in my report I have one table that display severel links,

and when I click in one line in the table I want to display the Excel file linked to this line.

exemmple :

Line1: Report Balance

Line2: Report Statistic

.....

and When i click on the line1 it will call one Excel file called RPTBAL.xls

Thanks for your help

Where are these Excel files? On a web server? If so, you can add a hyperlink expression that points to the Excel file for each row.

You can't add them on an entire row but you could add them on all the textboxes in the row. You can also make the hyperlink dynamic based on the content of the textbox. That way each row could point to a different file.

|||

Thanks for your eplay , is correct and I tested it and it works fine,

but each time it gives meesage when I call this Excel file , so please you have solution about this , to don't ask me in each call ,

I have the same problem in sharepoint when I call Excel file it dispay message before to open the file .

Regards

How to browse cube in Excel?

Hi, all here,

Could anyone tell me how can I browse cube data in Excel?

Thanks a lot in advance for any guidance and help.

There are two options I know of.

One is Microsofts Free giveaway Data Access Tool. Only works with Excel 2002 onwards I think, get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en home page for product is at http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx

Another is XLCubed www.xlcubed.com which is similar but far more powerful and friendly. This works with Excel 2000 SP2 onwards

Most of the Big front end tools like Cognos and proclarity have Excel plugins but they tend to be fairly expensive. XL3 is about ￡300 a seat and they do 30 day trial versions. We had four or five trials in a row before we settled on it as a final solution.

The MS tool is useable but not for anything too complicated. Both are really easy to pick up if your Excel users know anything about pivot tables.

regards

Steve

|||Hi, Steve, thanks a lot.|||

Hi,

I have downloaded and installed the add-in. When I try to connect to the database I get an error with Initialization of the data source failed.

Can one one help me on this?

cheers

Josh

How to browse cube in Excel?

Hi, all here,

Could anyone tell me how can I browse cube data in Excel?

Thanks a lot in advance for any guidance and help.

There are two options I know of.

One is Microsofts Free giveaway Data Access Tool. Only works with Excel 2002 onwards I think, get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en home page for product is at http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx

Another is XLCubed www.xlcubed.com which is similar but far more powerful and friendly. This works with Excel 2000 SP2 onwards

Most of the Big front end tools like Cognos and proclarity have Excel plugins but they tend to be fairly expensive. XL3 is about ￡300 a seat and they do 30 day trial versions. We had four or five trials in a row before we settled on it as a final solution.

The MS tool is useable but not for anything too complicated. Both are really easy to pick up if your Excel users know anything about pivot tables.

regards

Steve

|||Hi, Steve, thanks a lot.|||

Hi,

I have downloaded and installed the add-in. When I try to connect to the database I get an error with Initialization of the data source failed.

Can one one help me on this?

cheers

Josh