How to assign primary key or forign key to existing tables

Hi,

I am new to SQL server. I have one doubt.

I am using SQL server 2005 Express Edition

I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George

Quote:

Originally Posted by sijugeo

Hi,

I am new to SQL server. I have one doubt.

I am using SQL server 2005 Express Edition

I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George

Hi
You can do by searching in sql books online.
Ok......add primary key constraint on one column in one table using alter table add constraint.add refference constraint on one column in other table
example:
[code]

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE doc_exe ADD

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE doc_exe ;
GOsql

how to ask for a tablename in ole db.

How do you ask in ole db which tables do exist or
if a table does exist ?
(Offcourse this should be RDBMS independant,
so I do not want to query 'system' tables or views).
ben brugman
(A pointer to another thread or webpage,
or the right keywords for google would be sufficient
anwser, thanks in advance).OLE DB supports several schema rowsets that return meta data. This and
other schema rowsets are documented in the OLE DB documentation. You can
find more info at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbstartpage1.asp.
The C# example below displays the 'Tables' rowset in a datagrid.
object[] restrictions = { null };
DataTable schemaRowSet = myOleDbConnection.GetOleDbSchemaTable
(
System.Data.OleDb.OleDbSchemaGuid.Tables,
restrictions
);
this.dataGrid1.DataSource = schemaRowSet;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:uxcP3G5$DHA.552@.TK2MSFTNGP11.phx.gbl...
> How do you ask in ole db which tables do exist or
> if a table does exist ?
> (Offcourse this should be RDBMS independant,
> so I do not want to query 'system' tables or views).
> ben brugman
> (A pointer to another thread or webpage,
> or the right keywords for google would be sufficient
> anwser, thanks in advance).
>
>

how to ask for a tablename in ole db.

How do you ask in ole db which tables do exist or
if a table does exist ?
(Offcourse this should be RDBMS independant,
so I do not want to query 'system' tables or views).
ben brugman
(A pointer to another thread or webpage,
or the right keywords for google would be sufficient
anwser, thanks in advance).OLE DB supports several schema rowsets that return meta data. This and
other schema rowsets are documented in the OLE DB documentation. You can
find more info at
http://msdn.microsoft.com/library/d...
artpage1.asp.
The C# example below displays the 'Tables' rowset in a datagrid.
object[] restrictions = { null };
DataTable schemaRowSet =
myOleDbConnection.GetOleDbSchemaTable
(
System.Data.OleDb.OleDbSchemaGuid.Tables,
restrictions
);
this.dataGrid1.DataSource = schemaRowSet;
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:uxcP3G5$DHA.552@.TK2MSFTNGP11.phx.gbl...
> How do you ask in ole db which tables do exist or
> if a table does exist ?
> (Offcourse this should be RDBMS independant,
> so I do not want to query 'system' tables or views).
> ben brugman
> (A pointer to another thread or webpage,
> or the right keywords for google would be sufficient
> anwser, thanks in advance).
>
>sql

How to approach (Trigger-Stored procedure )

Hi All

I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate

I have a trigger on the master table upon deletion to archive. This is
triggered from a seperate routine from a vb app to delete a record
real time.

CREATE Trigger Archive_Proc On dbo.MASTER
For Delete
As
Declare @.iDate As DateTime
Set @.iDate = GetDate()

If @.@.RowCount = 0 Return
set Nocount on
Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select ProductID, ProductNo, @.iDate from deleted

My problem is that I have a temp table that gets filled from a
seperate transaction.It needs to be matched against the master table
then deleted at both master and temp. but the issue is that the temp
table contains its own SoldDate value that needs to be archived.

Q 1: if I use a stored proc. how do i pass the SoldDate value to the
trigger as Triggers dont use GVs.
Q 2: How do I set up the stored procedure to delete with multiple
tables. I can get it to UPDATE but not delete...

CREATE PROCEDURE COMPARESOLD
@.Pool Smallint
AS
Set NoCount on

