Best way to copy database: alli

Showing posts with label alli. Show all posts

Tuesday, March 27, 2012

Bind object to RDLC Report

Hi,all
I hava a question about Bind object to RDLC Report,the RDLC like
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DummyDataSource">
<ConnectionProperties>
<ConnectString />
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>47739726-cc8c-4719-b061-c392c2cceb68</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>WindowsApplication1_MedcialCase</DataSetName>
<Top>0.25cm</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID">
<rd:DefaultName>ID</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Name">
<rd:DefaultName>Name</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PatientOfMedcialCase.Value.Name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Name_1">
<rd:DefaultName>Name_1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="PatientOfMedcialCase">
<rd:DefaultName>PatientOfMedcialCase</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PatientOfMedcialCase.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Patient Of Medcial Case</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>8cm</Width>
</TableColumn>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
</TableColumns>
<Height>1.90476cm</Height>
</Table>
</ReportItems>
<Height>5cm</Height>
</Body>
<rd:ReportID>a69b2d8e-258a-4212-8d4a-c67c96055732</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="WindowsApplication1_MedcialCase">
<rd:DataSetInfo>
<rd:DataSetName>WindowsApplication1</rd:DataSetName>
<rd:ObjectDataSourceType>WindowsApplication1.MedcialCase, WindowsApplication1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</rd:ObjectDataSourceType>
<rd:TableName>MedcialCase</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText />
<DataSourceName>DummyDataSource</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.Guid</rd:TypeName>
<DataField>ID</DataField>
</Field>
<Field Name="PatientOfMedcialCase">
<rd:TypeName>WindowsApplication1.Patient</rd:TypeName>
<DataField>PatientOfMedcialCase</DataField>
</Field>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>24cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

the object I bind to the Report like

public class MedcialCase
{
private Guid iD;

public Guid ID
{
get { return iD; }
set { iD = value; }
}

private Patient patient;

public Patient PatientOfMedcialCase
{
get { return patient; }
set { patient = value; }
}

}

public class Patient
{
private string name;

public string Name
{
get { return name; }
set { name = value; }
}

}

and the code bind object to report like

MedcialCase medcialCase = new MedcialCase();
medcialCase.ID = Guid.NewGuid();
Patient patient = new Patient();
patient.Name = "Michael";
medcialCase.PatientOfMedcialCase = patient;
this.MedcialCaseBindingSource.DataSource = medcialCase;
this.reportViewer1.RefreshReport();

I choose the MedcialCase.ID and MedcialCase.PatientOfMedcialCase.Name to show, but when the report was run ,,just MedcialCase.ID has been shown,
the text of MedcialCase.PatientOfMedcialCase.Name is "=Fields!Name.Value"

I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name

anyone can tell me why?and how to bind object like MedcialCase to the Report?

thanks!

This is snippet from

http://www.gotreportviewer.com/objectdatasources/index.html

Nested objects

Only the first level of public properties can be dragged and dropped to a report. If the type of a property is a custom class with its own properties then you can manually enter an expression to access the values of second level properties. For example if the Employee class has a HomeAddress property of type Address then you can enter the expression =Fields!HomeAddress.Value.Zip to access the value of Zip property.

If the value of a property is a collection then you can use a subreport to display the collection. See this example.

In your case, this translates to "=Fields!PatientOfMedcialCase.Value.Patient.Name"

Hope that helps,

Tudor Trufinescu

|||

hi,Tudor

Thanks for your advice,

when I try to do follow your advice,I Modify the class like:

public class Book
{
private string m_title;
private string m_publisher;
private float m_listPrice;

public Book(string title, string publisher, float listPrice)
{
m_title = title;
m_publisher = publisher;
m_listPrice = listPrice;
}

public string Title
{
get { return m_title; }
set { m_title = value; }
}

public string Publisher
{
get { return m_publisher; }
set { m_publisher = value; }
}

public float ListPrice
{
get { return m_listPrice; }
set { m_listPrice = value; }
}
}

class Author
{
private int m_id;
private string m_name;
private Book book;

public Author(int id)
{
m_id = id;
}

public Author(string name, int id, Book books)
{
m_name = name;
m_id = id;
book = books;
}

public int Id
{
get { return m_id; }
set { m_id = value; }
}

public string Name
{
get { return m_name; }
set { m_name = value; }
}

public Book Books
{
get { return this.book; }
set { this.book = value; }
}
}

and report text like "=Fields!Books.Value.ListPrice" as you say,

when the report run ,the text still display the "#error"

How shall I do?

|||

I have changed the object data source sample to illustrate the use of nested objects

See this post for more details.

http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx

Thanks,

Tudor

|||that's helpful,i have solved the problem,thanks!|||Just wondering why I get the following error when I use GenericsCollection:

"The Value expression for the textbox ‘AddressLine1’ refers to the field ‘Contacts’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. "

for example I have these attributes of a class Company:

public Address PrivateAddress
{
get { return _privateAddress; }
set
{
_privateAddress = value;
}
}

public GenericCollection<Address> Contacts
{
get { return _contacts; }
set
{
_contacts = value;
}
}

I have no problems using the PrivateAddress object, but the Contacts gives me the above error message.

