Creating Access tables with SQL

I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?uuuuuummmm...

maybe a little more details?

What error, what syntax...|||I think you are looking for the IDENTITY property. It's not a separate data type like in Access, but a property available for certain data types (principally INT) in SQL.

Do a search on Autonumber on this forum. I recently answered a similar question...

Regards,

hmscott|||Originally posted by Brett Kaiser
uuuuuummmm...

maybe a little more details?

What error, what syntax...

Good point, it was a bit vague.

Trying to create a table with field SID as an Autonumber and the primary key.

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="creattable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT NOT NULL
PRIMARY KEY
DEFAULT 1,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"


Which is really helpful as you can see.

Thanks in advance for any help.|||Try SID INT IDENTITY (1,1)

Regards,

hmscott|||Originally posted by hmscott
Try SID INT IDENTITY (1,1)

Regards,

hmscott

Tried it but no joy.|||Did you try it this way?

Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)

regards,

hmscott|||Did a copy and paste on your code, still throws up the same error.
Might have to convince the client to stop using Access.|||Managed to get the answer in another forum, in case anybody is interested the solution is:

SID COUNTER PRIMARY KEY

Thanks for all the help.

Creating Access tables in SQL

I'm using Coldfusion and trying to create a Access table with a autonumber field (SID).

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="createtable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"

Which is really helpful as you can see.

Thanks in advance for any help.Create Table #tbl.code#_Users
(
SID COUNTER PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)|||Thanks for that works a treat.sql

creating access database with tables fail in SSIS package

I'm writing a package for SSIS and need to create a destination access database with a table on the fly. I've tried the code below - whcih works to create a database - but it doesn't create a table in that database to send data to. Instead of tbl.Parentcatalog = cat I've also used cat.Tables.Append(tbl) but this fails with a type problem. What is going wrong here?

private static void CreateDatabase(string currentDirectory)

{

if (!File.Exists(currentDirectory + DESTINATIONNAME))

{

// Create Database

ADOX.Catalog cat = new ADOX.CatalogClass();

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME);

// Need to add columns using CREATE TABLE

ADOX.Table tbl = new ADOX.TableClass();

tbl.Name = "Currency";

tbl.Columns.Append("CurrencyCode", DataTypeEnum.adVarChar, 3);

tbl.Columns.Append("Name", DataTypeEnum.adVarWChar, 16);

tbl.Columns.Append("ModifiedDate", DataTypeEnum.adDate, 24);

tbl.ParentCatalog = cat;

}

}

replaced adVarWChar with DataTypeEnum.adWChar and it worked.

Creating a view with multiple join criteria

This is a silly question but i must present it.
I am creating a view between two tables. Both tables are identified by
two pieces of data:
User ID and Product Name
fooshly those who created the tables did not create the Product name to
be similar. Now i'm trying to join on dissimilar information. I
cannot change the original tables, and i'm stuck making reports trying
to validate information with these joins.
one piece of information. If i can restrict product names to the first
three characters then i can do a successful table join. I can't think
for the life of me how to do it.. any suggestions?Try this:
CREATE VIEW vw_ViewName
WITH SCHEMABINDING
AS
SELECT A.[FieldName], B.[FieldName], (etc.....)
FROM TableA A
JOIN TableB B
ON Left(A.ProductName, 3) = Left(B.ProductName, 3)
AND (A.UserID = B.UserID)
"epikto" <epikto@.gmail.com> wrote in message
news:1142636261.915610.295790@.i39g2000cwa.googlegroups.com...
> This is a silly question but i must present it.
> I am creating a view between two tables. Both tables are identified by
> two pieces of data:
> User ID and Product Name
> fooshly those who created the tables did not create the Product name to
> be similar. Now i'm trying to join on dissimilar information. I
> cannot change the original tables, and i'm stuck making reports trying
> to validate information with these joins.
> one piece of information. If i can restrict product names to the first
> three characters then i can do a successful table join. I can't think
> for the life of me how to do it.. any suggestions?
>|||Excellent. I knew it was simple... Just couldn't find the right syntax
with SQL. Thank you!sql

creating a view with columns not in another table

Hello,

I'm somewhat of a newbie and I need to create a view with a column
that is not derived from any other tables. What I want is for this
field to be an auto-increment field or some kind of UID. Can anyone
quickly tell me how to do this.

Here is my dilemma. I need to pull data from a unique data set from a
table which does not have a primary key, and none exists in its data.
Please tell me how to put this data in a view(or another table) so
that I can pull it out with unique records. It doesnt matter what the
unique field consists of.

ThanksAdd newid() to your select statement.

Select newid(),

This will create a UID for each line returned by the select statement.

margraft@.yahoo.com wrote in message news:<1db47099.0411231136.50d722c7@.posting.google.com>...
> Hello,
> I'm somewhat of a newbie and I need to create a view with a column
> that is not derived from any other tables. What I want is for this
> field to be an auto-increment field or some kind of UID. Can anyone
> quickly tell me how to do this.
> Here is my dilemma. I need to pull data from a unique data set from a
> table which does not have a primary key, and none exists in its data.
> Please tell me how to put this data in a view(or another table) so
> that I can pull it out with unique records. It doesnt matter what the
> unique field consists of.
> Thanks|||> Select newid(),
> This will create a UID for each line returned by the select statement.

If you do twice the request, the UID wont be the same... So it removes a lot
of the interest of the UID. The best thing to do would ne to add a primary
key to the table...

--
Vincent|||(margraft@.yahoo.com) writes:
> I'm somewhat of a newbie and I need to create a view with a column
> that is not derived from any other tables. What I want is for this
> field to be an auto-increment field or some kind of UID. Can anyone
> quickly tell me how to do this.
> Here is my dilemma. I need to pull data from a unique data set from a
> table which does not have a primary key, and none exists in its data.
> Please tell me how to put this data in a view(or another table) so
> that I can pull it out with unique records. It doesnt matter what the
> unique field consists of.

Using newid() as Mike suggested will give you a 16-byte binary value,
which may be somewhat difficult to manage. But it's certainly the
easiest method.