Update MASTER
Set Released = 2
From TEMP, MASTER
Where TEMP.ProductNo = MASTER.ProductNo
AND TEMP.ProductID = MASTER.ProductID
AND INVENTORY.Released = 1

hopefully someone can lead me to the right direction...
ThanksHi

I have not picked up the exact relationship between temp and the Master
table. Posing correct DDL (using the scripting options in Enterprise Manager
or the QA Object browser) and example data as inserts statements go a long
way to remove this sort of ambiguity.

It seems that you should have a FK from the TEMP table to the Master that
cascades on delete.

It may also be possible to change the trigger so that it picks up the
SoldDate from TEMP such as:

Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select d.ProductID, d.ProductNo, ISNULL(t.SoldDate,@.iDate)
from deleted d LEFT JOIN Temp t on d.ProductID = t.ProductID

You may want something slightly different if there are multiple TEMP
records.

John

"Alvin" <josesievert@.earthlink.net> wrote in message
news:cc27243c.0311061338.45d42f52@.posting.google.c om...
> Hi All
> I need opinions on how to approach my task.
> I currently have 3 tables: the master table, the archive and a temp
> table.
> MASTER: has 3 fields ProductID and ProductNo and Released
> ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
> TEMP: ProductID, ProductNo, SoldDate
> I have a trigger on the master table upon deletion to archive. This is
> triggered from a seperate routine from a vb app to delete a record
> real time.
> CREATE Trigger Archive_Proc On dbo.MASTER
> For Delete
> As
> Declare @.iDate As DateTime
> Set @.iDate = GetDate()
> If @.@.RowCount = 0 Return
> set Nocount on
> Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
> Select ProductID, ProductNo, @.iDate from deleted
>
> My problem is that I have a temp table that gets filled from a
> seperate transaction.It needs to be matched against the master table
> then deleted at both master and temp. but the issue is that the temp
> table contains its own SoldDate value that needs to be archived.
> Q 1: if I use a stored proc. how do i pass the SoldDate value to the
> trigger as Triggers dont use GVs.
> Q 2: How do I set up the stored procedure to delete with multiple
> tables. I can get it to UPDATE but not delete...
> CREATE PROCEDURE COMPARESOLD
> @.Pool Smallint
> AS
> Set NoCount on
> Update MASTER
> Set Released = 2
> From TEMP, MASTER
> Where TEMP.ProductNo = MASTER.ProductNo
> AND TEMP.ProductID = MASTER.ProductID
> AND INVENTORY.Released = 1
>
> hopefully someone can lead me to the right direction...
> Thanks

How to apply complex constraints

Hi all!
I want to create a constraint that uses data from other tables,
specifically i want to make sure that a varchar has exactly the length
specified in an integer-column in a table that I pointed out with a
foreign key.

I would like this to be solved something like this:

create table string_size_limits
(
row_id INTEGER PRIMARY KEY
string_size INTEGER
)

create table strings
(
string_size_limit_row_id INTEGER
REFERENCES string_size_limits(row_id)
string varcher(50)
CONSTRAINT check_string_size CHECK ?
)

Is it possible to solve this problem without SP using above model?
Is it possible to solve this problem with SP using above model?
Must the above problem be solved using triggers?

Any help appreciated"Jon" <jonsjostedt@.hotmail.com> wrote in message
news:9f379edc.0503150952.59caa802@.posting.google.c om...
> Hi all!
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
> I would like this to be solved something like this:
> create table string_size_limits
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
> create table strings
> (
> string_size_limit_row_id INTEGER
> REFERENCES string_size_limits(row_id)
> string varcher(50)
> CONSTRAINT check_string_size CHECK ?
> )
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?
> Any help appreciated

A CHECK constraint can only access data in the table it's created on, but in
your example, what is the purpose of Row_ID as a primary key? In other
words, what is the difference between these two limits:

insert into string_size_limits select 1, 5
insert into string_size_limits select 2, 5

Is the second string size limit somehow different because its row_id is
different? If the primary key of your limits table was just the string size
itself, then the foreign key could be used in the CHECK constraint:

create table dbo.StringSizes (
StringSize int not null,
constraint PK_StringSizes primary key (StringSize)
)

create table dbo.Strings
(
StringSize int not null,
String varchar(50) not null,
constraint PK_Strings primary key (String),
constraint FK_Strings_StringSizes foreign key (StringSize)
references StringSizes (StringSize),
constraint CHK_StringLength check (len(String) = StringSize)
)

insert into dbo.StringSizes select 3
insert into dbo.StringSizes select 5

insert into dbo.Strings select 3, 'Jon'
insert into dbo.Strings select 3, 'John' -- Fails
insert into dbo.Strings select 5, 'Check'
insert into dbo.Strings select 5, 'Cheque' -- Fails

If that doesn't help, I suggest you give some more details on Row_Id, and
also working CREATE TABLE and INSERT statements. But if you can't use the
string size limit itself in the foreign key, a trigger is the most likely
alternative.

Simon|||Jon (jonsjostedt@.hotmail.com) writes:
> I want to create a constraint that uses data from other tables,
> specifically i want to make sure that a varchar has exactly the length
> specified in an integer-column in a table that I pointed out with a
> foreign key.
> I would like this to be solved something like this:
> create table string_size_limits
> (
> row_id INTEGER PRIMARY KEY
> string_size INTEGER
> )
> create table strings
> (
> string_size_limit_row_id INTEGER
> REFERENCES string_size_limits(row_id)
> string varcher(50)
> CONSTRAINT check_string_size CHECK ?
> )
> Is it possible to solve this problem without SP using above model?
> Is it possible to solve this problem with SP using above model?
> Must the above problem be solved using triggers?

The problem does not need be solved with triggers, but that's the best
solution.

The alternative is to write a UDF which access the string_size_limits
table, and then you CHECK constraint would read:

CHECK (len(string) = dbo.maxlen(row_id))

The reason you should not do this, is because the performance penalty
can be severe. I remember that I played with this once, and added a
constraint with a UDF to the copy of an existing table. I then inserted
all 24000 rows into that table. Instead of two seconds it took 30!

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In full SQL-92, you can write such a CHECK() constraint, but not in SQL
Server yet. You would have to use a trigger and get away from
declarative code.

However, why are you putting metadata into the database in violation of
basic design principles? This is sooo wrong.|||"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de
news:1110989814.440585.47880@.o13g2000cwo.googlegro ups.com...
> In full SQL-92, you can write such a CHECK() constraint, but not in SQL
> Server yet. You would have to use a trigger and get away from
> declarative code.
> However, why are you putting metadata into the database in violation of
> basic design principles? This is sooo wrong.

Why is this so wrong? How about a link to thoes basic design principles?
Where do you think metadata should be stored?

How to Alter a table in a trigger?

Hi,everyone.

I have a problem with SQL SERVER 2005, described as follows,

(1) To create two tables MASTERINFO and PRODUCT,

--TABLE MASTERINFO--
CREATE TABLE MASTERINFO
(
ID CHAR(2),
FIELDNAME VARCHAR(50),
FIELDTYPE VARCHAR(50),
);

Table MASTERINFO with following records,

MASTERINFO('1','PRODUCTNAME','VARCHAR(50)');
MASTERINFO('2','PRODUCTADD','VARCHAR(50)');
MASTERINFO('3','PRODUCTEXP','VARCHAR(50)');

--TABLE PRODUCT--
CREATE TABLE PRODUCT
(
ID CHAR(5),
PRODUCTNAME VARCHAR(50),
PRODUCTADD VARCHAR(50),
PRODUCTEXP VARCHAR(50),
);

In our project, field name and field data type of fields PRODUCTNAME,PRODUCTADD and PRODUCTEXP in the table PRODUCT are changed with values of fields FIELDNAME and FIELDTYPE in the table MASTERINFO.That is to say, when using the following UPDATE statement,

UPDATE MASTERINFO SET FIELDNAME='PRODUCTNAME1',FIELDTYPE='VARCHAR(60)' WHEN ID='1';