Has anyone implemented Generics objects? Any help would be appreciated.
Thanks

Bind object to RDLC Report

the object I bind to the Report like

public class MedcialCase
{
private Guid iD;

public Guid ID
{
get { return iD; }
set { iD = value; }
}

private Patient patient;

public Patient PatientOfMedcialCase
{
get { return patient; }
set { patient = value; }
}

}

public class Patient
{
private string name;

public string Name
{
get { return name; }
set { name = value; }
}

}

and the code bind object to report like

I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name

anyone can tell me why?and how to bind object like MedcialCase to the Report?

thanks!

This is snippet from

http://www.gotreportviewer.com/objectdatasources/index.html

Nested objects

If the value of a property is a collection then you can use a subreport to display the collection. See this example.

In your case, this translates to "=Fields!PatientOfMedcialCase.Value.Patient.Name"

Hope that helps,

Tudor Trufinescu

|||

hi,Tudor

Thanks for your advice,

when I try to do follow your advice,I Modify the class like:

public class Book
{
private string m_title;
private string m_publisher;
private float m_listPrice;

public Book(string title, string publisher, float listPrice)
{
m_title = title;
m_publisher = publisher;
m_listPrice = listPrice;
}

public string Title
{
get { return m_title; }
set { m_title = value; }
}

public string Publisher
{
get { return m_publisher; }
set { m_publisher = value; }
}

public float ListPrice
{
get { return m_listPrice; }
set { m_listPrice = value; }
}
}

class Author
{
private int m_id;
private string m_name;
private Book book;

public Author(int id)
{
m_id = id;
}

public Author(string name, int id, Book books)
{
m_name = name;
m_id = id;
book = books;
}

public int Id
{
get { return m_id; }
set { m_id = value; }
}

public string Name
{
get { return m_name; }
set { m_name = value; }
}

public Book Books
{
get { return this.book; }
set { this.book = value; }
}
}

and report text like "=Fields!Books.Value.ListPrice" as you say,

when the report run ,the text still display the "#error"

How shall I do?

|||

I have changed the object data source sample to illustrate the use of nested objects

See this post for more details.

http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx

Thanks,

Tudor

|||that's helpful,i have solved the problem,thanks!|||Just wondering why I get the following error when I use GenericsCollection:

for example I have these attributes of a class Company:

public Address PrivateAddress
{
get { return _privateAddress; }
set
{
_privateAddress = value;
}
}

public GenericCollection<Address> Contacts
{
get { return _contacts; }
set
{
_contacts = value;
}
}

I have no problems using the PrivateAddress object, but the Contacts gives me the above error message.

Has anyone implemented Generics objects? Any help would be appreciated.
Thanks
sql

Thursday, March 8, 2012

BI Accelerator: problem with current day setting

Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?Hi
I have already seen this problem a while ago. Checkout
http://www.dbforums.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting the al
l tu current".
What I did at the time (and of course that is not the solution) was to remov
e the Weel.Standard dimension from the cube.
Did you found a solution.?

quote:
Originally posted by Eugene Frolov
Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?

|||Hi!
Checkout this:
http://support.microsoft.com/defaul...b;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message447789.html
>

BI Accelerator: multiply source databeses

Hi All!
I'm trying to use MS BI Accelerator for creating DW which must contain data
from different sources. Every source is our company departmnet standard
database. I'm looking for best practice for collecting fact and dim data
from this databases in BI Acc staging database, i.e. creating Source Data
ETLM process using Master_Import and its sub - DTS packages with mimimal
re-writing of its. For example, I need to get customers for Dim_Customer_Std
dimension table from Department_1, then Department_2 and so on for other
dept's and dim's. Fact table must be populated the same way - sales data
from Department_1 must be consolidated with Department_2 ...
What is the best way to do so: create a different sub-packages for every
department and then add Execute Package task into Master_Import package or
exist another way?If you read the PAG, you will see that the Master Import packages were
design as a convienence for customers wanting to load from flat files. We
fully expected that customers will need to load the staging database with
their own data (possibly from multiple data sources). You need to implement
that piece of the system yourself, i.e. come up with your own "Master
Import" where the data comes from your own data sources. Then you plug that
in place of Master Import. The PAG also discusses ways that you could make
simple changes to the Master Import packages if what you want to do is
similar to what Master Import does -- in your case, this does not seem to
apply, so you would just replace Master Import with your own system -- then
Master Update takes the data from the staging database and moves it through
the system from there.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All!
> I'm trying to use MS BI Accelerator for creating DW which must contain
data
> from different sources. Every source is our company departmnet standard
> database. I'm looking for best practice for collecting fact and dim data
> from this databases in BI Acc staging database, i.e. creating Source Data
> ETLM process using Master_Import and its sub - DTS packages with mimimal
> re-writing of its. For example, I need to get customers for
Dim_Customer_Std
> dimension table from Department_1, then Department_2 and so on for other
> dept's and dim's. Fact table must be populated the same way - sales data
> from Department_1 must be consolidated with Department_2 ...
> What is the best way to do so: create a different sub-packages for every
> department and then add Execute Package task into Master_Import package or
> exist another way?
>|||Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
with MS BI Accelerator (Development, Deployment and Maintenance).
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
: news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
> If you read the PAG, you will see that the Master Import packages were
> design as a convienence for customers wanting to load from flat files. We
> fully expected that customers will need to load the staging database with
> their own data (possibly from multiple data sources). You need to
implement
> that piece of the system yourself, i.e. come up with your own "Master
> Import" where the data comes from your own data sources. Then you plug
that
> in place of Master Import. The PAG also discusses ways that you could make
> simple changes to the Master Import packages if what you want to do is
> similar to what Master Import does -- in your case, this does not seem to
> apply, so you would just replace Master Import with your own system --
then
> Master Update takes the data from the staging database and moves it
through
> the system from there.
> Hope that helps.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Eugene Frolov" <john@.alice.ru> wrote in message
> news:%23om6%23VntEHA.2536@.TK2MSFTNGP11.phx.gbl...
> data
data[vbcol=seagreen]
Data[vbcol=seagreen]
> Dim_Customer_Std
or[vbcol=seagreen]
>|||That is what we call the PAG (Prescriptive Architecture Guides).
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene Frolov" <john@.alice.ru> wrote in message
news:OMgGTkztEHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thank you for answer, but: what is a PAG? I'm reading ALL 3 guides comes
> with MS BI Accelerator (Development, Deployment and Maintenance).
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> /
> : news:OHtoAPttEHA.2128@.TK2MSFTNGP11.phx.gbl...
We[vbcol=seagreen]
with[vbcol=seagreen]
> implement
> that
make[vbcol=seagreen]
to[vbcol=seagreen]
> then
> through
> rights.
standard[vbcol=seagreen]
> data
> Data
mimimal[vbcol=seagreen]
other[vbcol=seagreen]
data[vbcol=seagreen]
every[vbcol=seagreen]
package[vbcol=seagreen]
> or
>