If you want an integer number, the best is probably to write a multi-step
table function where you select the data into a table with an IDENTITY
column, and then return that table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a view to retrieve data from more than one database sql server 2000

Hi everyone,

we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?

Thanks

Have a look at the topic Crossdatabase Ownership chain. It only possible under certain circumstances, the ownership chain cannot be broken.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Creating a view between 2 different Datasources

I want to create a SQL server view that combines 2 tables on 2 different
servers. Both of the servers are in house and I have permission to view
both of them. What is the easiest way to go about doing this?
I am able to create a view with multiple tables on one server but have
never had to create one from multiple datasources.
Let's say that their names are Server_1 and Server_2.
Thanks in advance.
- Will
*** Sent via Developersdex http://www.examnotes.net ***Create a linked server so that Server_1 can see Server_2. Then your view
can work just like any other query:
SELECT <col_list>
FROM databasename.dbo.tablename t1
INNER JOIN [server_2].databasename.dbo.tablename t2
ON t1.key = t2.key;
"Will Chamberlain" <will.chamberlain@.devdex.com> wrote in message
news:uR950C6GGHA.2212@.TK2MSFTNGP15.phx.gbl...
>I want to create a SQL server view that combines 2 tables on 2 different
> servers. Both of the servers are in house and I have permission to view
> both of them. What is the easiest way to go about doing this?
> I am able to create a view with multiple tables on one server but have
> never had to create one from multiple datasources.
> Let's say that their names are Server_1 and Server_2.
> Thanks in advance.
> - Will
> *** Sent via Developersdex http://www.examnotes.net ***

Creating a View

I have 2 tables. T1 is for current data. T2 is a audit tracking table for T1. There will be several records in T2 for each 1 in T1. T2 has a Action Field that stores the last action and a auditID to record changes on T1.

What I want to do is create a view that shows the current records in T1 and all the records in the audit tabel T2. I can do the Join but this would duplicate all the fields.

I am looking for something like this:

Select 'AuditID' AuditID,TD.*,'Action' Action from TrakrDetails TD
--Union
Select TDA.* from TrakrDetails_Audit TDA
order by AuditID desc

This craps out because there are 2 additional Fields in T2.

Any Suggestions?

Thanks
JonSorry but you have to list out all of the columns...

If you want to show columns that aren't in the other table you can use a literal like space, or you can use a null

SELECT ' ' AS Col1,
, Null As Col2
, Col3 FROM myTable99
UNION ALL
SELECT Col1
, Col2
, Col3
FROM myTable00|||Thanks Brett

Your way works.

Normally I would have done it this way but it seemed like it was the long way around (thats the way it normally goes for me).

I thought there might be a easy way I was missing.

Thanks Again
Jon|||As an aside NEVER use SELECT *

(Except for analysis, never for code...save yourself a lot of pain)

Creating a trigger using a cursor

Hi all,
I need to create a trigger on all tables in a database that will insert into
an audit table username, and event on the table. I can create the trigger
individually, but I would like to put this into a cursor so I do not have to
run the trigger 500 times.
I am grabbing all user tables and trying to exec a string within the cursor
to create the triggers. I keep gettin eror by kyword insert. which I believe
is near
" INSERT INTO #inputbuffer"
Below is the code I am using:
TIA,
Joe
declare @.name varchar(100), @.str varchar(8000)
declare crscall cursor for
select name from sysobjects
where type = 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status = 0
begin
declare @.str varchar(8000),@.name varchar(50)
set @.name = 'testrights'
select @.str = 'IF EXISTS (SELECT name FROM sysobjects
WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
'FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll
deallocate crsCAllAre you sure you really want this kind of automation?
Anyway, change the script to print out the query strings instead of just
executing them. Then test them: parse them and attempt to execute them.
And when it's done - I don't want to scare you - you'll still have to test
them 500 times.
ML|||Thank you daniel,
I guess it was just an extra pair of eyes!
The first typo did the trick.
Thanks again.
Joe|||Thank you! this was helpful as well as Daniels.|||This is a one-time thing to create all the triggers so it won't get into
production code.
So good for the poster if he can automate the creation of the triggers.
But I agree with out on the last part, he's still have to test them all.
Maybe he can automate that part too. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8C32588A-D032-40D3-B554-D6A64EB83F15@.microsoft.com...
> Are you sure you really want this kind of automation?
> Anyway, change the script to print out the query strings instead of just
> executing them. Then test them: parse them and attempt to execute them.
> And when it's done - I don't want to scare you - you'll still have to test
> them 500 times.
>
> ML|||If he puts his mind to it, someday his entire life might get automated. :)
He'll have automated himself out of existence.
ML|||That's what I am looking for. Automation is a wonderful thing!|||Well, I wish you good luck on your journey. :)
I hope those 500 tables weren't created automatically by mistake... ;)
ML|||jaylou wrote on Thu, 28 Jul 2005 07:01:13 -0700:

> Hi all,
> I need to create a trigger on all tables in a database that will insert
> into an audit table username, and event on the table. I can create the
> trigger individually, but I would like to put this into a cursor so I do
> not have to run the trigger 500 times.
> I am grabbing all user tables and trying to exec a string within the
> cursor to create the triggers. I keep gettin eror by kyword insert. which
> I believe is near
> " INSERT INTO #inputbuffer"
> Below is the code I am using:
Did you copy and paste that code? If so, there are 2 errors I spotted
straight away, both near the word INSERT. Comments inline, look for Typo #1
and Typo #2.
Dan

> TIA,
> Joe
> declare @.name varchar(100), @.str varchar(8000)
> declare crscall cursor for
> select name from sysobjects
> where type = 'u'
> open crscall
> fetch next from crscall
> into @.name
> while @.@.Fetch_Status = 0
> begin
> declare @.str varchar(8000),@.name varchar(50)
> set @.name = 'testrights'
> select @.str = 'IF EXISTS (SELECT name FROM sysobjects
> WHERE name = '+''''+@.name+'_Audit_InsUpd'+''''+' AND type =
> '+''''+'TR'+''''+')
> DROP TRIGGER Audit_InsUpd'
> exec (@.str)
> select @.str = 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
> ON '+@.name+
> 'FOR INSERT, UPDATE AS
Typo #1. There's no space between ' and FOR, so you'd end up with invalid
syntax here as the table name will be concatenated into FOR and then the
INSERT keyword is invalid as there is no FOR.

> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = '+''''+'DBCC INPUTBUFFER('+ STR(@.@.SPID)+')'+''''+char(13)+
> ' INSERT INTO #inputbuffer
Typo #2. There's a ' missing at the start of this line, so this INSERT won't
be inside the string being assigned to @.str, it's going to be run in the
trigger creating code and #inputbuffer doesn't yet exist as a table.
However, I'm pretty sure the error is due to typo #1 otherwise you'd have
received an error about table #inputbuffer not existing, the compiler might
not be getting this far.

> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> insert into Tbl_MSDBAudit
> select SUSER_SNAME(),@.qry
> END'
> select @.str
> exec (@.str)
> fetch next from crscall
> into @.name
> end
> close crsCAll
> deallocate crsCAll
>|||Hi
Run this Code
declare @.str varchar(8000),@.name varchar(50)
declare crscall cursor for
select name from sysobjects
where type =3D 'u'
open crscall
fetch next from crscall
into @.name
while @.@.Fetch_Status =3D 0
begin
--set @.name =3D 'testrights'
select @.str =3D 'IF EXISTS (SELECT name FROM sysobjects
WHERE name =3D '+''''+@.name+'_Audit_InsUpd'+'=AD'''+' AND type =3D
'+''''+'TR'+''''+')
DROP TRIGGER Audit_InsUpd'
exec (@.str)
select @.str =3D 'CREATE TRIGGER '+@.name+'_Audit_InsUpd
ON '+@.name+
' FOR INSERT, UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr =3D '+''''+'DBCC INPUTBUFFER('+
STR(@.@.SPID)+')'+''''+char(13)+
' INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry =3D (SELECT EventInfo FROM #inputbuffer)
insert into Tbl_MSDBAudit
select SUSER_SNAME(),@.qry
END'
select @.str
exec (@.str)
fetch next from crscall
into @.name
end
close crsCAll=20
deallocate crsCAll=20
With warm regards
Jatinder Singhsql

Creating a Trigger

I have 3 tables on my db, Projects, ProljectAllocationLog and Users

Project consists of Projectid(PK), ProjectName, UserID
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
Users consists of UserID (PK), Fullname

Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,

I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)

Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is

Create Trigger tr_UpdateAllocationLog
ON Projects
AFTER Update
AS
If NOT UPDATE (Userid)

DECLARE @.PROJECTID
DECLARE @.NEWUSER
DECLARE @.PREVIOUSUSER

SET @.PROJECTID= (SELECT projected FROM Inserted)
SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)

If @.NEWUSER <> @.PREVIOUSUSER

INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@.PROJECTID, @.NEWUSER, GETDATE())

Go

I would appreciate any commentssomething like this is a little more graceful and handles multiple updates...

CREATE TRIGGER dbo.trg_ProjectStatusHistory
ON dbo.Project
FOR INSERT, UPDATE
AS
INSERT INTO ProjectStatusHistory(ProjectID,StatusID,SystemStat usID,DateChanged,UpdatedByUIDKey)
SELECT i.ProjectId,i.StatusId,i.SystemStatusId,GETDATE(), i.UpdatedByUIDKey
FROM inserted i
LEFT JOIN deleted d
ON i.ProjectId = d.ProjectID
WHERE i.StatusId <> d.StatusId OR i.SystemStatusId <> d.SystemStatusId
OR (i.StatusId is not null and d.Statusid is null)|||Briliant, thank you very much, I will do it the way you suggested, however, just out of curiosty, would my way have worked even though it was a very clumsey way of doing it?|||yours would not have worked for transactions that updated multiple rows and my memory is a little cloudy on this because I do not code a lot of triggers but I think I had a problem once calling records from one of the virtual tables and then calling records from another like you did here...

SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)

This might not work but I am not sure why. It was a while ago and if something happened more 3 days ago it might as well have never happened at all.|||That will only pull the first ID from inserted, and the first ID from deleted, and there is no guarantee that the two virtual tables will even be sorted identically.

You MUST use set-based operations in triggers.|||I dont understand what you mean by set based operations, can you explain please|||do not mind the blind dude. it's time for his medication.

the trigger in my first post is set based. he did not see it.|||but what does set based mean exactly? (Sorry to sound such a thicko)|||google "set based thinking"

here's one of many links:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx|||This is funny

I just had a request to build a "Project" app

I was so disdaned by the fact that I billed them a boatload and told them to buy MS Project and to send the users to class|||and I bet nobody blinked twice.|||do not mind the blind dude. it's time for his medication.

the trigger in my first post is set based. he did not see it.

I was referring to this:
SET @.NEWUSER = (SELECT UserID from Inserted)
SET @. PREVIOUSUSER = (SELECT UserID from Deleted)
And somebody better be minding the blind dude. Who's turn is it today, anyway? 'Cause nobody made me breakfast.

Creating a table from a view with an if condition.

I'm trying to create a table using a view that links 3 tables (d inner join(
s
join ds)). Is it possible to do an IF condition on line #2 that if the
expression returns a negative value to replace it with a 0 else return the
expression value. I was wondering if there's a way to do the if condition
before the create table other than first creating the table and then do an
update query on the QOH column
select D.*, S.SHORT_SUM_QTY,
(DS.REQD_SUM_QTY - S.SHORT_SUM_QTY) AS QOH
INTO MA_MRP_DS
FROM Select . . .Look at the CASE expression in the books online. It will do this.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"danlin" <danlin@.discussions.microsoft.com> wrote in message
news:A64B882A-9232-4DC8-8D0C-74D9EEC64B3B@.microsoft.com...
> I'm trying to create a table using a view that links 3 tables (d inner
> join(s
> join ds)). Is it possible to do an IF condition on line #2 that if the
> expression returns a negative value to replace it with a 0 else return the
> expression value. I was wondering if there's a way to do the if condition
> before the create table other than first creating the table and then do an
> update query on the QOH column
> select D.*, S.SHORT_SUM_QTY,
> (DS.REQD_SUM_QTY - S.SHORT_SUM_QTY) AS QOH
> INTO MA_MRP_DS
> FROM Select . . .
>