We hope that field name of field PRODUCTNAME is automatically changed into PRODUCTNAME1 and data type of its is changed into 'VARCHAR(60) in the table PRODUCT.

I want to use a trigger to realize, but I failed for ALTER TABLE statement can not be included in the trigger. How can I do?

Please give me some advice. Thank you in advance.The mere fact that you feel the need to alter your table structure in a trigger demonstrates that you need to rethink your entire database design.

I'm not kidding...

Describe the database application you are trying to build, and maybe we can give you some tips.|||self modifying code can be a lot of fun to debug, don't you think?|||Think you!

In oracle DBMS, we can use EXECUTE IMMEDIATE statement to finsih such a task.However, I can not find the right statement in SQL SERVER 2005.|||who cares how to do it or if you can do it in Oracle? dude, the whole point is if it is a good idea or not and any code that modifies the definition of permanent database objects as part of the application is a terribly bad idea. I believe there is a way to do this in sql server, but I am not going to hand you a loaded gun to shoot yourself with.|||I wonder if you can pop a messagebox from a trigger in oracle? like "I am about to modify your schema. Confirm: yes/no"|||You can't do it through a trigger.

You could conceivably do it through a stored procedure, and under a good database design all interaction with tables would be done via sprocs anyway. But then again, we already know we're not talking about a "best practices" database design here.|||Hi,everyone.
Please give me some advice. Thank you in advance.

Sure, no problem.

Don't do it.

Now, for the fun part.

Why do you want to do this?

How to add sub-categories??

Hi all,

Can any body tell me how to add sub-categories to a database (MS SQL Server 2000) please.

I have a db with the following tables set:
Products: ProductID, CategoryID, ProductName
Categories: CategoryID, CategoryName

If a sub-category needs to be added how would I add it and what fields would it contain and the relationship between the Products and Category Table be?
Also any useful tips on this would be appreciated.

TIATIA,

If the sub-categories are the same as the categories you can just add a SubCategoryID field to your Products table. Then create a relationship between Category.CategoryID and Products.SubCategoryID.

Have some fun.|||Then does that mean i would have to add a Sub-Category Table... and the db would be like this:

Products: ProductsID, CategoryID, SubCategoryID...etc
Category: CatogeryID...etc
SubCategory: SubCategoryID...etc

the category table would have a 1-many relationship with products and a 1-many with sub-categories too?

For instance ... the products table would have books, games, music ...etc as products and books would have computers, history, science as it's sub-cats. games would have ps2, xbox ...etc as it's sub-cats.

So what would be the best way to describe the tabels and there relationships?|||I have found the best way to handle this is to have one table with an added field called 'parentID' or something like that. This value is the key on the row of the 'parent' category. This way, you can have unlimited subcategories.

Products: ProductsID, CategoryID, ...etc
Category: CatogeryID, ParentID, CategoryName, ...etc

Category Table (|seperator)
1 | 1 | 'Food & Clothing'
2 | 1 | 'Clothing'
3 | 2 | 'Shirts'
4 | 2 | 'Pants'
5 | 3 | 'Crew Neck T-Shirts'

Here we see that 'Crew Neck T-Shirts' is a sub category of 'Shirts', which is a sub category of 'Clothing', which is a sub category of 'Food & Clothing'. As you can see, this way allows for unlimited nesting of categories and is the best solution because a sub-category table would have the exact same information as the category table, so why duplicate the effort and over complicate the database?

How to add record thru command button in adp.

Hi,

In access database project where my tables are linked from MSSQL server, how can I add record from the FORM itself using command button. Bcz it is not like mdb files in access that you can just drag a command button onto the form and take an action like add, delete, print or find rec. I just came to know it from immediate window using following SQL line;

Docmd.RunSql "Insert......

This is ok but how it could be done thru a command button on FORM.

Thanks in advance.

With kind regards,
AshfaqueYou can use RunSQL with an ADP file. Or you can call an INSERT procedure on your SQL Server database.|||Hi blindman,

