SQL Adding a User account: wrong

Showing posts with label wrong. Show all posts

Thursday, March 22, 2012

AdventureWorks db for 101 Samples

I'm not sure if this should be asked here. If it's the wrong place, let me know and I'll correct.
I've just downloaded 101 Samples for Visual Studio 2005. But the data access samples require AdventureWorks database which is not included in the C# sample. Who know where I can get this database? It is a SQL 2005 database, I think.
Thanks!

You can download AdventureWorks from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=2adbc1a8-ae5c-497d-b584-eab6719300cd&DisplayLang=en

|||Thanks a lot, Gail!|||I'm using the version of SQL Server 2005 Express that comes together with Visual Studio 2005 Beta 2. When I try to attach the AventureWorks db, there is an error.

The database 'AdventureWorks' cannot be opened because it is version 607. This server supports version 603 and earlier. A downgrade path is not supported. Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

What version of SQL Server 2005 is required? Could it work with my VS.NET 2005 Beta 2?
Thanks!|||Hi blackpuppy,
You'll need the June CTP version of SQL Server Express, which will work just fine with your VS Beta 2. You can download that version from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=1A722B0F-6CCA-4E8B-B6EA-12D9C450ED92&displaylang=en

Before you install SQL Server Express, using Control Panel | Add/Remove Programs to uninstall your current version of .NET Framework 2.0.

Install the version of .NET Framework 2.0 from the SQL Server Express download site and then install SQL Server Express.|||Are you saying that instead of installing appropriate version of AdventureWorks, sql server 2005 Express has to be installed (downloaded from the internet)? I got installation of sql server 2005 CTP from Microsoft, after attending webcast sessions, and it didn't include AdventureWorks database. Now I'm trying to learn something about 2005, and sample database would really help. I don't want to reinstall Express because it won't include all tools I need for learning.
Is it possible to download somewhere appropriate version of this sample database?
Thanks,
Edi|||

What I'm asking is is there anywhere older copy of AdventureWorks, compatible with Sqlserver 2005 CTP?
Thanks

|||

The version of AdventureWorks at this site : http://www.microsoft.com/downloads/details.aspx?FamilyID=2adbc1a8-ae5c-497d-b584-eab6719300cd&DisplayLang=en
is the appropriate version for SQL Server 2005 June CTP release and SQL Server Express 2005 June CTP.

If your version of SQL Server is older than that, then you may have problems using the sample database available from this site. However, it's not clear to me what version of SQL Server you really have or whether you have SQL Server Express or the Developer edition of SQL Server. Can you run SELECT @.@.VERSION and report back the result? This will identify your SQL Server version and help solve the issue.

|||Well... I have SQL Server 2005 Express Loaded from the June CTP (version 9.00.1128.00) and the above AdventureWorks and it still doesn't work!!!

I still get the error above!... Is there anything else that would work? or better yet are there any other samples out there to down load?

thanks,
ward0093|||

Okay. I've forwarded this to the samples team for further investigation. I'll report back when I get morre information.

In the meantime, if you want other sample databases, you can certainly download and install the pubs and northwind sample databaes from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en

|||Thanks Gail....

I got it working on my Laptop by still not be development machine... and it was the same install for both machines.

Not sure what the deal is

ward0093|||How did you get it working? I am experiencing the same problem and was unable to install a different version of sql express after uninstalling the version that is bundled with VS beta 2.|||

I just downloaded the samples, and the adventureworksdb. The db doesn't have the sample table layout as the samples. The new version is highly normalized. Is there somewhere to get the older db?

Thanks.

|||

I don't understand what you mean by "The db doesn't have the sample table layout as the samples. " Can you be more specific? The database schema has not changed since the June CTP version, so I'm not sure what you're referring to.

Thanks.

|||

gaile-

Thanks for your quick response. Specifically, from CreatingMasterDetails sample:

SELECT EmployeeID, NationalIDNumber, ContactID, LoginID, DepartmentID, ManagerID, ShiftID, Title, EmergencyContactID, AddressID, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, BaseRate, PayFrequency, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate FROM HumanResources.Employee WHERE (EmployeeID = @.@.IDENTITY)

Several of these fields are not in the employee table (DepartmentID, ShiftID, AddressID...). Is the db different for express and server? The downloaded msi's are the same size. I'm using SQLServer2005.