Creating a table from .NET

I need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @.tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.

Here is my stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@.tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END

What went wrong?

Thank you.

DECLARE @.SQL VARCHAR(500)

SET @.SQL = 'CREATE TABLE ' + @.TableName + ' (bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)'

EXEC @.Sql

-- It should be noted that you SHOULD SANITIZE your parameter and make sure that a user does not put anything non-alphanumeric and _ because a malicious user could potentially execute an sql injection if you did not.

If you search for sp_execsql I believe you will find tons of postings.

|||

To be honest, I wouldn't expect the SProc you've listed to work the way you've described in SQL Server either. What you're looking for is dynamic SQL, which has been discussed a number of times in the past weeks in this forum. I suggest you read the information at http://www.sommarskog.se/dynamic_sql.html before you proceed to ensure that you understand the security implications of using SPs with dynamic SQL before implement it.

If the possibility of SQL Injection is not an issue for you, or you've figure out how to mediate it, that same document also has some recomendations on possible implementations.

Mike

|||Thank you both, marcD and Mike.|||Hi,

you should better use the SMO classes which expose an interface (.NET API) for a developer to manage SQL Server objects. You don′t need to care about syntax or semantics, as SMO is object oriented and can be easily used within C# and Visual Studio (with Intellisense) to produce a SQL-injectionfree code (if used the right way :-) ) The API is the successor of the DMO classes, formerly used in SQL Server 2000 and below.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

creating a table column that contains long string

I have made a database inside a C# project using project --> Add New Item --> SQL Database.

I have made its tables and define the columns, but I need a data type that allows inserting a long string , as a news paper .

When I use the 'text' data type , the inserted document can't entered as a whole, only subdocument can be entered.

I need also to know how can I create a table that contains Objects .

of any created class.

Thanks,

Aya.

The text type can hold up to 2GB of data. Should be enough no?

Could you show us your insert procedure?

|||

Ok thanks.

I just detect an error in my inserting process.

But I need to know whether I can create a table that contains Objects or not.

And also if I can create a table that contains lists or not.

Thanks,

Aya.

|||

Hi,

You should be aware that the 'text' datatype is being deprecated, please look at varchar(max)/nvarchar(max) as a replacement, which should give you a better user experience in general.

Could you define what do you mean by Objects and Lists? Maybe there are other ways to achieve what you are trying to do. Please elaborate.

Thanks!

-Mat

Creating a shared ODBC dsn for Access to SQL Server connection

I have a Microsoft Access application which uses linked SQL Server tables. I would like to create an ODBC DSN which would be available to all users so that I don't have to create a DSN on each machine. Can this be done? The Access application resides on a shared drive (Windows). Thanks for your help.

Don,

Will a File DSN work for what you are trying to do? I don't have much experience with those, so I can't help you more in that area.

I too had a similar problem as you do, and I didn't want to go through manually creating a DSN on everybody's machine. I found this script that will make your life slightly easier:

http://www.enterpriseitplanet.com/resources/scripts_win/article.php/3089341

With a little modification to the example included in that web page, I had all my users that needed a DSN ready to rock within a mouse-click!

Thanks,

Chuck

|||Thanks, Chuck. The script will certainly make the process easier if there isn't a way to create a global ODBC DSN. Appreciate the help.

Creating a Select statement with subqueries to 3 other tables...

I have four total tables.

Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields
related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group,
the second field (Stage_ID) is the ID of the record it is associated to
in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields
that is related to this issue. The first field (Stage_ID) is the ID of
the stage of an event, the second record is the ID number associated to
the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it
to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE
(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID
= A.Activity_ID))))))= '20000024')Wenin wrote:
> I have four total tables.
>
> Table One (Documents)- List of Documents. Each record has two fields
> related to this issue. First field (Document_ID) is the ID of the
> document, second field is the ID of the record (Task_ID) it is
> associated to in Table Two.
> Table Two (Activities)- List of activities. Each record has two
fields
> related to this issue. First field (Activity_ID) is the ID of the
> activity, the second field (Group_ID) is the ID of the record it is
> associated to in Table Three.
> Table Three (Groups) - List of groups. Each record has two fields
> related to this issue. First field (Group_ID) is the ID of the
group,
> the second field (Stage_ID) is the ID of the record it is associated
to
> in Table four.
> Table Four (Stages)- List of Event Stages. Each record has two
fields
> that is related to this issue. The first field (Stage_ID) is the ID
of
> the stage of an event, the second record is the ID number associated
to
> the event. This last ID is a known value.
> 20000024 = the Event ID
> I'm trying to come up with a list of Documents from the first table
> that is associated to an Event in the Fourth table.
> Query Analyzer shows no errors within the script. It just doesn't
> return any data. I know that it should, if it does what I'm wanting
it
> to do.
>
> SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
> Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C
WHERE
> (C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE
(B.Activity_ID
> = A.Activity_ID))))))= '20000024')

I'm thinking this is going to require the use of Joins, but I still
can't seem to wrap my head around how joins work exactly.|||First, to save Joe Celko from having to post...

Rows are not records, columns are not fields. You are thinking in the
old sequential file processing mentality. It will take you a year to
unlearn this and get your mind accustomed to thinking in a set-oriented
fashion.

Ok, now that that's out of the way...

You are correct, it will require the use of joins. Please don't take
this as an insult, as I'm sure that you are still learning SQL and
there was a time for all of us when we didn't know it, but this is a
pretty simple set of joins. You really do need to "get your head around
how joins work exactly" if you are going to be doing any SQL coding.
There are plenty of good books for SQL beginners out there. Even the
SQL for Dummies book should get you past this hurdle.

A join takes each table being joined, creates a cartesian product of
the two (i.e., every possible combination of records) then filters that
result based on the ON part of the join. As an example, if I have two
tables:

Customers
cust_id
1
2

Orders
ord_id cust_id
1 1
2 2
3 1

The cartesian product would be:
cust_id ord_id cust_id
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2
2 3 1