Bcz I am new to adp, can you please extend your help in more detail. I never used adp and its prodedure. How can I do it. Do I need to create a function / procedure? If so. How..Need to know badly. . Help in this regards will be higly appriciated.

Thanks a lot.
Ashfaque|||In an ADP application, no data is stored in Microsoft Access. It is all stored on the back-end server.

You can put code in the modules or forms in the ADP file, but it should be limited to affecting the behavior of the user interface. Your business logic should be implemented in stored procedures, views, and user-defined functions on your database server.

For an explanation of how to write TSQL code for SQL Server, look up VIEWS and STORED PROCEDURES in Books Online.|||Thanks again blindman,

I will check it.

With regards,
Ashfaque

How to add more tables to publishing database in transactional replication

I have setup the transactional replication with one table which is
working fine. I need more tables to publishing database in order to
test it. How to do this?
Thanks in advance
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sp_addarticle. You can also right click on the publication, select
publication properties, click on the articles tab, and then select show
unpublished objects, and then start to add more articles.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lance kepart" <lance4848@.yahoo.com> wrote in message
news:exxUNUsGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> I have setup the transactional replication with one table which is
> working fine. I need more tables to publishing database in order to
> test it. How to do this?
> Thanks in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to add formatting lines and outlines in SRSS tables

I have a table set up in an SRSS report to display my data with subtotals. The data displays perfectly. What I can't seem to make happen, though, is simple display features (under lines above a subtotal, outlines of a group of boxes). If I try to use the line tool it tells me that it can't be placed in this part of the table, but from experiment I gather it can't be placed in ANY part of the table. Also, when I select a three column two row area (in my design) and attempt to apply a border style (hoping to put a border around the cells), the border applies internally as well.

Can SRSS provide this kind of formatting inside of a table?

Jim

Just curious -- is it at all possible to add lines and other formatting items within a table in a SRSS report?

Have you tried setting the border properties for those cells?

>L<

|||

Yes, unfortunately, the border properties hit each and every cell border. I would like to outline three cells (much as I could in Excel) -- but instead, each individual cell is outlined, which is overkill.

Also, I tried using the "overline" property subtotal cells -- hoping to get a subtotal line between the subtotal and the data it is totals, and find that overline doesn't do anything. Underline, strikethrough -- both work -- not overline.

|||

>> hit each and every cell border

Horizontally (if you want to put in a horizontal line, or group a set of boxes horizontally) you should be able to set the individual border elements (top, left, bottom, right) to handle this perfectly. Just don't define the "inner" lefts and rights the same way as the other border elements.

And FWIW that's exactly how Excel does what you are talking about <s>.

If you want to outline a group of cells vertically, you can do this with column borders, it's a little more difficult.

Now that I think about it, perhaps there are some border column formats that are getting in the way of your doing what you want. You might want to check that...

Otherwise... maybe I'm misunderstanding your question?

>L<

|||

yes -- thank you. For some reason I missed the existence of the expansion check to get to the directions.

Sorry for the bother.

How to add formatting lines and outlines in SRSS tables

I have a table set up in an SRSS report to display my data with subtotals. The data displays perfectly. What I can't seem to make happen, though, is simple display features (under lines above a subtotal, outlines of a group of boxes). If I try to use the line tool it tells me that it can't be placed in this part of the table, but from experiment I gather it can't be placed in ANY part of the table. Also, when I select a three column two row area (in my design) and attempt to apply a border style (hoping to put a border around the cells), the border applies internally as well.

Can SRSS provide this kind of formatting inside of a table?

Jim

Just curious -- is it at all possible to add lines and other formatting items within a table in a SRSS report?

Have you tried setting the border properties for those cells?

>L<

|||

Yes, unfortunately, the border properties hit each and every cell border. I would like to outline three cells (much as I could in Excel) -- but instead, each individual cell is outlined, which is overkill.

Also, I tried using the "overline" property subtotal cells -- hoping to get a subtotal line between the subtotal and the data it is totals, and find that overline doesn't do anything. Underline, strikethrough -- both work -- not overline.

|||

>> hit each and every cell border