Friday, February 24, 2012

Best way to this?

Hi All!
I'm setting up a game that involves pari-mutuel betting.
For example - Total Pool is 1, 213. Take total amount and divide into
bet.
Bet Odds would be:
Calculation
First- 314 4 - 1 (3.86 - 1)
(1213 / 314)
Second - 251 5 - 1 (4.83 - 1)
(1213 / 251)
Third - 414 3 - 1 (2.92 - 1) (1213 / 41
4)
Fourth - 234 5 - 1 (5.18 - 1) (1213 /
234)
Total - 1,213
I thought of setting three tables, one for totals of each place, one table
to hold the grand total, and one table for the results of the odds to go to.
And then at the end of the race, clear out all tables and do it again.
Any thoughts or better suggestions. I'm all ears!
Thanks!what do you mean one table to hold the grand total?
Is the total pool going to change?
If you are going to keep flushing the data then why do you need permanent
tables in the first place. I am sorry I don't know about this game..
Can you give an insight to it?
"Rudy" wrote:

> Hi All!
> I'm setting up a game that involves pari-mutuel betting.
> For example - Total Pool is 1, 213. Take total amount and divide into
> bet.
> Bet Odds would be:
> Calculation
> First- 314 4 - 1 (3.86 - 1)
> (1213 / 314)
> Second - 251 5 - 1 (4.83 - 1)
> (1213 / 251)
> Third - 414 3 - 1 (2.92 - 1) (1213 /
414)
> Fourth - 234 5 - 1 (5.18 - 1) (1213
/
> 234)
> Total - 1,213
> I thought of setting three tables, one for totals of each place, one table
> to hold the grand total, and one table for the results of the odds to go t
o.
> And then at the end of the race, clear out all tables and do it again.
> Any thoughts or better suggestions. I'm all ears!
> Thanks!
>|||I'm a little

. Surely you don't need to store the computed data
somewhere.
You have your data (simplified, please don't criticise for lack of primary
keys, etc):
create table bets (position varchar(20), numbets int);
And you have data:
insert into bets values ('First', 314);
insert into bets values ('Second', 251);
insert into bets values ('Third', 414);
insert into bets values ('Fourth', 234);
And then you have views which show the info you want:
select *, (select sum(numbets) from bets) / convert(float,numbets) payout
from bets
But I wouldn't show the "5-1" version, because you might want to use 7-2 if
it's near 3.5 for example. Much easier just to say "Paying $1.52" or whateve
r.
Does this help?
Rob
"Rudy" wrote:

> Hi All!
> I'm setting up a game that involves pari-mutuel betting.
> For example - Total Pool is 1, 213. Take total amount and divide into
> bet.
> Bet Odds would be:
> Calculation
> First- 314 4 - 1 (3.86 - 1)
> (1213 / 314)
> Second - 251 5 - 1 (4.83 - 1)
> (1213 / 251)
> Third - 414 3 - 1 (2.92 - 1) (1213 /
414)
> Fourth - 234 5 - 1 (5.18 - 1) (1213
/
> 234)
> Total - 1,213
> I thought of setting three tables, one for totals of each place, one table
> to hold the grand total, and one table for the results of the odds to go t
o.
> And then at the end of the race, clear out all tables and do it again.
> Any thoughts or better suggestions. I'm all ears!
> Thanks!
>|||Hi Guys!
This helps Rob. I may want to keep some of the records, not sure. My main
objetive here is just to have the calculation done quickly. Omni, is just a
simple game to bet on horses.
Thanks again!!!
Rudy
"Rob Farley" wrote:
> I'm a little