Thanks.

-Gary

sql

AdventureWorks db for 101 Samples

You can download AdventureWorks from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=2adbc1a8-ae5c-497d-b584-eab6719300cd&DisplayLang=en

|||Thanks a lot, Gail!|||I'm using the version of SQL Server 2005 Express that comes together with Visual Studio 2005 Beta 2. When I try to attach the AventureWorks db, there is an error.

What version of SQL Server 2005 is required? Could it work with my VS.NET 2005 Beta 2?
Thanks!

|||Hi blackpuppy,
You'll need the June CTP version of SQL Server Express, which will work just fine with your VS Beta 2. You can download that version from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=1A722B0F-6CCA-4E8B-B6EA-12D9C450ED92&displaylang=en

Before you install SQL Server Express, using Control Panel | Add/Remove Programs to uninstall your current version of .NET Framework 2.0.

What I'm asking is is there anywhere older copy of AdventureWorks, compatible with Sqlserver 2005 CTP?
Thanks

|||

|||Well... I have SQL Server 2005 Express Loaded from the June CTP (version 9.00.1128.00) and the above AdventureWorks and it still doesn't work!!!

I still get the error above!... Is there anything else that would work? or better yet are there any other samples out there to down load?

thanks,
ward0093|||

Okay. I've forwarded this to the samples team for further investigation. I'll report back when I get morre information.

|||Thanks Gail....

I got it working on my Laptop by still not be development machine... and it was the same install for both machines.

Not sure what the deal is

I just downloaded the samples, and the adventureworksdb. The db doesn't have the sample table layout as the samples. The new version is highly normalized. Is there somewhere to get the older db?

Thanks.

|||

Thanks.

|||

gaile-

Thanks for your quick response. Specifically, from CreatingMasterDetails sample:

Thanks.

-Gary

AdventureWorks db for 101 Samples

You can download AdventureWorks from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=2adbc1a8-ae5c-497d-b584-eab6719300cd&DisplayLang=en

|||Thanks a lot, Gail!|||I'm using the version of SQL Server 2005 Express that comes together with Visual Studio 2005 Beta 2. When I try to attach the AventureWorks db, there is an error.

Before you install SQL Server Express, using Control Panel | Add/Remove Programs to uninstall your current version of .NET Framework 2.0.

What I'm asking is is there anywhere older copy of AdventureWorks, compatible with Sqlserver 2005 CTP?
Thanks

|||

|||Well... I have SQL Server 2005 Express Loaded from the June CTP (version 9.00.1128.00) and the above AdventureWorks and it still doesn't work!!!

I still get the error above!... Is there anything else that would work? or better yet are there any other samples out there to down load?

thanks,
ward0093|||

Okay. I've forwarded this to the samples team for further investigation. I'll report back when I get morre information.

|||Thanks Gail....

I got it working on my Laptop by still not be development machine... and it was the same install for both machines.

Not sure what the deal is

I just downloaded the samples, and the adventureworksdb. The db doesn't have the sample table layout as the samples. The new version is highly normalized. Is there somewhere to get the older db?

Thanks.

|||

Thanks.

|||

gaile-

Thanks for your quick response. Specifically, from CreatingMasterDetails sample:

Thanks.

-Gary

AdventureWorks db for 101 Samples

You can download AdventureWorks from this site: http://www.microsoft.com/downloads/details.aspx?FamilyID=2adbc1a8-ae5c-497d-b584-eab6719300cd&DisplayLang=en

|||Thanks a lot, Gail!|||I'm using the version of SQL Server 2005 Express that comes together with Visual Studio 2005 Beta 2. When I try to attach the AventureWorks db, there is an error.

What version of SQL Server 2005 is required? Could it work with my VS.NET 2005 Beta 2?
Thanks!

Before you install SQL Server Express, using Control Panel | Add/Remove Programs to uninstall your current version of .NET Framework 2.0.

What I'm asking is is there anywhere older copy of AdventureWorks, compatible with Sqlserver 2005 CTP?
Thanks

|||

|||Well... I have SQL Server 2005 Express Loaded from the June CTP (version 9.00.1128.00) and the above AdventureWorks and it still doesn't work!!!

I still get the error above!... Is there anything else that would work? or better yet are there any other samples out there to down load?

thanks,
ward0093|||