Horizontally (if you want to put in a horizontal line, or group a set of boxes horizontally) you should be able to set the individual border elements (top, left, bottom, right) to handle this perfectly. Just don't define the "inner" lefts and rights the same way as the other border elements.

And FWIW that's exactly how Excel does what you are talking about <s>.

If you want to outline a group of cells vertically, you can do this with column borders, it's a little more difficult.

Now that I think about it, perhaps there are some border column formats that are getting in the way of your doing what you want. You might want to check that...

Otherwise... maybe I'm misunderstanding your question?

>L<

|||

yes -- thank you. For some reason I missed the existence of the expansion check to get to the directions.

Sorry for the bother.

How to add filter based on query?

Hi guys,

I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.

I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )

I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:

declare @.GeoId int;
set @.GeoID = 200765;

select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000

but how I can make same in SSAS and Reports?

Thanks in advance

I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).

|||

Hi Mosha,

Thank you very much for helping me.

I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?

It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project

Best regards,

Galin

|||

Hi,

Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.

I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.

Daniel.

|||I made .NET function to calculate distance - same as T-SQL one

also I made Longitude and Latitude measures and I could pass them in MDX query

Hope this helps|||

Hi,

Can you provide a sample of your MDX query ?

I was thinking that Lat and Long to be dimensions not measures.

Thnx.

daniel

|||

I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:

Measures(AggregateFunction)

PriceCount (Count),

Latitude (None),

Longitude (None)

Dimension

[Tbl DW Dim Property Type]

but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:

Code Snippet

SELECT

NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,

NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS

FROM [DW DEV]

so I used an .NET sp to capture the data:

Code Snippet

SELECT

NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,

NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS

FROM [DW DEV]

Code Snippet

public static double DoSomething(double x)

{

return x;

}

And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].

I know that I'm missing something but I can't figure.

Any help would be appreciated.

|||

I've managed to run a query like this

Code Snippet

SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,

NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *

[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM

( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,

mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,

'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS

FROM (

SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS

FROM [DEV]))

but i noticed a significant performance degradation.

Any suggestions ?

How to add filter based on query?

Hi guys,

I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.

I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )

I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:

declare @.GeoId int;
set @.GeoID = 200765;

select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000

but how I can make same in SSAS and Reports?

Thanks in advance

I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).

|||

Hi Mosha,

Thank you very much for helping me.

I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?

It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project

Best regards,

Galin

|||

Hi,

Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.

I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.

Daniel.

|||I made .NET function to calculate distance - same as T-SQL one

also I made Longitude and Latitude measures and I could pass them in MDX query

Hope this helps|||

Hi,

Can you provide a sample of your MDX query ?

I was thinking that Lat and Long to be dimensions not measures.

Thnx.

daniel

|||

I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:

Measures(AggregateFunction)

PriceCount (Count),

Latitude (None),

Longitude (None)

Dimension

[Tbl DW Dim Property Type]

but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:

Code Snippet

SELECT

NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,

NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS

FROM [DW DEV]

so I used an .NET sp to capture the data:

Code Snippet

SELECT

NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,

NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS

FROM [DW DEV]

Code Snippet

public static double DoSomething(double x)

{

return x;

}

And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].

I know that I'm missing something but I can't figure.

Any help would be appreciated.

|||

I've managed to run a query like this

Code Snippet

SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,

NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *

[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM

( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,

mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,

'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS

FROM (

SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS

FROM [DEV]))

but i noticed a significant performance degradation.

Any suggestions ?

How to add filter based on query?

Hi guys,

I have the following problem:
I have several tables in SQL 2005 ( table GeographicLocations and other related to it ). I created a cube in SSAS in which GeographicLocations is dimension.

I was able to add parameters but I need something more complicated: Having columns longitude and latitude in GeographicLocations I need to be able to return all locations within X (parameter) miles from passed GeographicLocation (parameter - GeographicLocationID )

I found SQL function that calculates distance from passed longitude and latitude of two points and T-SQL would like this:

declare @.GeoId int;
set @.GeoID = 200765;

select * from GeographicLocations as t1
where dbo.geo_distance(t1.longitude, t1.latitude,
(select longitude from GeographicLocations where id=@.GeoID),
(select latitude from GeographicLocations where id=@.GeoID)) < 2000

but how I can make same in SSAS and Reports?

Thanks in advance

I suggest making Longitude and Latitude related attributes of GeoLocation, and then implement SSAS sproc which computes difference between two points (similar to geo_distance).

|||

Hi Mosha,

Thank you very much for helping me.

I would like to ask you to be more specific as I am newbee in SSAS. Could you point me at some samples and resources that use such approach?

It is kind of blury to me how to create related attributes in such manner (although I read this article ), how to create SSAS stored procedure and how to call it from the report project

Best regards,

Galin

|||

Hi,

Any advise ? I'm dealing with the same problem and I'm also a newbie in SSAS.

I was thinking to make an user defined function in an assembly to compute the distance but I can't figure out if this will be viable.

Daniel.

|||I made .NET function to calculate distance - same as T-SQL one

also I made Longitude and Latitude measures and I could pass them in MDX query

Hope this helps|||

Hi,

Can you provide a sample of your MDX query ?

I was thinking that Lat and Long to be dimensions not measures.

Thnx.

daniel

|||

I've changed the fact table in order to have Latitude and Longitude as Measures; so now I have:

Measures(AggregateFunction)

PriceCount (Count),

Latitude (None),

Longitude (None)

Dimension

[Tbl DW Dim Property Type]

but from tests I've found that performing filter operations on Latitude/Longitude measures it is returning wrong results:

Code Snippet

SELECT

NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,

NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, [Measures].[Latitude]>44.42659335504373) ON ROWS

FROM [DW DEV]

so I used an .NET sp to capture the data:

Code Snippet

SELECT

NON EMPTY { [Measures].[Prices Count] } ON COLUMNS,

NON EMPTY FILTER([Tbl DW Dim Property Type].[Name].Members, MDXClassLibrary.DoSomething([Measures].[Latitude])>44.42659335504373) ON ROWS

FROM [DW DEV]

Code Snippet

public static double DoSomething(double x)

{

return x;

}

And during debugging I’ve found that the sp’s parameter is receiving wrong values not the ones that are in [Measures].[Latitude].

I know that I'm missing something but I can't figure.

Any help would be appreciated.

|||

I've managed to run a query like this

Code Snippet

SELECT NON EMPTY { [Measures].[Prices Avg], [Measures].[Prices Max], [Measures].[Prices Min] } ON COLUMNS,