If I joined these tables on cust_id = cust_id then it would give me:
cust_id ord_id cust_id
1 1 1
1 3 1
2 2 2

Without the join criteria the cartesian product is useless, but it is
the starting point.

Another way to think of it is this... I know my two tables are related
and I know the column that relates them. Therefore I can join on that
column. That is a VERY simplistic approach to joins, but it will
hopefully point you in the right direction.

Good luck,
-Tom.

Creating a report model based on related entities

I created a report model that has two entities. Both entities, in the SQL
terminology, can have an inner join. Now the two tables do not have any
foreign key relationship or anything. ..
Now when i open the report builder and select a field from one of the entity
the other one just dissapears away. What do i need to do to be able to select
fields from both entities ?The above question is for designing ad hoc reports through the report model.|||It is not visible because when you select an entity then that becomes a
primary entity and remaining is not visible. Now the entities related to the
selected or the primary will be visible. As you said that there is no
relation so it doesn't appear on the list and it wont appear. So you need to
have a relation to be able to select other fields as well.
Amarnath
"Tk_Neo" wrote:
> The above question is for designing ad hoc reports through the report model.|||I have a smilar problem. I have a single data source view which contains 3
tables. Each table is a completely denormalized (like a spreadsheet) dataset
from a particular business system. Within the datasource view editor I
created primary keys on each table and established relationships between each
table. However, when I try to create a report in report builder, as soon as
add an entity from one table to my report, the other 2 tables disappear.
What do I have to do to prevent the tables from disappearing? Please be
specific since I am fairly new to the Ad Hoc component of reporting services.
Thanks.
"Amarnath" wrote:
> It is not visible because when you select an entity then that becomes a
> primary entity and remaining is not visible. Now the entities related to the
> selected or the primary will be visible. As you said that there is no
> relation so it doesn't appear on the list and it wont appear. So you need to
> have a relation to be able to select other fields as well.
> Amarnath
> "Tk_Neo" wrote:
> > The above question is for designing ad hoc reports through the report model.

Creating a Push Subscription on existing database.

My subscriber database has a subset of the tables in the Publisher but,
otherwise the schema is exactly the same.
Using the Push Subscription Wizard and the Initialize Subscription screen,
one is presented with two options: (I am using transactional publication)
-Yes, initialize the schema and data
-No, the Subscriber alreday has the schema and data
If I pick the first option, the initialization will fail because it tries to
drop the tables and views and my tables have relationships and contraints on
them.
If I pick the second option, the stored procedures used to update
(synchronize) the subcriber do not get created in the subcriber data base.
If I create a new database instead, everything works as expected.
How do I create a Push subscription where the table structure is already
there; but I do need to insure the stored procedures required by Replication
get created on the subscriber database?
Bill
William R
if you are running SQL 2k above sp1 do this in your publication database
sp_addpublication 'dummy'
sp_replicationdboption 'pubs','publish','true'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
ePublishingAndWantToGenerateAProcFor'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
rePublishingAndWantToGenerateAProcFor2'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
rePublishingAndWantToGenerateAProcFor3'
sp_addarticle
'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
rePublishingAndWantToGenerateAProcFor4'
sp_scriptpublicationcustomprocs 'dummy'
this will generate the procs you need in the results pane, script them out
and then issue a
sp_droppublication 'dummy'
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> My subscriber database has a subset of the tables in the Publisher but,
> otherwise the schema is exactly the same.
> Using the Push Subscription Wizard and the Initialize Subscription screen,
> one is presented with two options: (I am using transactional publication)
> -Yes, initialize the schema and data
> -No, the Subscriber alreday has the schema and data
> If I pick the first option, the initialization will fail because it tries
to
> drop the tables and views and my tables have relationships and contraints
on
> them.
> If I pick the second option, the stored procedures used to update
> (synchronize) the subcriber do not get created in the subcriber data base.
> If I create a new database instead, everything works as expected.
> How do I create a Push subscription where the table structure is already
> there; but I do need to insure the stored procedures required by
Replication
> get created on the subscriber database?
> Bill
> --
> William R
|||Hilary,
I was wondering if something like this would be the solution. Since there
are so many tables, I could use the create database option to create a dummy
subscriber and copy the procedures over to the real subcriber.
It seems rather odd, MS didn't think of such an option for the wizard though.
Thank you for your response.
Bill
"Hilary Cotter" wrote:

> if you are running SQL 2k above sp1 do this in your publication database
> sp_addpublication 'dummy'
> sp_replicationdboption 'pubs','publish','true'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor','TableNameYouAr
> ePublishingAndWantToGenerateAProcFor'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor2','TableNameYouA
> rePublishingAndWantToGenerateAProcFor2'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor3','TableNameYouA
> rePublishingAndWantToGenerateAProcFor3'
> sp_addarticle
> 'dummy','TableNameYouArePublishingAndWantToGenerat eAProcFor4','TableNameYouA
> rePublishingAndWantToGenerateAProcFor4'
> sp_scriptpublicationcustomprocs 'dummy'
> this will generate the procs you need in the results pane, script them out
> and then issue a
> sp_droppublication 'dummy'
>
> "WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
> news:D46CC819-EFD7-47ED-B39B-BDD090DE4E62@.microsoft.com...
> to
> on
> Replication
>
>
|||That is another way, but it is more work.
I would advise you however to script out the publishing database, create a
database called pub, and a database called sub.
In pub, run the creation script. Then run your publication script (changing
the publication name), and then create and push your subscription to sub.
This way your snapshot generation time will be very very fast and the impact
on your publisher will be low.
"WhiskRomeo" <wrlucasD0N0TSPAM@.Xemaps.com> wrote in message
news:AB0769C1-646E-4406-8B50-68A60BE62109@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I was wondering if something like this would be the solution. Since there
> are so many tables, I could use the create database option to create a
> dummy
> subscriber and copy the procedures over to the real subcriber.
> It seems rather odd, MS didn't think of such an option for the wizard
> though.
> Thank you for your response.
> Bill
>
> "Hilary Cotter" wrote:

Creating a Publication

