Create an automated routine to check database definitions

Is there any utility, command, or query in SQL Server, that I can execute
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> > Is there any utility, command, or query in SQL Server, that I can execute
> > from a batch mode to script out all of my database definitions (tables,
> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> > create
> > an automated routine to check database definitions?
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy :)
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
>> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
>> > Is there any utility, command, or query in SQL Server, that I can
>> > execute
>> > from a batch mode to script out all of my database definitions (tables,
>> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
>> > create
>> > an automated routine to check database definitions?
>> I wrote one some time ago, using DMO. I can provide compiled exe or
>> sources
>> if you want. AFAIR works only with integrated security (kind of bug I
>> never
>> really needed to fix). Works fine if you need to monitor database (or all
>> databases) schema.
>> Cheers,
>> Wojtek
>>

create a view that return data and its count of description

I have a view that has a link to 3 tables.
I need to dispaly only the data in one table and list how
many that data is defined in one table.
here is the code for the first view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
All I need is the oilName and its COUNT of cautions.
let say the oil name is "Basil" and it has three counts.
All I need is Basil in one coumn and in the other coulmn
I need "3"
Thank youWithout DDL, I'm taking some guesses, but try:
SELECT o.oilName, count(*) AS CautionCount
FROM Oils AS o
join OilCautions AS oc
on o.OILID = oc.OILID
group by o.oilName
HTH
Vern
>--Original Message--
>I have a view that has a link to 3 tables.
>I need to dispaly only the data in one table and list how
>many that data is defined in one table.
>here is the code for the first view.
>USE Aromatherapy
>GO
>if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
>= 'V')
> DROP VIEW
> Oils_Cautions_View
>go
>CREATE VIEW Oils_Cautions_View AS
>SELECT o.oilID, oilName,
>Description FROM Oils AS o, Cautions as c, OilCautions as
>oc
>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>All I need is the oilName and its COUNT of cautions.
>let say the oil name is "Basil" and it has three counts.
>All I need is Basil in one coumn and in the other coulmn
>I need "3"
>Thank you
>.
>|||Hi Vern,
Thank you very much for your time
Cristian
>--Original Message--
>Without DDL, I'm taking some guesses, but try:
>SELECT o.oilName, count(*) AS CautionCount
>FROM Oils AS o
>join OilCautions AS oc
> on o.OILID = oc.OILID
>group by o.oilName
>HTH
>Vern
>>--Original Message--
>>I have a view that has a link to 3 tables.
>>I need to dispaly only the data in one table and list
how
>>many that data is defined in one table.
>>here is the code for the first view.
>>USE Aromatherapy
>>GO
>>if exists (select name from sysobjects
>> where name = 'Oils_Cautions_View' and
type
>>= 'V')
>> DROP VIEW
>> Oils_Cautions_View
>>go
>>CREATE VIEW Oils_Cautions_View AS
>>SELECT o.oilID, oilName,
>>Description FROM Oils AS o, Cautions as c, OilCautions
as
>>oc
>>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>>All I need is the oilName and its COUNT of cautions.
>>let say the oil name is "Basil" and it has three counts.
>>All I need is Basil in one coumn and in the other
coulmn
>>I need "3"
>>Thank you
>>.
>.
>

Create a variable type TABLE

I’ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. .The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TESTE]

ON[dbo].[TABTESTE]

FOR INSERT

AS

DECLARE

@.YearTablenvarchar(4),

@.IdClientINT,

@.MyTableTABLE

(

IdClientINT,

SituNVARCHAR(50)

)

BEGIN

SET NOCOUNT ON;

SELECT @.YearTable = SITUACAO, @.IdClient = IdClient FROM INSERTED

SET @.MyTable = 'TABLE' & @.YearTable

UPDATE@.MyTable

SET

Situ= 'X'

WHEREIdClient = @.IdClient

END

GO

Erros:

Msg 156, Level 15, State 1, Procedure TESTE, Line 9

Incorrect syntax near the keyword 'TABLE'.

Msg 137, Level 15, State 1, Procedure TESTE, Line 17

Must declare the scalar variable "@.MyTable".

Msg 1087, Level 15, State 2, Procedure TESTE, Line 18

Must declare the table variable "@.MyTable".

I don't have much experience working with triggers but here's a shot...

-Try changing the @.MyTable to varchar(100)

-Declare another variable @.sql varchar(500)