NON EMPTY { ([Tbl DW Dim Time].[Calendar Year].[Calendar Year].ALLMEMBERS *

[Tbl DW Dim Time].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM

( SELECT ( { FILTER([Tbl DW Dim Gis].[Coord].MEMBERS,

mdxclasslibrary.dist([Tbl DW Dim Gis].[Coord].CurrentMember.Name,

'44.42366640719915000000000','26.07366800308228000000000',1 )>0)}) ON COLUMNS

FROM (

SELECT ({[Tbl DW Dim Property Type].[Name].&[Apartment] } ) ON COLUMNS

FROM [DEV]))

but i noticed a significant performance degradation.

Any suggestions ?

How to add Fill Factor in the SQL script

I am trying to set up the relationship (Primary Key and Forign Key) in several tables. I would like to find a way also be able to set 'Fill Factor = 90%' in the script. Here is the code that i have so far:

ALTER TABLE Req
ADD CONSTRAINT FK_Req_Bow FOREIGN KEY ( BowID )
REFERENCES Bow ( BowID )

Any help would be greatly appreciated!

J827FILLFACTOR pertains to an index that you need to create for your FOREGN KEY constraint, not to the constraint itself.

how to add data to my tables (was "SQL Server Newbie")

Hi. I just set up my first sql server database and I've managed to connect to it via ASP as a test.
I'm not sure how to add data to my tables. In MS Access, you can edit the table and add records. How do I do that in SQL Server?
I'm using the Enterprise manager tool to create tables... does it have something i can use?

ThanksThe sky's the limit on how you can add and edit data in SQL tables, but comparable to what you said about editing tables in access... In EM, you can right click a table then select to open table and return all rows, top or specifiy a query. (I usually use top 1 if I plan on adding rows)

I would advise using query analyzer to do any editing though. Much easier and more efficient IMHO.|||When I try to return rows by right clicking on the table, i get an error that says:
An unexpected error happened during this operation.
Query Designer encountered a Query Error: Unspecified error.

??|||If all else fails, and you have primary keys on your tables, you can always just link the tables in MS Access. As Dr_Clong advised, though, you probably want to pick up a book on Transact-SQL. I liked Sams Publishing's "Teach Yourself in 21 Days" book.|||ok... so it sounds like i have to write sql INSERT statements to add data...??
Sorry if that's a dumb question.
I'll look up some books on transact sql.|||I don't know your setup or for that matter what could be causing your error, but I agree the best way to start out is by educating yourself on SQL Server tools and TSQL.|||1. Stop using Enterprise Manager

2. Start Using Query Analyzer

3. Open Books Online and Leave it Open

4. INSERT INTO myTable99(Col1, Col2, Col3) SELECT 'x','y','z'

5. Or bcp data in from flat files

6. Have a Margarita!

7. Post here often

8. Have Another Margarita

9. Go to 8.

How to Add Columns and Use Them at Run Time using VB 2005

In order to allow my customers to use their existing data and install updated application, I want to be able to add columns to existing CE tables at startup time, and allow the user to enter data into the new column(s) immediately. I used the ALTER TABLE command as shown in the example below:

cmd.CommandText = "ALTER TABLE tblStores ADD dtDemoDate datetime, dtDemoMode int"

cmdexe = cmd.ExecuteNonQuery()

To add the column, But this does not set the columns in the attached dataset. The program sees the columns and data is able to be added, but is not then in the underlying table? What else do I need to do?

Thank you - b2bMan

You must update the dataset to fit the new schema. Distribute an application update together with the code to update the schema.

How to add an SQL to report?

I am using Crystal Reports 9.0.

I would like to link to tables together with fields "unitID" and "PropUnit"

I links the fields but when I add more then one field to the report the data disappears in the preview.

I looked at Crystals sample report. It uses a SQL statement.

How do I add an SQL statement if that's what I need to get this to work.

Thanks,

SteveIs it possible that there are no records satisfying this criteria/join ?

I don't think displaying 3 fields would suppress display of records from preview!

Thanks|||Thanks. This is my 1st try at using CR and that didn't even cross my mine.

Steve

How to add a second table in Report Builder?

Hi

I need a report with two tables. Table one pulls information from one entity in my database and table two pulls info from another entity in the same database.

I built a report model so that, I can access different tables using the same DataSource. But I don't seen an option to add a second table for the same report in Report Builder.

All my server components are on Win2003 server and client components on Windows XP.

I would greatly appreciate any pointers and suggestions on the same.

Thanks

Preps.

In Report Builder you can create report with only one dataregion (table, matrix, chart). But you can load this report into Report Designer and add more data sources and more data regions.

<edit>

|||

More info on creating model-based reports in Report Designer:

http://msdn2.microsoft.com/en-us/ms345642(sql.90).aspx

How to add (pick and choose) items from one table to another

We have two tables (one has DTS import of external spreadsheet - the other is the online table)

The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table).

I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table whereDescription=@.FirstTableFieldText)

I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how).... Even the base structure (pseudo code- whatever) would help

I suppose that you can do it in one insert statement

Insert into SecondTable(partno,ID)

Select partno,thirdtable.ID from firsttable

LEFT JOIN thirdtable

ON thirdtable.description = firsttabledescription

where partno not in (select partno from secondtable)

I do not know exactly your database schema but in this TSQL statement you select records from first table and connect each record to its description ID ( join) and in where statement you select only records which has partno which does not exists in second table (destination).

Is it what you need?

Thanks

JPazgier