Hi,
I am trying to create a publication and when i select all tables then there
are certain tables which cannot be published. I get a key with a cross sign
on it.
Whats the reason and how to overcome this? coz I want to publish all tables.
Any help is highly appreciated.
Thanks
pmud
It seems you're using transactional replication and the key with a cross
sign means that this table can't be replicated because it doesn't have a
primary key. This script will list all these tables:
select * from information_schema.tables
where table_type = 'base table'
and objectproperty(object_id(table_name),'IsMsShipped' ) = 0
and objectproperty(object_id(table_name),'TableHasPrim aryKey') = 0
Using this script you can find them, add the necessary PKs and then add them
to your publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the answer. I have a doubt. I have to write this script in query
analyzer after creating the publication? Also , some tables will not have a
primary key. is there no way these tables can be replicated?
Thanks for any help.
pmud
"Paul Ibison" wrote:

> It seems you're using transactional replication and the key with a cross
> sign means that this table can't be replicated because it doesn't have a
> primary key. This script will list all these tables:
> select * from information_schema.tables
> where table_type = 'base table'
> and objectproperty(object_id(table_name),'IsMsShipped' ) = 0
> and objectproperty(object_id(table_name),'TableHasPrim aryKey') = 0
> Using this script you can find them, add the necessary PKs and then add them
> to your publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||This script is to identify those tables requiring a PK and which don't have
one. The PK is mandatory for transactional replication. Otherwise you could
use merge or snapshot for those extra tables. Have a look in books on line
(BOL) for more details of the differences between these types of
replication - this is a good starting point.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul...That was helpful
pmud
"Paul Ibison" wrote:

> This script is to identify those tables requiring a PK and which don't have
> one. The PK is mandatory for transactional replication. Otherwise you could
> use merge or snapshot for those extra tables. Have a look in books on line
> (BOL) for more details of the differences between these types of
> replication - this is a good starting point.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Creating a new measure that only sums data in specific part of cube

I have a cube with about 6 dimensions but only two are important right now. My main fact table is called MRSTATS. The dimension tables are Dim_Client#, and Dim_Test. I would like a measure called HospVol that only aggregates rows where MRSTATS.[Client#]=2. Then I need a measure called HospRVU that multiplies HospVol Measure by Dim_test.RVU. How do I got about configuring this?

Let me know if you need more information or if i'm going at this the wrong way. These values will be used in an SSRS report. Thank you very much.

I'm a little unclear on how you intend to set up your query, so my example may not be quite what you are looking for. Anyway, here is a sample query for AdventureWorks. I've broken down the calculations to make this easier to read:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[List Price] as

IIF(

[Product].[List Price].CurrentMember.MemberValue=0,

NULL,

[Product].[List Price].CurrentMember.MemberValue)

member [Measures].[Bikes Sold] as

[Measures].[Reseller Sales Amount Bikes] /

[Measures].[List Price], format="#,#"

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[List Price],

[Measures].[Bikes Sold]

} on 0,

NONEMPTY [Product].[Product].[Product].Memberson 1

from [Adventure Works]

|||

To clarify things a little bit this is for labratory testing and Dim_Section is the section of the lab where the test was performed, Dim_Test is a table of the different tests and their various billing codes. Dim_Client# is a table of different Clients who we do testing for.

Alright the rows in my table in SSRS is going to be based on members of my Dim_Section Table and then my Dim_Test table and the columns are going to be attributes of the Dim_Test table as well as a few measure groups.

The only measure I have now is Volume which is a sum of the my item_qty field. I want a measure called HospVol which is the summation of the item_qty when the client# = 2 and all so a OtherVol for rows where client# !=2

I have done no work with MDX query text just the BI SSAS interface in SQL2005.

|||

Take a look at this code. The main thing to look at are the expressions at the top. Don't get too concerned with rows and columns just yet. SSRS will "flatten" your queries so that you establish the structure in the report. Main thing is to get the formulas right for you needs.

I wrote these samples against the AdventureWorks sample SSAS OLAP database. I'd recommend working with that database a bit to get comfortable with these concepts before applying them to your cubes.

Good Luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[Reseller Sales Amount Not Bikes] as

AGGREGATE(

EXCEPT(

[Product].[Category].[Category].Members,

[Product].[Category].[Category].[Bikes]

),

[Measures].[Reseller Sales Amount]

)

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[Reseller Sales Amount Not Bikes]

} on 0,

NONEMPTY [Date].[Calendar].[Calendar Year].Memberson 1

from [Adventure Works]

|||

Is there a way to set up my intended measure inside Business Intelligence SSAS without using MDX?

Otherwise could you give me a little insight on what the MDX code is actually used for? Is the MDX code used in building a cube and if so where can i edit the MDX code of the cube. Or is MDX used to run queries against the cube?

This is sort of what I get out of the above query: It sets up two new measures based on one existing measure. I dont know what 'on 0' means at the end of the select statement. The query uses the three measures and aggregates each for each year in the database. (Again, i dont know what 'on 1' means) and then [Adventure Works] is probably the cube name?

Could you direct me to a webpage about MDX?

Thanks for your help and patience.

|||

So, MDX is used for two things: defining expressions and assembling cell sets. The WITH MEMBER portions at the top of the sample are the expression approach. The SELECT ... FROM portion of the sample are the cell set construction part.

The SELECT stuff is only used when building queries. In cube design, you won't take advantage of this stuff. So, if the "on 0" and "on 1" stuff don't make a lot of sense, that's OK for now.

The WITH MEMBER stuff is the foundation for the MDX you would embedded in your cube as calculated members. Take a look at Books Online for topic "CREATE MEMBER statement".

I'm not aware of any books that cover MDX really deep. You may want to browse some of the books on SSAS as they should all cover calculated members and some basic MDX queries.

The best MDX learning resource I've found is this class http://www.hitachiconsulting.com/page.cfm?ID=trainingHandsOnMDXQueries. The schedule for the class is available at http://www.hitachiconsulting.com/page.cfm?ID=trainingSchedule and you would just need to click the email link at the bottom of the page to get the details on how to register. BTW, I need to disclose I work for Hitachi Consulting, the company providing this course.

Good luck,
Bryan

|||