Okay. I've forwarded this to the samples team for further investigation. I'll report back when I get morre information.

|||Thanks Gail....

I got it working on my Laptop by still not be development machine... and it was the same install for both machines.

Not sure what the deal is

I just downloaded the samples, and the adventureworksdb. The db doesn't have the sample table layout as the samples. The new version is highly normalized. Is there somewhere to get the older db?

Thanks.

|||

Thanks.

|||

gaile-

Thanks for your quick response. Specifically, from CreatingMasterDetails sample:

Thanks.

-Gary

Tuesday, March 20, 2012

Adventure Works Cube

Hi all,

I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen?

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

I am using AS 9.00.3.042. When you filter the day, where you drag the Date.Fiscal hierarchy. If I put the Date.Fiscal hierarchy in the dimension filter panel, then the result is correct. However, if I put the Date.Fiscal hierarchy in the Drop Filter Fields, then the result is not correct.

Thanks,

|||

Sorry, but I'm just not getting the same behavior and I'm using the same SP level. Could you possibly trace the MDX that is submitted for the query (using Profiler) and post that to this thread? That might help determine what's going on.

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

WITH
SET [Filtered] AS
'
DISTINCT(
{
[Date].[Fiscal].[Date].&[2],
[Date].[Fiscal].[Date].&[1]
}
)
'
SET [Tree] AS
'
HIERARCHIZE(
GENERATE(
[Filtered],
{
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Year]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Semester]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Quarter]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month]),
[Date].[Fiscal].CURRENTMEMBER
}
)
)
'
SELECT [Tree] ON Columns FROM [Adventure Works]

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

CREATE SESSION
MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS
'
AGGREGATE({
[Date].[Fiscal].[Date].&[1],
[Date].[Fiscal].[Date].&[2]
})
'

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

SELECT
NON EMPTY [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Sales Amount]
}
ON ROWS
FROM [Adventure Works]
WHERE
(
[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

|||

Sorry Bryan,

I forgot to include this:

CREATE SESSION
SET [Adventure Works].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0] AS
'
{
{ [Date].[Fiscal Year].[All Periods] },
AddCalculatedMembers([Date].[Fiscal Year].[Fiscal Year].MEMBERS)
}
'

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

The AGGREGATE function is causing you to select the first member of the set. If you change the members to &[2] and &[3], you'll get the value for just July 2, 2001. If you replace AGGREGATE with SUM, you'll get the right result but that's not really an option here.

I can't really say why the AGGREGATE function is behaving this way, but would suggest you use the other filtering option in the browser window. If you look just above the "Drop Filters Here" section, you'll see a grid with an entry that looks like "Select Dimension". Apply your filter here.

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

The reason I drag the Fiscal Hierarchy in the "Drop Filters Here" is I want it to act like Excel Pivot Table. When I browser the cube though Excel Pivot Table, it has the same error. Most of our end user use Excel Pivot Table to brower the cube, but AS2005 give the wrong answer for this case.

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

One possible work around is to hide the [Date].Fiscal Year] attribute. I find that having [Date].[Fiscal].[Fiscal Year] and [Date].[Fiscal Year] in the same cube just confuses things,especially when using OWC as it is not really mutliple hierarchy and attribute aware.

What your users would need to do then is to drag [Date].[Fiscal] onto the column and filter it in place. If you start from your example that is showing the incorrect figures and drag [Date].[Fiscal Year] off the grid and drag the [Date].[Fiscal] from the filter to the columns does that give you a grid that looks how you want?

|||

I asked a friend of mine on the product team and it is possible that this issue was fixed in the hotfix rollup that was released for SP2 http://support.microsoft.com/kb/939537 I could not find a specific reference, but there are a couple of broader issues which it might come under. It might be worth testing this in a Virtual PC to see if it corrects the issue.

As with all hotfixes, it is only recommended to install this if it does address a particular issue. Otherwise they recommend waiting for the next service pack, but I have not heard when another service pack is likely to be released. It would not surprise me to see the next SP come out after Windows Server 2008 is released (which should be Q1 next year), but this is just a guess on my behalf.

sql

Adventure Works Cube

Hi all,

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

Thanks,

|||

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

|||

Sorry Bryan,

I forgot to include this:

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

|||

Adventure Works Cube

Hi all,

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

Thanks,

|||

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

|||

Sorry Bryan,