. Surely you don't need to store the computed data
> somewhere.
> You have your data (simplified, please don't criticise for lack of primary
> keys, etc):
> create table bets (position varchar(20), numbets int);
> And you have data:
> insert into bets values ('First', 314);
> insert into bets values ('Second', 251);
> insert into bets values ('Third', 414);
> insert into bets values ('Fourth', 234);
> And then you have views which show the info you want:
> select *, (select sum(numbets) from bets) / convert(float,numbets) payout
> from bets
> But I wouldn't show the "5-1" version, because you might want to use 7-2 i
f
> it's near 3.5 for example. Much easier just to say "Paying $1.52" or whate
ver.
> Does this help?
> Rob
>
> "Rudy" wrote:
>|||Well you can always save the contents of the view to an archive table after
betting stops. That's not going to be hard to do. But while the numbers are
changing, just let your view change with it.
"Rudy" wrote:
> Hi Guys!
> This helps Rob. I may want to keep some of the records, not sure. My main
> objetive here is just to have the calculation done quickly. Omni, is just
a
> simple game to bet on horses.
> Thanks again!!!
> Rudy
> "Rob Farley" wrote:
>

Sunday, February 19, 2012

Best way to send DB results to variable

Hello all

I have a database query which will return a single value

SELECT COUNT(DISTINCT(Level_id)) FROM RoleSkill WHERE Route_ID = 1

obviously this will return only a single value and not an array of data like most database queries, therefore i would like it to be sent directly to an integer variable.

I tried doing this directly with a sqlcommand but get error that it cannot be converted to integer. Is there a good way of solving this problem?

thanks for your help

TomExecuteScalar|||Aha right you are!

thanks very much i expected the process to be more complex, i had forgotten there are 3 options for a command object.

cheers ...Tom|||Output parameters

Tuesday, February 14, 2012

Best way to Import data from EXCEL using a TSQL

Hi All
I have a excel spread sheet that i need data from. This is the Data.
MyID
TextField
DateField
IntField
FloatField
1
Ian One
01/04/04
10
10.0304455900
2
Ian Two
02/04/04
20
20.0304455900
3
Ian Three
03/04/04
30
30.0304455900
4
Ian Four
04/04/04
40
40.0304455900
5
Ian Five
05/04/04
50
50.0304455900
6
Ian Six
06/04/04
60
60.0304455900
I have a table call tbl_Import which is built based on the field names
above the data.
MyID - Int
TextField - Varchar
DateField - DateTime
IntField - Int
FloatField - Float.
I have tried 2 different methods for getting this data in to the table
correctly.
First
Declare @.ExcelSource as Varchar(255)
SET @.ExcelSource ='\\Server\RPT\TestImport.xls'
Insert Into tbl_Import
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="\\Server\RPT\TestImport.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...Import_Data
Select * from tbl_Import
Delete from tbl_Import
This method gives me the following error message.
"Error converting data type nvarchar to float."
So i then change the data type in the table to VarChar on the Fload field
And it does the import but the data columns are all out of order in the
tables like it just guest them.
SECOND
BULK INSERT tbl_Import
FROM '\\Server\RPT\TestImport.xls'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
DATAFILETYPE = 'char'
)
Select * from tbl_Import
Delete from tbl_Import
And i get this message.
"Bulk insert data conversion error (type mismatch) for row 1, column 1
(MyID)."
Is there a better easer why of doing this that acktualy works.
I will be need ing to include it into a Stored Procedure once i have got it
importing the data corectly.
Ian
If it is a one time import, then just use dts ( I am assuming that your data
is formatted:
myId textField dateField etc
And not each value in a different row. This will be very hard to do using
SQL, since once you import telling which groups with which will be a
nightmare. )
Just right click on the database in enterprise manager and choose import.
It is pretty simple from there
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored

"Ian" <ian@.NoWhere.com> wrote in message
news:%23kDlrwjeEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I have a excel spread sheet that i need data from. This is the Data.
>
> MyID
> TextField
> DateField
> IntField
> FloatField
> 1
> Ian One
> 01/04/04
> 10
> 10.0304455900
> 2
> Ian Two
> 02/04/04
> 20
> 20.0304455900
> 3
> Ian Three
> 03/04/04
> 30
> 30.0304455900
> 4
> Ian Four
> 04/04/04
> 40
> 40.0304455900
> 5
> Ian Five
> 05/04/04
> 50
> 50.0304455900
> 6
> Ian Six
> 06/04/04
> 60
> 60.0304455900
>
>
> I have a table call tbl_Import which is built based on the field names
> above the data.
> MyID - Int
> TextField - Varchar
> DateField - DateTime
> IntField - Int
> FloatField - Float.
> I have tried 2 different methods for getting this data in to the table
> correctly.
> First
> Declare @.ExcelSource as Varchar(255)
> SET @.ExcelSource ='\\Server\RPT\TestImport.xls'
> Insert Into tbl_Import
> SELECT * FROM
> OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="\\Server\RPT\TestImport.xls";User ID=Admin;Password=;Extended
> properties=Excel 5.0')...Import_Data
> Select * from tbl_Import
> Delete from tbl_Import
>
> This method gives me the following error message.
> "Error converting data type nvarchar to float."
>
> So i then change the data type in the table to VarChar on the Fload field
> And it does the import but the data columns are all out of order in the
> tables like it just guest them.
>
> SECOND
>
> BULK INSERT tbl_Import
> FROM '\\Server\RPT\TestImport.xls'
> WITH
> (
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> DATAFILETYPE = 'char'
> )
> Select * from tbl_Import
> Delete from tbl_Import
> And i get this message.
> "Bulk insert data conversion error (type mismatch) for row 1, column 1
> (MyID)."
>
>
> Is there a better easer why of doing this that acktualy works.
> I will be need ing to include it into a Stored Procedure once i have got
it
> importing the data corectly.
>
> Ian
>
>
>
>
>
>
>
>
>
|||Ian,
You can import Excel data conveniently with OpenRowSet. Here is a
script that should be close to what you need. You may need to modify
some registry values so that the mixed-type column will be imported as
text. See
http://groups.google.com/groups?q=29...8-39393E666E76
for relevant threads on this.
set nocount on
go
-- modify registry entries
Set
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows
to 20
Set
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\ImportMixedTypes
to 'Text'
-- specify IMEX=1 in the connection string of OpenRowSet
-- don't ask what this does - there's virtually no documentation of it

create table Staging (
excelRow int identity(1,1) primary key,
s varchar(100)
)
insert into Staging
select F1 from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=NO;IMEX=1'
,Sheet1$
)
go
create table Data (
pk int not null primary key,
tx varchar(20), -- increase from 80 as needed
dt datetime,
n integer,
f float
)
-- if there are blank rows higher up than
-- the row before #1, change this appropriately
declare @.start int
set @.start = (
select min(excelRow)
from Staging
where s is null
)
declare @.blocksize int
set @.blocksize = (
select min(excelRow) - @.start
from Staging
where s is null and excelRow > @.start
)
set @.start = @.start + 1
insert into Data
select
(select s from Staging where excelRow = A.Block),
(select s -- set datetime mdy or dmy previously if needed
from Staging where excelRow = A.Block+1),
(select s from Staging where excelRow = A.Block+2),
(select s from Staging where excelRow = A.Block+3),
(select s from Staging where excelRow = A.Block+4)
from (
select distinct excelRow as Block
from Staging
where (excelRow - @.start) % @.blocksize = 0
and excelRow >= @.start
) A
go
select * from Data
go
SK
drop table Staging, Data
Ian wrote:

>Hi All
>I have a excel spread sheet that i need data from. This is the Data.
>
> MyID
> TextField
> DateField
> IntField
> FloatField
> 1
> Ian One
> 01/04/04
> 10
> 10.0304455900
> 2
> Ian Two
> 02/04/04
> 20
> 20.0304455900
> 3
> Ian Three
> 03/04/04
> 30
> 30.0304455900
> 4
> Ian Four
> 04/04/04
> 40
> 40.0304455900
> 5
> Ian Five
> 05/04/04
> 50
> 50.0304455900
> 6
> Ian Six
> 06/04/04
> 60
> 60.0304455900
>
>
>I have a table call tbl_Import which is built based on the field names
>above the data.
>MyID - Int
>TextField - Varchar
>DateField - DateTime
>IntField - Int
>FloatField - Float.
>I have tried 2 different methods for getting this data in to the table
>correctly.
>First
>Declare @.ExcelSource as Varchar(255)
>SET @.ExcelSource ='\\Server\RPT\TestImport.xls'
>Insert Into tbl_Import
>SELECT * FROM
>OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data
>Source="\\Server\RPT\TestImport.xls";User ID=Admin;Password=;Extended
>properties=Excel 5.0')...Import_Data
>Select * from tbl_Import
>Delete from tbl_Import
>
>This method gives me the following error message.
>"Error converting data type nvarchar to float."
>
>So i then change the data type in the table to VarChar on the Fload field
>And it does the import but the data columns are all out of order in the
>tables like it just guest them.
>
>SECOND
>
>BULK INSERT tbl_Import
>FROM '\\Server\RPT\TestImport.xls'
>WITH
> (
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> DATAFILETYPE = 'char'
> )
>Select * from tbl_Import
>Delete from tbl_Import
>And i get this message.
>"Bulk insert data conversion error (type mismatch) for row 1, column 1
>(MyID)."
>
>
>Is there a better easer why of doing this that acktualy works.
>I will be need ing to include it into a Stored Procedure once i have got it
>importing the data corectly.
>
>Ian
>
>
>
>
>
>
>
>
>
>
|||Hi Steve
Thanks for your time.
I have read the code you sent.
But I am not sure that it will cater for the fact that the first row in the
Spread sheet has the field names.
The spread sheet in fact is used by people and a VB application before it is
imported by my Stored Procedure and they need to know the column names.
A B C D
E
1 MyID TextField DateField IntField
FloatField
2 1 Ian One 01/04/04 10
10.0304455900
3 2 Ian Two 02/04/04 20
20.0304455900
Sorry but the lay out of the sample data got a little screwed up when I sent
it. I hope this time it looks more accurate.
Above is what it should have looked like.
What you have got here is really well thought up and actually might have
solved another of my problems.
If I am reading the code right then I think what it is doing is thinking all
the data and column names are in the first column. so that is where you get
your single column from in the staging table. Then you select the 5 rows in
the staging table to make up each record.
Why is it that the registry has to be changed.
I ask because this is for a work server.
If it is the only why to import data from Excel as it's correct data type
then I will do it.
Ian
"Steve Kass" <skass@.drew.edu> wrote in message
news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Ian,
> You can import Excel data conveniently with OpenRowSet. Here is a
> script that should be close to what you need. You may need to modify
> some registry values so that the mixed-type column will be imported as
> text. See
> http://groups.google.com/groups?q=29...8-39393E666E76
> for relevant threads on this.
> set nocount on
> go
> -- modify registry entries
> Set
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows
> to 20
> Set
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\ImportMixedTypes[vbcol=seagreen]
> to 'Text'
> -- specify IMEX=1 in the connection string of OpenRowSet
> -- don't ask what this does - there's virtually no documentation of it