So I think i've found the direction I need to go with this. I think I need to be making a caluculated member to do the aggregations I need. So this is the MDX I have right now based on the example you provided and some templates I found.

Code Snippet

CREATEMEMBERCURRENTCUBE.[MEASURES].[HospVol]

ASAGGREGATE

(

EXCEPT

(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]

),

[Measures].[Item Qty]

)

FORMAT_STRING = "Standard";

The error I'm getting right now is "Error 1 Query (4, 1) Parser: The syntax for 'CREATE' is incorrect. 0 0"

How does this look to you? Thank you so much for your assistance.

|||

For anyone else looking for MDX documentation, here it is:

http://msdn2.microsoft.com/en-us/library/ms145506(SQL.90).aspx

|||

It looks like in the EXCEPT function you are trying to return all the members of your Dim Client dimension's Dim Client hierarchy except the member with a key of 2. Is that correct?

By executing the MEMBERS function against the hierarchy without specifiying the level, you will get the ALL member in your list. So, I would recommend using [Dim Client #].[Dim Client#].[Dim Client#].Members instead to return just the leaf level members and not the ALL level member.

Regarding the error, I don't know exactly what's going on with that. I would recommend creating the calculated member using the form- view of the Cube Designer's Calculations tab. This will help you get the overall syntax correct.

In the form view, you will need to give the calculated member a name, [HospVol]. (Be sure to include the brackets.)

You will need to identify it's parent hierarchy. In this case, select MEASURES.

Next, enter your expression: AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Item Qty])

Then, select your format string and visibility.

Finally, set the Non-Empty Behavior. This one is a little confusing but basically you identify a measure that when the measure is empty, the calculation is not performed. For this calculation, you can probably just ignore that.

Once you've set up the calculated member, just deploy your cube (you don't need to reprocess if the cube is already processed).

Good luck,
Bryan

|||

Hmm, I was going for nothing but Client# 2 so i'll have to look at that. It turns out that i'm going to need the except client# 2 so this one is still useful. I actually am using the form in the cube designer so thats probably why it was giving me the error with the create member.

I put in your expression and it evaluated but the aggregations are wrong because the values are higher than that of the total volume. I have confirmed that [Volume] has correct values so it must be a problem with the calculated member.

Here is my exact expression

Code Snippet

AGGREGATE(

EXCEPT(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

Could it be a problem with how is aggregating? That should be based on [Volume] though correct?

If it clears up anything with the Client# dimension, there are to attributes, [Dim_Client#] and [Org Client]. There's not exactly much of a heirarchy going on.

Would the aggregation of just Client# 2 be:

Code Snippet

AGGREGATE(

FILTER(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

This calculation takes a long time and returns blank cells. Maybe FILTER is the wrong keyword.

Am I going to run into any problems creating calculated members inside a non-measure dimension that multiply an measure by an attribute of the dimension?

Aside from this stuff, I think i'm pretty much ready to take this on by myself. Thanks for all your help

|||

Your expression needs to build a set of members and then cross join it to the measure of interest. This will generate a set of measure values associated with each member. That set can then be aggregated into a single value.

So, in the case where you want to get the set of all members that are NOT client key 2, the EXCEPT expression will give you that. If you want the set of just client 2, then just ask for that one member. Here is the set definition for each of these:

Code Snippet

EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2])

Code Snippet

[Dim Client#].[Dim Client#].&[2]

So, we have a set of members. Now we need to cross join this to the measure of interest, [Measures].[Volumes]. This will give us one measure value for each member in the set. If we have just one member in the set, SSAS can return just that one value. If we have multiple members, we need to aggregate those values to get a single, returnable value.

Code Snippet

AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Volumes])

Code Snippet

([Dim Client#].[Dim Client#].&[2],[Measures].[Volumes])

As for the syntax problems, I'd kinda need to play with the cube to figure that one out. That's why we often use the WITH MEMBER syntax on a SELECT statement to get these things defined before moving them into the cube.

Anyway, hope that helps. Good luck.

Bryan

Creating a new measure that only sums data in specific part of cube

I have a cube with about 6 dimensions but only two are important right now. My main fact table is called MRSTATS. The dimension tables are Dim_Client#, and Dim_Test. I would like a measure called HospVol that only aggregates rows where MRSTATS.[Client#]=2. Then I need a measure called HospRVU that multiplies HospVol Measure by Dim_test.RVU. How do I got about configuring this?

Let me know if you need more information or if i'm going at this the wrong way. These values will be used in an SSRS report. Thank you very much.

I'm a little unclear on how you intend to set up your query, so my example may not be quite what you are looking for. Anyway, here is a sample query for AdventureWorks. I've broken down the calculations to make this easier to read:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[List Price] as

IIF(

[Product].[List Price].CurrentMember.MemberValue=0,

NULL,

[Product].[List Price].CurrentMember.MemberValue)

member [Measures].[Bikes Sold] as

[Measures].[Reseller Sales Amount Bikes] /

[Measures].[List Price], format="#,#"

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[List Price],

[Measures].[Bikes Sold]

} on 0,

NONEMPTY [Product].[Product].[Product].Memberson 1

from [Adventure Works]

|||

To clarify things a little bit this is for labratory testing and Dim_Section is the section of the lab where the test was performed, Dim_Test is a table of the different tests and their various billing codes. Dim_Client# is a table of different Clients who we do testing for.

Alright the rows in my table in SSRS is going to be based on members of my Dim_Section Table and then my Dim_Test table and the columns are going to be attributes of the Dim_Test table as well as a few measure groups.

The only measure I have now is Volume which is a sum of the my item_qty field. I want a measure called HospVol which is the summation of the item_qty when the client# = 2 and all so a OtherVol for rows where client# !=2

I have done no work with MDX query text just the BI SSAS interface in SQL2005.

|||

Take a look at this code. The main thing to look at are the expressions at the top. Don't get too concerned with rows and columns just yet. SSRS will "flatten" your queries so that you establish the structure in the report. Main thing is to get the formulas right for you needs.

I wrote these samples against the AdventureWorks sample SSAS OLAP database. I'd recommend working with that database a bit to get comfortable with these concepts before applying them to your cubes.

Good Luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[Reseller Sales Amount Not Bikes] as

AGGREGATE(

EXCEPT(

[Product].[Category].[Category].Members,

[Product].[Category].[Category].[Bikes]

),

[Measures].[Reseller Sales Amount]

)

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[Reseller Sales Amount Not Bikes]

} on 0,

NONEMPTY [Date].[Calendar].[Calendar Year].Memberson 1

from [Adventure Works]

|||

Is there a way to set up my intended measure inside Business Intelligence SSAS without using MDX?

Otherwise could you give me a little insight on what the MDX code is actually used for? Is the MDX code used in building a cube and if so where can i edit the MDX code of the cube. Or is MDX used to run queries against the cube?

This is sort of what I get out of the above query: It sets up two new measures based on one existing measure. I dont know what 'on 0' means at the end of the select statement. The query uses the three measures and aggregates each for each year in the database. (Again, i dont know what 'on 1' means) and then [Adventure Works] is probably the cube name?

Could you direct me to a webpage about MDX?

Thanks for your help and patience.

|||

So, MDX is used for two things: defining expressions and assembling cell sets. The WITH MEMBER portions at the top of the sample are the expression approach. The SELECT ... FROM portion of the sample are the cell set construction part.

The SELECT stuff is only used when building queries. In cube design, you won't take advantage of this stuff. So, if the "on 0" and "on 1" stuff don't make a lot of sense, that's OK for now.

The WITH MEMBER stuff is the foundation for the MDX you would embedded in your cube as calculated members. Take a look at Books Online for topic "CREATE MEMBER statement".

I'm not aware of any books that cover MDX really deep. You may want to browse some of the books on SSAS as they should all cover calculated members and some basic MDX queries.

The best MDX learning resource I've found is this class http://www.hitachiconsulting.com/page.cfm?ID=trainingHandsOnMDXQueries. The schedule for the class is available at http://www.hitachiconsulting.com/page.cfm?ID=trainingSchedule and you would just need to click the email link at the bottom of the page to get the details on how to register. BTW, I need to disclose I work for Hitachi Consulting, the company providing this course.

Good luck,
Bryan

|||

So I think i've found the direction I need to go with this. I think I need to be making a caluculated member to do the aggregations I need. So this is the MDX I have right now based on the example you provided and some templates I found.

Code Snippet

CREATEMEMBERCURRENTCUBE.[MEASURES].[HospVol]

ASAGGREGATE

(

EXCEPT

(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]

),

[Measures].[Item Qty]

)