I forgot to include this:

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

|||

Tuesday, March 6, 2012

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);

Saturday, February 25, 2012

ADO.NET Source Custom Properties - Documentation Wrong?

Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Property expressions for components within the Data Flow task are visible and available only on the containing Data Flow task itself at the present time. Select the Data Flow task on the Control Flow tab, or an empty spot on the Data Flow designer surface, go to the Properties window (it doesn't have a Properties dialog box), select Expressions, click the ellipsis, and in there you'll be able to drop down a list, select the SqlCommand property of your DataReader source, and set the expression.

Note that nothing will show up in the DataReader editor to indicate that you've set a value (with an expression) for the SqlCommand property...it will appear blank. I trust that the "usability" of this feature will be enhanced in the future. For now, however, it's better to have awkward access to data flow properties via property expressions, than none at all.

Best regards,

-Doug
|||Doug,
Thank you so much for your post. This is the holy grail I've been looking for, and I'll take it any way I can get it. It had boggled my mind because I had read rumors of this feature but was never able to make it work.
Thanks,
Adrian
|||Doug,

Do you know if Microsoft intends (in future revs) on allowing Web Service Task web-parameters to be updateable dynamically at runtime? Right now this one is killing me. I mean, who calls a web service with the same parameter every time?!?|||

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

As I assume you've discovered, the Property list in the Property Expressions Editor only displays available properties for data flow objects that you've already placed on the Data Flow surface of the designer. (As a result, you can't use the Property list to view all the properties of data flow objects that support property expressions.) For example, if you've placed a DataReader Source on the designer surface, the Property list contains an entry for the [DataReader Source].[SqlCommand] property. The list also displays many properties of the Data Flow task itself.

Enjoy your expressions,

-Doug

|||I don't know the answer to that, but I understand why it would be useful. You can make your request known, as always, by using sqlwish@.microsoft.com and BetaPlace http://msdn.microsoft.com/sql/bugs/default.aspx.

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

Just one more step in my eventual goal: Total global domination.|||A mere email to sqlwish may not achieve your entire goal, but Microsoft is more responsive to customer input than the average person gets to see. Especially the documentation team (have I mentioned those Feedback links in BOL?), where improving a BOL topic runs less risk of having unforeseen side-effects than code changes.|||Doug

I have the same problem as Adrian. I need to set the SQL Command of the Data Reader from a Variable. Trying your approach, the Property Drop Down List of the Property Expression Editor does not list an SqlCommand property.

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Seems like a reasonable request though so why not log it-
http://lab.msdn.microsoft.com/productfeedback/default.aspx|||If the ADO.Net source is in the dataflow, and you open the Expressions dialog for that dataflow, you should indeed find an entry in the properties drop down. It will likely be called "[DataReader Source].[SqlCommand]"

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

You should be able to use aproperty expression (set it at the data flow level though, Expressions), but your syntax is not valid. Have a look at the BOL reference for the expression syntax. It should look more like this -

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.

ADO.NET Source Custom Properties - Documentation Wrong?

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Best regards,

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

Enjoy your expressions,

-Doug

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.

Thursday, February 16, 2012

ADO Connections

Forgive me if this is the wrong forum, but it does have to do with data access. Just a quick question: When using an ADO Component /Object to connect to a database, C# or whatever, that connection that is established - How likely is it that data being retrieved from the database is intercepted, is that channel encrypted/secured, and if not for either questions, how do i secure that channel?

Please check following blog:

http://blogs.msdn.com/sql_protocols/archive/2005/10/04/476705.aspx

If you want to force client encryption, there is a connection string property in ADO, please see below link:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

good luck!

Ming.

Thursday, March 22, 2012

AdventureWorks db for 101 Samples

AdventureWorks db for 101 Samples

AdventureWorks db for 101 Samples

AdventureWorks db for 101 Samples

Tuesday, March 20, 2012

Adventure Works Cube

Adventure Works Cube

Adventure Works Cube

Tuesday, March 6, 2012

ADOMD.NET : dynamic parameters

ADOMD.NET : dynamic parameters

Saturday, February 25, 2012

ADO.NET Source Custom Properties - Documentation Wrong?

ADO.NET Source Custom Properties - Documentation Wrong?

Thursday, February 16, 2012

ADO Connections

SQL Adding a User account

Blog Archive

About Me