> create table Staging (
> excelRow int identity(1,1) primary key,
> s varchar(100)
> )
> insert into Staging
> select F1 from OpenRowSet(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=NO;IMEX=1'
> ,Sheet1$
> )
> go
> create table Data (
> pk int not null primary key,
> tx varchar(20), -- increase from 80 as needed
> dt datetime,
> n integer,
> f float
> )
> -- if there are blank rows higher up than
> -- the row before #1, change this appropriately
> declare @.start int
> set @.start = (
> select min(excelRow)
> from Staging
> where s is null
> )
> declare @.blocksize int
> set @.blocksize = (
> select min(excelRow) - @.start
> from Staging
> where s is null and excelRow > @.start
> )
> set @.start = @.start + 1
>
> insert into Data
> select
> (select s from Staging where excelRow = A.Block),
> (select s -- set datetime mdy or dmy previously if needed
> from Staging where excelRow = A.Block+1),
> (select s from Staging where excelRow = A.Block+2),
> (select s from Staging where excelRow = A.Block+3),
> (select s from Staging where excelRow = A.Block+4)
> from (
> select distinct excelRow as Block
> from Staging
> where (excelRow - @.start) % @.blocksize = 0
> and excelRow >= @.start
> ) A
> go
> select * from Data
> go
> SK
> drop table Staging, Data
> Ian wrote:
it[vbcol=seagreen]
|||Still the lay out is not correct.
The Data is in rows just like it would be in a table with the first row as
the field names.
Ian
"Ian" <ian@.NoWhere.com> wrote in message
news:%235F6xokeEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Hi Steve
> Thanks for your time.
> I have read the code you sent.
> But I am not sure that it will cater for the fact that the first row in
the
> Spread sheet has the field names.
> The spread sheet in fact is used by people and a VB application before it
is
> imported by my Stored Procedure and they need to know the column names.
> A B C
D
> E
> 1 MyID TextField DateField IntField
> FloatField
> 2 1 Ian One 01/04/04 10
> 10.0304455900
> 3 2 Ian Two 02/04/04 20
> 20.0304455900
> Sorry but the lay out of the sample data got a little screwed up when I
sent
> it. I hope this time it looks more accurate.
> Above is what it should have looked like.
> What you have got here is really well thought up and actually might have
> solved another of my problems.
> If I am reading the code right then I think what it is doing is thinking
all
> the data and column names are in the first column. so that is where you
get
> your single column from in the staging table. Then you select the 5 rows
in[vbcol=seagreen]
> the staging table to make up each record.
>
> Why is it that the registry has to be changed.
> I ask because this is for a work server.
> If it is the only why to import data from Excel as it's correct data type
> then I will do it.
>
> Ian
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\ImportMixedTypes[vbcol=seagreen]

[vbcol=seagreen]
field[vbcol=seagreen]
got
> it
>
|||Ian,
It's much easier if this is in the form of a table. This should
select the information:
select MyID, TextField, DateField, IntField, FloatField
from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=YES'
,Sheet1$
)
if you use the appropriate file name. There should be no reason to
modify the registry in this case, but sometimes you will get additional
blank rows imported below the data and you can make the change if need
be. The reason for the changes, if needed, are because the Excel data
provider has various options that are controlled by the registry entries.
You can still rely on a staging table into which you import text if
needed, and you may need to be careful with the dates, because the
format aa/bb/cc is ambiguous.
SK
Ian wrote:

>Hi Steve
>Thanks for your time.
>I have read the code you sent.
>But I am not sure that it will cater for the fact that the first row in the
>Spread sheet has the field names.
>The spread sheet in fact is used by people and a VB application before it is
>imported by my Stored Procedure and they need to know the column names.
> A B C D
>E
>1 MyID TextField DateField IntField
>FloatField
>2 1 Ian One 01/04/04 10
>10.0304455900
>3 2 Ian Two 02/04/04 20
>20.0304455900
>Sorry but the lay out of the sample data got a little screwed up when I sent
>it. I hope this time it looks more accurate.
>Above is what it should have looked like.
>What you have got here is really well thought up and actually might have
>solved another of my problems.
>If I am reading the code right then I think what it is doing is thinking all
>the data and column names are in the first column. so that is where you get
>your single column from in the staging table. Then you select the 5 rows in
>the staging table to make up each record.
>
>Why is it that the registry has to be changed.
>I ask because this is for a work server.
>If it is the only why to import data from Excel as it's correct data type
>then I will do it.
>
>Ian
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
>
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Eng ines\Excel\ImportMixedTypes
>
>it
>
>
>
|||Hi Steve
when i execut
select MyID,TextField,DateField,IntField,FloatField
from OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\Server1\RPT\TestImport.xls;HDR=NO'
,Import_Data
)
I get this as an out put.
Server: Msg 207, Level 16, State 3, Line 34
Invalid column name 'MyID'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'TextField'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'DateField'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'IntField'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'FloatField'.
BUT
When i do
select MyID,TextField,DateField,IntField,FloatField
from OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=\\Server1\RPT\TestImport.xls;HDR=YES; IMEX=1'
,Import_Data
)
Then it sort of works.
It does select the data but some of it is not exact.
Excel
30.030445111
Selected it is
30.030445110999999
And all the Integers
1
becomes.
1.0
Is there some thing else i need to set.
Ian
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Ian,
> It's much easier if this is in the form of a table. This should
> select the information:
> select MyID, TextField, DateField, IntField, FloatField
> from OpenRowSet(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=YES'
> ,Sheet1$
> )
> if you use the appropriate file name. There should be no reason to
> modify the registry in this case, but sometimes you will get additional
> blank rows imported below the data and you can make the change if need
> be. The reason for the changes, if needed, are because the Excel data
> provider has various options that are controlled by the registry entries.
> You can still rely on a staging table into which you import text if
> needed, and you may need to be careful with the dates, because the
> format aa/bb/cc is ambiguous.
> SK
>
> Ian wrote:
the[vbcol=seagreen]
is[vbcol=seagreen]
D[vbcol=seagreen]
sent[vbcol=seagreen]
all[vbcol=seagreen]
get[vbcol=seagreen]
in
>
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Eng ines\Excel\ImportMixedType
s[vbcol=seagreen]

[vbcol=seagreen]
field[vbcol=seagreen]
got[vbcol=seagreen]
|||That's because you typed HDR=NO where I suggested HDR=YES. HDR means
"header row", and you have a header row. You can also look at select *
to see what the columns are, if there's still a problem, and for the
record, the columns are automatically named F1, F2, F3, ... when you say
HDR=NO.
SK
Ian wrote:

>Hi Steve
>when i execut
>select MyID,TextField,DateField,IntField,FloatField
>from OPENROWSET (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=\\Server1\RPT\TestImport.xls;HDR=NO'
> ,Import_Data
>)
>I get this as an out put.
>
>Server: Msg 207, Level 16, State 3, Line 34
>Invalid column name 'MyID'.
>Server: Msg 207, Level 16, State 1, Line 34
>Invalid column name 'TextField'.
>Server: Msg 207, Level 16, State 1, Line 34
>Invalid column name 'DateField'.
>Server: Msg 207, Level 16, State 1, Line 34
>Invalid column name 'IntField'.
>Server: Msg 207, Level 16, State 1, Line 34
>Invalid column name 'FloatField'.
>
>BUT
>
>When i do
>select MyID,TextField,DateField,IntField,FloatField
>from OPENROWSET (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 5.0;Database=\\Server1\RPT\TestImport.xls;HDR=YES; IMEX=1'
> ,Import_Data
>)
>
>Then it sort of works.
>It does select the data but some of it is not exact.
>Excel
>30.030445111
>Selected it is
>30.030445110999999
>And all the Integers
>1
>becomes.
>1.0
>
>Is there some thing else i need to set.
>
>Ian
>
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
>
>the
>
>is
>
>D
>
>sent
>
>all
>
>get
>
>in
>
>s
>
>