FORMAT_STRING = "Standard";

The error I'm getting right now is "Error 1 Query (4, 1) Parser: The syntax for 'CREATE' is incorrect. 0 0"

How does this look to you? Thank you so much for your assistance.

|||

For anyone else looking for MDX documentation, here it is:

http://msdn2.microsoft.com/en-us/library/ms145506(SQL.90).aspx

|||

It looks like in the EXCEPT function you are trying to return all the members of your Dim Client dimension's Dim Client hierarchy except the member with a key of 2. Is that correct?

By executing the MEMBERS function against the hierarchy without specifiying the level, you will get the ALL member in your list. So, I would recommend using [Dim Client #].[Dim Client#].[Dim Client#].Members instead to return just the leaf level members and not the ALL level member.

Regarding the error, I don't know exactly what's going on with that. I would recommend creating the calculated member using the form- view of the Cube Designer's Calculations tab. This will help you get the overall syntax correct.

In the form view, you will need to give the calculated member a name, [HospVol]. (Be sure to include the brackets.)

You will need to identify it's parent hierarchy. In this case, select MEASURES.

Next, enter your expression: AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Item Qty])

Then, select your format string and visibility.

Finally, set the Non-Empty Behavior. This one is a little confusing but basically you identify a measure that when the measure is empty, the calculation is not performed. For this calculation, you can probably just ignore that.

Once you've set up the calculated member, just deploy your cube (you don't need to reprocess if the cube is already processed).

Good luck,
Bryan

|||

Hmm, I was going for nothing but Client# 2 so i'll have to look at that. It turns out that i'm going to need the except client# 2 so this one is still useful. I actually am using the form in the cube designer so thats probably why it was giving me the error with the create member.

I put in your expression and it evaluated but the aggregations are wrong because the values are higher than that of the total volume. I have confirmed that [Volume] has correct values so it must be a problem with the calculated member.

Here is my exact expression

Code Snippet

AGGREGATE(

EXCEPT(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

Could it be a problem with how is aggregating? That should be based on [Volume] though correct?

If it clears up anything with the Client# dimension, there are to attributes, [Dim_Client#] and [Org Client]. There's not exactly much of a heirarchy going on.

Would the aggregation of just Client# 2 be:

Code Snippet

AGGREGATE(

FILTER(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

This calculation takes a long time and returns blank cells. Maybe FILTER is the wrong keyword.

Am I going to run into any problems creating calculated members inside a non-measure dimension that multiply an measure by an attribute of the dimension?

Aside from this stuff, I think i'm pretty much ready to take this on by myself. Thanks for all your help

|||

Your expression needs to build a set of members and then cross join it to the measure of interest. This will generate a set of measure values associated with each member. That set can then be aggregated into a single value.

So, in the case where you want to get the set of all members that are NOT client key 2, the EXCEPT expression will give you that. If you want the set of just client 2, then just ask for that one member. Here is the set definition for each of these:

Code Snippet

EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2])

Code Snippet

[Dim Client#].[Dim Client#].&[2]

So, we have a set of members. Now we need to cross join this to the measure of interest, [Measures].[Volumes]. This will give us one measure value for each member in the set. If we have just one member in the set, SSAS can return just that one value. If we have multiple members, we need to aggregate those values to get a single, returnable value.

Code Snippet

AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Volumes])

Code Snippet

([Dim Client#].[Dim Client#].&[2],[Measures].[Volumes])

As for the syntax problems, I'd kinda need to play with the cube to figure that one out. That's why we often use the WITH MEMBER syntax on a SELECT statement to get these things defined before moving them into the cube.

Anyway, hope that helps. Good luck.

Bryan