Analysis Service Deployment Error

Hi,

I created a SQL Server 2005 Analysis Service project. When I tried to deploy a standard Decision Tree model, it gave me errors (see below). Clearly, I can't use ntext data type with DISTINCT, but how can I change the SQL command since it was automatically created? What was the final impact of removing the DISTINCT word from the SQL command?

SQL queries 1
SELECT DISTINCT [dbo_Training_x0020_Data].[Hellos] AS [dbo_Training_x0020_DataHellos0_0]

FROM [dbo].[Training Data] AS [dbo_Training_x0020_Data]

Error Messages 1
OLE DB error: OLE DB or ODBC error: The ntext data type cannot be selected as DISTINCT because it is not comparable.; 42000.

Please assist!

MaryYou can change the data type in the DSV, or add a calculated DSV column that casts the ntext to text

analysis Backup problem

Hi guys,

Im having problem in backing up my Analysis Database...Below is the error...

"The semaphore period timeout has expired"

Anybody who encountered this problem...Please let me know...

thanks,

Larry

Hi Larry,

I've encountered this same problem but this happened to me while starting up our Analysis Services. How did you resolve this problem?

Regards,

Joseph

|||

If you see this problem persisting please report it at Connect (http://connect.microsoft.com/sql)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

analysis Backup problem

Hi guys,

Im having problem in backing up my Analysis Database...Below is the error...

"The semaphore period timeout has expired"

Anybody who encountered this problem...Please let me know...

thanks,

Larry

Hi Larry,

I've encountered this same problem but this happened to me while starting up our Analysis Services. How did you resolve this problem?

Regards,

Joseph

|||

If you see this problem persisting please report it at Connect (http://connect.microsoft.com/sql)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

an unkown insert type by me(!)

Hi Dear Coder Friends;

i just discovered below insert type in MSSQL 2005 Automatic creating insert sentence but i couldn't use because there is a syntax error.

INSERT INTO [KimlikBilgileri]

([CvId]

,[KimlikNo]

,[Ad]

,[Soyad]

,[Cinsiyet]

,[DogumTarihi]

,[UlkeId]

,[DogumYeri]

,[MedeniDurumu])

VALUES

(<CvId, int,>

,<KimlikNo, char(11),>

,<Ad, varchar(50),>

,<Soyad, varchar(50),>

,<Cinsiyet, char(5),>

,<DogumTarihi, smalldatetime,>

,<UlkeId, int,>

,<DogumYeri, varchar(50),>

,<MedeniDurumu, varchar(8),>)

Question for above;

-- What's this type called?

-- does it make any security bug like injections?

i want to use this one as a stored proc to add "create proc KimlikBilgilerInsert as ". So i think i don't have to declare one by one

am i right?

Thank you for your valuable knowledge

The syntax error is due to the datatypes. Remove all the datatype indicators.

This is called an INSERT statement.

Yes, it is susceptible to SQL Injection -there are very long varchar(50) fields, and there is no data validation. I would consider where the values are gathered, and if from textboxes on a form, then I would put this statement in a stored procedure and add some data validation code.

An interview question

Hi,

My wife had an interview today. Below is the sql-question she was asked:

There are 2 tables, A and B. The tables have same structure - each of them consists of 100 columns named c_1 through c_100. There are no primary keys defined on the tables.
Each table has 10,000 rows. Most rows are identical for tables A and B, however there are few that are not.
She was required to show only those rows that have no match in the other table - by 1 query that will be short, i.e. - will not contain someting like "col_1,col_2,.._col_99,Col_100" .Hmm...interesting question. It's hard to think of how to do this if there is no field(s) that can be used to join the two tables together, but part of the way might be doing a UNION on the two tables since they have the same structure, then using the UNION as a sub-query for an outer GROUP BY and do a HAVING Count(*) = 1. That way, it'll only show records that do not have a match to another table.

The problem with this is that you don't know which table contains that record and you do have to do the GROUP BY on all the fields outputted from the UNION query.

I'm kinda reaching here, but maybe create some type of primary key composed of the data in each row...by creating an expression like SOUNDEX(field1 + field2 + ... field99 + field100). That way, you have a primary key that might be dependable for use in an OUTER or FULL join. Just another idea...

Maybe something like this could be part of the way...I'm curious as to what the answer would be myself, especially if the answer has to be "1 query that will be short".

Kael V. Dowdy MCSD, MCP|||I think the answer involves using the BINARY_CHECKSUM function, but I'll have to check it out.

blindman|||OK, here goes:

select *
from (select BINARY_CHECKSUM(*) CheckSum, * from TableA) TableA
full outer join (select BINARY_CHECKSUM(*) CheckSum, * from TableB) TableB
on TableA.CHeckSum = TableB.Checksum
where TableA.Checksum is null or TableB.Checksum is null

The full outer join should show any differences between the two tables.

An "EXISTS" Problem

Hey, guys,
Below are my DDL,
CREATE TABLE [dbo].[test1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
CREATE TABLE [dbo].[test2] (
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
test1 table
id A B
--
1 c 1
2 c 2
3 c 3
4 c 4
6 b
9 d
10 e
test2 table
A B
--
c 1
b 1
b 2
d 1
result table
id A B
--
2 c 2
3 c 3
4 c 4
6 b
9 d
Here is my sql to get the result table
SELECT P.id,P.A,P.B
FROM test1 P left outer join test2 R on P.A=R.A
WHERE (NOT EXISTS
(SELECT *
FROM test2 Q
WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
and R.A is not null
group by P.id,P.A,P.B
Can this SQL command be neater?
thanks a lot.
AllenHere are a couple of other methods, although 'cleaner' is a bit subjective'.
Personally, I prefer the NOT EXISTS technique over LEFT JOIN.
INSERT INTO test1 VALUES(1,'c',1)
INSERT INTO test1 VALUES(2,'c',2)
INSERT INTO test1 VALUES(3,'c',3)
INSERT INTO test1 VALUES(4,'c',4)
INSERT INTO test1 VALUES(6,'b', NULL)
INSERT INTO test1 VALUES(9,'d', NULL)
INSERT INTO test1 VALUES(10,'e', NULL)
GO
INSERT INTO test2 VALUES('c', 1)
INSERT INTO test2 VALUES('b', 1)
INSERT INTO test2 VALUES('b', 2)
INSERT INTO test2 VALUES('d', 1)
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
WHERE NOT EXISTS
(
SELECT *
FROM test2 Q
WHERE
P.A = Q.A AND P.B = Q.B
)
GROUP BY P.id, P.A, P.B
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
LEFT JOIN test2 Q ON P.A = Q.A AND P.B = Q.B
WHERE Q.A IS NULL
GROUP BY P.id, P.A, P.B
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allen" <cpchen@.cht.com.tw> wrote in message
news:u19bQ77tDHA.2408@.tk2msftngp13.phx.gbl...
> Hey, guys,
> Below are my DDL,
> CREATE TABLE [dbo].[test1] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> CREATE TABLE [dbo].[test2] (
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> test1 table
> id A B
> --
> 1 c 1
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> 10 e
> test2 table
> A B
> --
> c 1
> b 1
> b 2
> d 1
>
> result table
> id A B
> --
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> Here is my sql to get the result table
> SELECT P.id,P.A,P.B
> FROM test1 P left outer join test2 R on P.A=R.A
> WHERE (NOT EXISTS
> (SELECT *
> FROM test2 Q
> WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
> and R.A is not null
> group by P.id,P.A,P.B
>
> Can this SQL command be neater?
>
> thanks a lot.
> Allen
>

AmoAdventureWorks sample problems.

Can execute the above sample (on XP Pro SP2) successfully once. Subsequent attempts result in error below(after grabbing all system resources for a few minutes). Tried deleting the database and then stop starting the server before retrying - to no avail.

Kind of sapping my confidence in AMO objects.

Any ideas?

Thanks in advance

Zub

--

Unhandled Exception: System.Data.OleDb.OleDbException: Deferred prepare could no

t be completed.

Query timeout expired

at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

at System.Data.OleDb.OleDbDataReader.BuildSchemaTableRowset(Object handle)

at System.Data.OleDb.OleDbDataReader.GenerateSchemaTable(OleDbDataReader data

Reader, Object handle, CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav

ior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(Comman

dBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataT

able datatable, SchemaType schemaType, IDbCommand command, String srcTable, Comm

andBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType sc

hemaType, IDbCommand command, String srcTable, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType sc

hemaType, String srcTable)

at Microsoft.Samples.SqlServer.Program.AddTable(DataSourceView dsv, OleDbConn

ection connection, String tableName) in AmoAdventure

Works\Program.cs:line 239

at Microsoft.Samples.SqlServer.Program.CreateDataSourceView(Database db) in AmoAdventureWorks\Program.cs:line 211

at Microsoft.Samples.SqlServer.Program.CreateAndProcessDatabase(Server svr) in AmoAdventureWorks\Program.cs:line 72

at Microsoft.Samples.SqlServer.Program.Main() in

AmoAdventureWorks\Program.cs:line 48

The sample application assumes you have installed SQL Server on the same machine you have your Analysis Server.

And you have attached sample AdventureWorksDW SQL database. Search for "connectionString" in your project and you be able to point it to any SQL Server you have AdventureWorksDW database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward I'm not sure what you are suggesting.

I have SQL Server Anaylsis Server and AMOAdventureWorks.exe all running on the same machine.

The point is that it works once. The AS database is created, cubes defined and populated. The problem is that subsequent exceutions fail.

Thanks

Zub

|||

The error message suggests problems with connecting to relational database.

AMOAdventureWorks creates a AS database and populates it with data from SQL server you installed locally. Is it possible that SQL server was unavalible?

I've tried to run AMOAdventureWorks several times. And it worked without a problem.

Try running it in the debugger and see exactly where it fails.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward

Thanks for the response.

OK I'm debugging.

Rebooted machine. Ran the program once - it succeeded.

Second time I get a IndexOutofRangeException on the line

DataTable dataTable = dataTables[0];

in

static void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)

{

OleDbDataAdapter adapter = new OleDbDataAdapter(

"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",

connection);

DataTable[] dataTables = adapter.FillSchema(dsv.Schema,

SchemaType.Mapped, tableName);

DataTable dataTable = dataTables[0];

......

when called from

AddTable(dsv, connection, "DimCustomer");

rgds

Zub

|||

Couple things I notice in the code.

1) It works the first time but not the second time. Does your table name exist in DB when you pass in second time?

2) If you pass same table name on second time, it does not make sense because DimCustomer is already in dsv.Schema after you call it first time. This is redundent.

Try replacing with following lines and see if you can get the table everytime you call.

DataSet dataSet = new DataSet();

DataTable[] dataTables = adapter.FillSchema(dataSet,

SchemaType.Mapped, tableName);

3) Anyway, you don't use any AMO object except in the line of doing FillSchema. If you still have problem as my suggestion in Step 2, then it must be the connection problem. Check if the table exists in the DB and valid permission to access that table as well.

|||

Sorry I just notice one more thing.

You got Query time out error on second time. Is your table a big table or a complicated view when you call second time? It seems that it took a lot of time to execute the statement. You may want to increase the connection time out in your connection or set it to unlimited.

Also, you can try the same SQL Statement "Select * from [dbo].[DimCustomer] where 1=0" as New query in SQL Management studio. See how long it takes to get the result.

AMO: Hanging on Partition.Update

I am using AMO to manage partitions.

I am trying to create a partition then use the Update method to create the new partition.. snippet of code below:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

70

71 newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

72 //TODO: Deal with warnings

Every time I run it, it just hangs on the Update method. It seems to be looping.. the query execution will only stop when I restart the SSAS Service.

No errors, no log entries, no Event Log entries.... nada, zilch, vacuum, nothing......

Any ideas anyone?

BTW: This is the SP2 CTP.. I think this might be a bug...|||

It sounds like a bug, it should not hang indefinitely. It would be a good idea to post this on the connect site if it is not already there.

In the mean time, have you tried calling Update() at the cube level, not on the partition object? This is the pattern that the AmoAdventureWorks sample uses (one of the AMO samples distributed with SQL Server), they don't actually call update on the partitions or measure groups, calling update at the cube level causes the changes to all the child objects to get persisted.

|||OK.. I'll give that a whirl... I do seem to be spending an awful lot of time at the connect site lately...|||

Changed the code to the following:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

70

71 cube.Update(UpdateOptions.ExpandFull,UpdateMode.CreateOrReplace);

72 //newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

73 //TODO: Deal with XmlaWarningCollection warnings

But the answer is still no.. it hangs on the update cube method too.. I can see a CommandBegin in the trace with the following XMLA:

<Create AllowOverwrite="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
...

and the details for the new partition is indeed included in the XMLA...but nothing happens .. it is still hanging... no errors, no logs.. the CPU for the SSAS service is zero with the memory stationary.

When I try to look at anything to do with the SSAS database in the SQL Server Management studio, it just hangs.

This is getting serious!

|||

This is probably a silly question, but can I just confirm that you are in fact using the Enterprise edition of SQL Server? Partitions are an Enterprise only feature and if you try to deploy them to a Standard edition server you will get unpredictable results.

I don't know if you have tried this yet, but I have seen one other case where we had symptoms similar to this, simple "alter" statements were causing the server to "hang". We were lucky that this was a dev environment and deleting the database first and then fully re-deploying appeared to fix this, but I have not yet been able to identify what triggered this behaviour.

|||

This is currently the developer edition, SP2 CTP which I am using as a local dev environment. I don't yet have a proper server environment in which to run tests.

From my understanding developer edition has alll the functionality of the Enterprise edition (?)

|||

You are correct. The Developer edition has the same functionality as the Enterprise edition.

I don't know if this is possible, but could you create a partition manually and trace the xmla that is generated using profiler and then run your program and trace that and see if the xmla that is produced is different? That might help highlight something that you might need to add or change to get things working.