>
>field
>
>got
>
>
>
|||Hi Steve
That is fantastic thank you for explaning that.
This is what i have done.
Insert Into tbl_Import
select MyID,TextField,DateField,IntField,FloatField,TextF loatField,
TextFloatFieldTwo
from OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=\\Server\RPT\TestImport.xls;HDR=YES;I MEX=1'
,Import_Data
)
Select * from tbl_Import
Delete from tbl_Import
It does work. I have attached a small TXT file with the input and output
values whe using Decimales.
In Excel the Value is 20.03 after import the float is 20.030000000000001
As the out put showes i may have to format the excel worsheet to Text and
the import the data because i need it to be exact. i cannot have it adding
fractions to my input.
Again. Thank you for you
Ian
"Steve Kass" <skass@.drew.edu> wrote in message
news:O%23q8QKleEHA.720@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> That's because you typed HDR=NO where I suggested HDR=YES. HDR means
> "header row", and you have a header row. You can also look at select *
> to see what the columns are, if there's still a problem, and for the
> record, the columns are automatically named F1, F2, F3, ... when you say
> HDR=NO.
> SK
> Ian wrote:
entries.[vbcol=seagreen]
it[vbcol=seagreen]
10[vbcol=seagreen]
have[vbcol=seagreen]
thinking[vbcol=seagreen]
rows[vbcol=seagreen]
type[vbcol=seagreen]
>
e[vbcol=seagreen]
names[vbcol=seagreen]
table[vbcol=seagreen]
the[vbcol=seagreen]
1[vbcol=seagreen]
begin 666 InputOutPut.txt
M4U%,(%1A8FQE($]U='!U= T*#0I&;&]A=$9I96QD"0E497AT1FQO871&:65L
M9 D)5&5X=$9L;V%T1FEE;&14=V\-"C$P+C S,3$U"0DQ,"XP,S$R"0D),3DX
M+C P, T*,C N,#,P,# P,# P,# P,# Q"3(P+C S"0D)-#4-"C,P+C S,#0T
M-30Y.3DY.3DY.0DS,"XP,S T"0D),C4U+C<X,PT*-# N,#,P-#0U-3@.Y.3DY
M.3DY"30P+C S,#0)"0DT-34N,# Y#0HU,"XP,S T,C(Q.3DY.3DY.3<)-3 N
M,#,P- D)"3$P+C P, T*-C N,#,P-#,Y.3DY.3DY.3DY"38P+C S,#0)"0DP
M,3DN,#(P#0H-"@.T*1F]R;6%T($9L;V%T"0E&;W)M870@.5F%R8VAA<@.D)1F]R
M;6%T(%9A<F-H87(-"@.T*#0H-"@.T*#0I%>&-E;"!);G!U= T*#0I&;&]A=$9I
M96QD"0E497AT1FQO871&:65L9 D)5&5X=$9L;V%T1FEE;&14=V\-"C$P+C S
M,3$U"0DQ,"XP,S$Q-0D),3DX+C P, T*,C N,#,)"0DR,"XP,PD)"30U#0HS
M,"XP,S T-#4U"0DS,"XP,S T-#4U"0DR-34N-S@.S#0HT,"XP,S T-#4U.0D)
M-# N,#,P-#0U-3D)"30U-2XP,#D-"C4P+C S,#0R,C()"34P+C S,#0R,C()
M"3$P+C P, T*-C N,#,P-#0)"38P+C S,#0T"0DP,3DN,#(P#0H-"D9O<FUA
H="!'96YE<F%L"0E&;W)M870@.1V5N97)A; D)1F]R;6%T(%1E>'0-"@.``
`
end
|||Ian,
If you need to represent the value 20.03 exactly, then [float] is the
wrong data type to use. The numbers [float] can represent exactly are a
specific set of binary fractions, not decimal fractions. Because 20.03
cannot be written exactly in the form <integer>/<power of 2>, [float]
cannot store it exactly. SQL Server provides types to represent decimal
fractions exactly: use DECIMAL(p,s) for precision p up to 38 and scale s
between 0 and p. If you import the number 20.030000000000001 into a
column of type, say, decimal(20, 8), it will be stored as 20.03 exactly.
SK
Ian wrote:

>Hi Steve
>That is fantastic thank you for explaning that.
>This is what i have done.
>Insert Into tbl_Import
>select MyID,TextField,DateField,IntField,FloatField,TextF loatField,
>TextFloatFieldTwo
>from OPENROWSET (
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 5.0;Database=\\Server\RPT\TestImport.xls;HDR=YES;I MEX=1'
> ,Import_Data
>)
>Select * from tbl_Import
>Delete from tbl_Import
>
>It does work. I have attached a small TXT file with the input and output
>values whe using Decimales.
>In Excel the Value is 20.03 after import the float is 20.030000000000001
>As the out put showes i may have to format the excel worsheet to Text and
>the import the data because i need it to be exact. i cannot have it adding
>fractions to my input.
>
>Again. Thank you for you
>Ian
>
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:O%23q8QKleEHA.720@.TK2MSFTNGP11.phx.gbl...
>
>entries.
>
>it
>
>10
>
>have
>
>thinking
>
>rows
>
>type
>
>e
>
>names
>
>table
>
>the
>
>1
>
>
>
>SQL Table Output
>FloatFieldTextFloatFieldTextFloatFieldTwo
>10.0311510.0312198.000
>20.03000000000000120.0345
>30.03044549999999930.0304255.783
>40.03044558999999940.0304455.009
>50.03042219999999750.030410.000
>60.03043999999999960.0304019.020
>
>Format FloatFormat VarcharFormat Varchar
>
>
>Excel Input
>FloatFieldTextFloatFieldTextFloatFieldTwo
>10.0311510.03115198.000
>20.0320.0345
>30.030445530.0304455255.783
>40.0304455940.03044559455.009
>50.030422250.030422210.000
>60.0304460.03044019.020
>Format GeneralFormat GeneralFormat Text
>

Tuesday, March 27, 2012

Bind object to RDLC Report

Nested objects

Bind object to RDLC Report

Nested objects

Thursday, March 8, 2012

BI Accelerator: problem with current day setting

BI Accelerator: multiply source databeses

Friday, February 24, 2012

Best way to this?

Sunday, February 19, 2012

Best way to send DB results to variable

Tuesday, February 14, 2012

Best way to Import data from EXCEL using a TSQL

Best way to Import data from EXCEL using a TSQL

Best way to copy database

Blog Archive

About Me