set @.sql = 'update ' + @.MyTable + ' set situ = '''X''' where IdClient = ' + @.IdClient

exec (@.sql)

You may need to play with the number of single quotes around X to get the string to build correctly.

|||

can you explain what are you trying to do with this statement.

SET @.MyTable = 'TABLE' & @.YearTable

Are you trying to concatenate the string?

|||It seems to me that he thinks that TABLE variables are some kind of references/pointers or interfaces, and that he can use TABLE var with the existing table ('TABLE' & @.YearTable) of the identical structure as TABLE var.
I guess that he, in fact, wants to create and execute some dynamic SQL string based on the inserted values.|||TABLE var is a table just like any "normal" table, it resides in the memory or in tempdb. It si ont some kind of reference!
You can't set it to a string, just like you can't do that with any "normal" table.

Are you trying to update the appropritate table ('TABLE' & @.YearTable) depending on the inserted value (@.YearTable = SITUACAO ... FROM INSERTED)?

|||

hi MauricioBogo,

declare @.MyTable Table

(

IdClient int,

Suit nvarchar(50)

)

after you declare @.MyTable as a "Table"

in this Transaction, the @.MyTable is a "local database object" Already.

not a variable value.

this is why you can't do this → " SET @.MyTable = 'TABLE' + @.Yeartable "

and update @.MyTable .

you can try this, as below:

declare @.sSQL varchar(Max)

declare @.Mytable varchar(255)

select @.yeartable = SITUACAO , @.IDClient = IDClient From Inserted

set @.MyTable = 'TABLE' + @.Yeartable

set @.sSQL = 'update ' + @.MyTable + ' set Situ ='X'
exec (@.sSQL)

--or--

or add any script you need.

try it.

hoping this can help.

Best Regrads,

Hunt.

|||

Yes, I'm trying to concatenate the string, but I already changed to + . Right ?

Thanks

|||

Hunt

Ok it works, thanks

Mauricio

|||

Table variables need to be declare alone:

DECLARE

@.YearTable nvarchar(4),

@.IdClient INT

DECLARE

@.MyTable TABLE

(

IdClient INT,

Situ NVARCHAR(50)

)

sql

Create a sql stement from existing tables

Is there any way to automatically create a "CREATE TABLE" statment using an
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

create a script to display all the rows of the view

I have a view that has a link to 3 tables.
I need to display all the rows of the view.
here is the code for the view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
I like to thank you ahead for the help
Thank you
Cristianselect * from Oils_Cautions_View
"Cristian" <vitanc@.hotmail.com> wrote in message
news:0c9901c392a4$2903c2b0$a001280a@.phx.gbl...
> I have a view that has a link to 3 tables.
> I need to display all the rows of the view.
> here is the code for the view.
> USE Aromatherapy
> GO
> if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
> = 'V')
> DROP VIEW
> Oils_Cautions_View
> go
> CREATE VIEW Oils_Cautions_View AS
> SELECT o.oilID, oilName,
> Description FROM Oils AS o, Cautions as c, OilCautions as
> oc
> Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
> I like to thank you ahead for the help
> Thank you
> Cristian
>|||1. Create DB Links OLE DB SQL Links for all the 3 servers.
say:
DBLINK1 points to Server1.employee
DBLINK2 points to Server2.employee
DBLINK3 points to Server3.employee
create view v_employee as
select * from dblink1...employee
union
select * from dblink2...employee
union
select * from dblink3...employee
go
select * from v_employee
There is no need for 'Hardcoding' the db servername.
If server needs to changed, you change it at the Link creation time.

Create a new table from 2 diffrent tables

Hello Newsgroup,
I have two tables(table1, table2) with diffrent fields and I want to create
a new table (newtable) with fields from table1 and table2
Example:
table table1
(
T1field1 char (4),
T1field2 char (4),
)
table table2
(
T2field1 char (4),
T2field2 char (4),
T2field3 char (4),
)
How can I do a query who can give me the result in a new table
like here:
table newtable
(
T1field1 char (4),
T1field2 char (4),
T2field1 char (4),
T2field2 char (4),
T2field3 char (4),
)
Thank you for youre time
WilliWhat are the keys? What do the tow tables have in common? What are the
relationships?
Please either post complete DDL of the tables, or at least include a
description of the relationship(s) between the two tables.
ML
http://milambda.blogspot.com/|||Iam not sure whether i understood your requirement... what iyou are asking
for is simple...
Select * INTO table from table1,table2
This wont make much sense, do u have any connecting feild between these two
tables.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Willi Kolmbach" wrote:

> Hello Newsgroup,
> I have two tables(table1, table2) with diffrent fields and I want to creat
e
> a new table (newtable) with fields from table1 and table2
> Example:
> table table1
> (
> T1field1 char (4),
> T1field2 char (4),
> )
> table table2
> (
> T2field1 char (4),
> T2field2 char (4),
> T2field3 char (4),
> )
>
> How can I do a query who can give me the result in a new table
> like here:
> table newtable
> (
> T1field1 char (4),
> T1field2 char (4),
> T2field1 char (4),
> T2field2 char (4),
> T2field3 char (4),
> )
> Thank you for youre time
> Willi
>
>|||Thank you for the fast reply !
Willi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:61B7DBE9-85E0-4C26-9AEA-24BC01E320E7@.microsoft.com...
> Iam not sure whether i understood your requirement... what iyou are asking
> for is simple...
> Select * INTO table from table1,table2
> This wont make much sense, do u have any connecting feild between these
> two
> tables.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Willi Kolmbach" wrote:
>

Create a log from insert and delete tables

How can I create a log with informations about create and delete tables from
my database?
I use a SQl Server 2000 and Visual Interdev where I can acess all my databas
e
using Data Connection creating, modifying and deleting tables.
I wanna create a log with the information about create and delete tables to
know what is happen in my database.> How can I create a log with informations about create and delete tables
> from
> my database?
> I use a SQl Server 2000 and Visual Interdev where I can acess all my
> database
> using Data Connection creating, modifying and deleting tables.
> I wanna create a log with the information about create and delete tables
> to
> know what is happen in my database.
The SQL Profiler, a tool shipped with SQL Server, is your friend here. Do
please check it in Books OnLine - I think this is what you need.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

create a local (portable) app plus data

We have an Access app that uses tables from an SQL server. Is it possible to
copy the SQL tables localy into the Access mdb file and run the app locally?
What about table relationships? How can I tell if they might be an issue?
Some relationships are built in the diagram tool on the server, and some are
coded into the app itself.
Thanks a heap - Randy
Randy,
Why don't you use MSDE (http://www.microsoft.com/sql/msde/default.mspx )
instead of Access? MDSE is SQL Server, desktop edition, and it is free. This
way you could have exactly the same structure in local databases as you have
on SQL Server.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
news:1382ED81-4064-4B05-956C-7DD5EF2D08FA@.microsoft.com...
> We have an Access app that uses tables from an SQL server. Is it possible
> to
> copy the SQL tables localy into the Access mdb file and run the app
> locally?
> What about table relationships? How can I tell if they might be an issue?
> Some relationships are built in the diagram tool on the server, and some
> are
> coded into the app itself.
> Thanks a heap - Randy
|||That sounds like a really good idea! I did a little looking around at the
link you gave me, but I have maybe a simple question - what is the process
like to create the MSDE localized database? Just an overview - is it about a
1 hour thing or about a 1 or more day thing? Is there a tool to do it?
Thanks again,
Randy
"Dejan Sarka" wrote:

> Randy,
> Why don't you use MSDE (http://www.microsoft.com/sql/msde/default.mspx )
> instead of Access? MDSE is SQL Server, desktop edition, and it is free. This
> way you could have exactly the same structure in local databases as you have
> on SQL Server.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
> news:1382ED81-4064-4B05-956C-7DD5EF2D08FA@.microsoft.com...
>
>
|||Randy,
I am not quite sure whether I understand the question. If you mean the
process of creating a local copy of the server database, it is quite simple:
you can script all objects from the server db in Enterprise Manager and then
execute the script in Query Analyzer on the MSDE, and you have a local copy
with the same structure, but without data. if you need a copy of the data as
well, you can use Data Transformation Services, Merge Replication, ...
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
news:62706E3A-F34B-4ABF-AA75-11600E920014@.microsoft.com...[vbcol=seagreen]
> That sounds like a really good idea! I did a little looking around at the
> link you gave me, but I have maybe a simple question - what is the process
> like to create the MSDE localized database? Just an overview - is it
> about a
> 1 hour thing or about a 1 or more day thing? Is there a tool to do it?
> Thanks again,
> Randy
> "Dejan Sarka" wrote:
|||At this point, you can download SQL Express 2005 (free also) and distro that
with your app.
William Stacey [MVP]
"RandyNesst" <RandyNesst@.discussions.microsoft.com> wrote in message
news:62706E3A-F34B-4ABF-AA75-11600E920014@.microsoft.com...[vbcol=seagreen]
> That sounds like a really good idea! I did a little looking around at the
> link you gave me, but I have maybe a simple question - what is the process
> like to create the MSDE localized database? Just an overview - is it
> about a
> 1 hour thing or about a 1 or more day thing? Is there a tool to do it?
> Thanks again,
> Randy
> "Dejan Sarka" wrote:

Create a database based on an existing one?

I want to create one databse with exactly the same tables as another, is that possible?Yes, you can use the Transfer object to create a script that recreates all objects in a target database. See the ScriptTransfer() method.

Create a cube in analysis services out of more than one fact table.

Is it possible to create a cube in analysis services that has columns from
more than one fact table as long as the fact tables share the same data
grain and dimensions?
Thanks for any advice.
Regards,
Anthony
Hi Anthony,
In AS 2005, this is support via the UDM.
In AS 2000, create two Cubes (one from each fact table) then create a
virtual cube to link the two.
If the facts are at the same grain and have the same dimensions, consider
moving the measures into one fact table.
regards
Jamie
"anthony garcia" <acgarcia21@.verizon.net> wrote in message
news:ZISch.7793$Ga7.2309@.trnddc01...
> Is it possible to create a cube in analysis services that has columns from
> more than one fact table as long as the fact tables share the same data
> grain and dimensions?
> Thanks for any advice.
> Regards,
> Anthony
>

Create a cube in analysis services out of more than one fact table.

Is it possible to create a cube in analysis services that has columns from
more than one fact table as long as the fact tables share the same data
grain and dimensions?
Thanks for any advice.
Regards,
AnthonyHi Anthony,
In AS 2005, this is support via the UDM.
In AS 2000, create two Cubes (one from each fact table) then create a
virtual cube to link the two.
If the facts are at the same grain and have the same dimensions, consider
moving the measures into one fact table.
regards
Jamie
"anthony garcia" <acgarcia21@.verizon.net> wrote in message
news:ZISch.7793$Ga7.2309@.trnddc01...
> Is it possible to create a cube in analysis services that has columns from
> more than one fact table as long as the fact tables share the same data
> grain and dimensions?
> Thanks for any advice.
> Regards,
> Anthony
>

Create 3 users tables

Hello,
In my web site I have 2 types of users: students and professors.
All users need to login but the personal information for each user type
has different fields.
So I create 3 tables: professors, students and authentication,
"professors" will have all the personal data from professors.
"students" will have all the personal data from students.
authentication will have the username and password from all users.
How can i link authentication table to both professors and students
tables so when the user logins i get the data from that user, either a
professor or student?
And is the right way to do this?
I am using SQL 2005 and Asp.Net 2.0
Thanks,
MiguelOn Wed, 21 Dec 2005 23:10:00 +0000, Miguel Dias Moura wrote:

>Hello,
>In my web site I have 2 types of users: students and professors.
>All users need to login but the personal information for each user type
>has different fields.
>So I create 3 tables: professors, students and authentication,
>"professors" will have all the personal data from professors.
>"students" will have all the personal data from students.
>authentication will have the username and password from all users.
>How can i link authentication table to both professors and students
>tables so when the user logins i get the data from that user, either a
>professor or student?
>And is the right way to do this?
>I am using SQL 2005 and Asp.Net 2.0
>Thanks,
>Miguel
Hi Miguel,
I would use one Persons table to hold information that applies to both
professors and students. Specific information then goes in the students
and the professors tables.
Login information could go in the Persons table, but is probably better
placed in a seperate table (for reasons of security, but also for
flexibility - maybe, someone will someday need two seperate accounts,
then you'll be ready for it).
The tables Persons, Students and Professors all have the same primary
key; this same column is also foreign key in the Students and Professors
table, in both cases referencing the Persons table.
The Logins table has userid as primary key (of course) and has also a
foreign key that referenced the Persons table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create .sdf from Existing SQL Server 2005 DB

Hello ,

1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?

So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,

Rookie

Some of the tools from www.primeworks-mobile.com should be able to help you. Also, you can pull the tables from the server with RDA, but you may have to recreate constraints.

Excel data: One solution would be to import Excel data to a SQL Server table, and use the options described above.

|||

Hello Erik,

Thanks for the info on tool.

Hope future versions of SQL management studio come with this functionality.

Thanks,

Rookie

|||According to MS, a "Data Migration Wizard" will appear in the SQL Compact 3.5 timeframe, which should be abel to perform schema and data migration.|||Refer to http://sourceforge.net/projects/sqlceviewer/
It is a utility to do just what you want.

Create .sdf from Existing SQL Server 2005 DB

Hello ,

1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?

So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,

Rookie

Some of the tools from www.primeworks-mobile.com should be able to help you. Also, you can pull the tables from the server with RDA, but you may have to recreate constraints.

Excel data: One solution would be to import Excel data to a SQL Server table, and use the options described above.

|||

Hello Erik,

Thanks for the info on tool.

Hope future versions of SQL management studio come with this functionality.

Thanks,

Rookie

|||According to MS, a "Data Migration Wizard" will appear in the SQL Compact 3.5 timeframe, which should be abel to perform schema and data migration.|||Refer to http://sourceforge.net/projects/sqlceviewer/
It is a utility to do just what you want.

Create .sdf from Existing SQL Server 2005 DB

Hello ,

1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?

So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,

Rookie

Some of the tools from www.primeworks-mobile.com should be able to help you. Also, you can pull the tables from the server with RDA, but you may have to recreate constraints.

Excel data: One solution would be to import Excel data to a SQL Server table, and use the options described above.

|||

Hello Erik,

Thanks for the info on tool.

Hope future versions of SQL management studio come with this functionality.

Thanks,

Rookie

|||According to MS, a "Data Migration Wizard" will appear in the SQL Compact 3.5 timeframe, which should be abel to perform schema and data migration.|||Refer to http://sourceforge.net/projects/sqlceviewer/
It is a utility to do just what you want.
sql

Crazy SQL statement help needed... converting the rows to columns (sort of)...

This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).

Employees table:
empName
Jane Doe
Alton Brown
John Doe

TrainingCourse table:
courseName
Welding
Brain Surgery
Scuba Diving

Results table:
empName: courseName: completeDate:
Jane Doe Welding 2/2/2002
Jane Doe Brain Surgery 3/7/2005
Alton Brown Scuba Diving 9/23/2004
Alton Brown Welding 11/4/2004
John Doe Brain Surgery 6/14/2003

End result of select statement:
Welding Brain Surgery Scuba Diving
Jane Doe 2/2/2002 3/7/2005
Alton Brown 11/4/2004 9/23/2004
John Doe 6/14/2003

Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.

--For SQL Server 2005, there is a pivot function to do this

select pt.empName, pt.[Welding]as [Welding], pt.[Brain Surgery]as [Brain Surgery],pt.[Scuba Diving]as [Scuba Diving]

from dbo.coursePivot$as t

pivot(min(t.completeDate)for t.courseNamein([Welding], [Brain Surgery],[Scuba Diving]))as pt

--For SQL Server 2000 use case

SELECT pvt.empName

,MAX(CASE pvt.courseNameWHEN'Welding'THEN completeDateEND)AS [Welding]

,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN completeDateEND)AS [Brain Surgery]

,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN completeDateEND)AS [Scuba Diving]

FROM dbo.coursePivot$AS pvt

GROUPBY pvt.empName

--Either way, you need hardcode your course names to get the pivot result table.

|||Thank you very much for your reply. I will give this a shot.|||

Okay, I just got a chance to try this, but how do I get this to work with multiple tables (I'm using 2000)? All examples I find don't really make that distinction. For example how does it determine where to get the pvt.empName from? Should it be:

SELECT dbo.Employees.empName

,MAX(CASE pvt.courseNameWHEN'Welding'THEN dbo.Results.completeDateEND)AS [Welding]

,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN dbo.Results.completeDateEND)AS [Brain Surgery]

,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN dbo.Results.completeDateEND)AS [Scuba Diving]

FROM dbo.coursePivot$AS pvt

GROUPBY pvt.empName

Thanks Again.

|||

"how do I get this to work with multiple tables (I'm using 2000)? "

Could you post the tables with a set of data along with your expect result? That would be easy to solve and explain. Thanks.

|||

One other thing: I get this error "The Query Designer does not support the CASE SQL construct."

|||Yes. But it will run.|||

Thanks limno for all your help. Because of the amount of work that has to be done in order to get this to work on the real tables, doing this using a pivot would still require us to run about 5000 queries, and it would remove the dynamic needs of the system, such as having to add new columns manually. My team and I have thought about it and are trying to come up with a way to query the database and create an multidimensional array of the course names and IDs, query the employee database and get all of the employee IDs and names, then finally, query the results table and get the employee ID, the course ID, and date (again, stored in a multidimensional array. I can then come up with a way to compare the values within a function that draws the table (I'll have to figure that out as well). The idea is that memory is cheaper than server processing from an efficiency standpoint.

So I now have two new questions:

1. I know how to store a dataset as an array, but how do I store a dataset as a multidimensional array?
2. (non-Sql): How can I compare, say the second values of multidimensional arrays, meaning if I have two multidimensional arrays, like [1,3] and [7,3], how can I check to see if the two 3's are equal?

Thanks again for all the help so far and to anyone willing to share more insight.

|||

Take a look at this article on my site:http://www.theabstractionpoint.com/dynamiccolumns.asp

This is one approach I have used. It is built for SQL Server 2005 (using dynamic SQL and the PIVOT statement available in 2005), but you could adjust it to dynamically generate the CASE statements you've already been shown. It might give you some ideas, at least.

Crazy selection help

Hi everyone -

I have two tables, one table (A) that holds a field called location that
has the partial path information of the file

and the second table (B) holds the full path including the filename

I only want to display the records from the partial path (A)
table that do not reside in the second (B) table


Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog\2006_02","Marey, John","Prog\2006_02\96.jpg"
"274.JPG","disney","Prog\2006_07","Amy Gross","Prog\2006_07\274.JPG"
"570.jpg","sdfsdf","Prog\2007_06","Lof3,"Test3","Prog\2007_06\570.jpg"
"850.JPG","222","Prog\2007_08","Malis, Mark","Prog\2007_08\850.JPG"

Table B =
imagefilename
"d:\webdata\web\uploads\qfimages\Prog\2006_02\96.jp g"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\112.p df"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\127.j pg"

I was thinking about using a cross join with a like condition,
but it fails (go figure)

could someone offer a little help to the query that i need to
perform??

thanks
tonyuse a LEFT OUTER JOIN from A (left table) to B (right table)

the ON condition will match columns (using LIKE or string functions or whatever)

the WHERE clause will test the PK of the right table being NULL (indicating no match found)|||Why don't you post the SQL you have tried so far?|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.location|||DECLARE @.TableA table (imagefilename varchar(100), [description] varchar(100)
, directory varchar(100), companyname varchar(100), location varchar(100))

DECLARE @.TableB table (imagefilename varchar(100))

INSERT INTO @.TableA (imagefilename, description, directory, companyname, location)
SELECT '96.jpg','test','Prog\2006_02','Marey, John','Prog\2006_02\96.jpg' UNION ALL
SELECT '274.JPG','disney','Prog\2006_07','Amy Gross','Prog\2006_07\274.JPG' UNION ALL
SELECT '570.jpg','sdfsdf','Prog\2007_06','Lof3,Test3','Pr og\2007_06\570.jpg' UNION ALL
SELECT '850.JPG','222','Prog\2007_08','Malis, Mark','Prog\2007_08\850.JPG'

INSERT INTO @.TableB(imagefilename)
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_02\96.j pg' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\112. pdf' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\127. jpg'

SELECT 'TableA' AS Source, Location
FROM @.TableA
UNION ALL
SELECT 'TableB' AS Source, imagefilename
FROM @.TableB b
WHERE NOT EXISTS (SELECT * FROM @.TableA a
WHERE b.imagefilename LIKE '%'+a.location)|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.locationnow change it to a LEFT OUTER JOIN and add this --

... where tbl1.location IS NULL|||GREAT !!!!

you folks are the best!

thank you x10000

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
Dan
There will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in
>
|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of[vbcol=seagreen]
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in
>
|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>
|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.4ax.com... [vbcol=seagreen]
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables
>

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
DanThere will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.
4ax.com...
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
>
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables[vbcol=seagreen]
>

CR9 - Link String field to Number Field

Hey,

I have two tables used within a report one therough btrieve and the other odbc.

Within each table there is a field called {emp.no}. However, one field is located within a Jobshop database and is a string field and the other is located within a TimeLOG database and is a number field.

Is there anyway of linking these fieds within crystal? I dont belive its possible with the Database Expert. But maybe elsewhere?

The report feeds through from the Jobshop (string) field and I need to match records within the TimeLOG (number) database to those within Jobshop based on emp.no and a date field.

Any help much appreciated.

Regards

RobSee the help on the ToText and ToNumber functions.
You should be able to use these in the Record Selection formula.