Creating views

Hi all,

Bit of a SQL server newbie here, I have two tables; Fish (containing Name and ID) and Fishing (containing just a concatonatted FishingID), but to relate the tables i would have to join (reference) the fields in the Fish table ie: fishname and FishID to match the other tables FishingID.

Every time i concanonate the fishname and FishID in the criteria the view doesent run but if i actually write an example FishingID in the criteria the view runs.

How do i refernce the fields in the criteria section?

JnrYou'll get an answer much quicker if you post the DDL of your tables, ssome sample data and the expected results...

Also what about the SELECT Statement you created...

My telepathic usb port is clogged...|||What is the concatennated ID for?

Something sounds FishyID to me.|||My telepathic usb port is clogged...That is a very good thing!

-PatP|||That is a very good thing!

-PatP

Why is that...I was hoping to use it to do all my typing for instead...|||You and a telepathic anything sounds like a class 3 SLG (spontaneous lawsuit generator) to me. Between the various forms of harassment, the politicially incorrect thoughts being broadcast, and the ensuing chaos from just having your head opened up to the casual passer-by... Egad!

-PatP

creating trigger to auto set create/modify dates

Hi,

I'm a newbie to sql server and this may be a really dumb question for
some you. I'm trying to find some examples of sql server triggers that
will set columns (e.g. the created and modified date columns) if the row
is being inserted and set a column (e.g. just the modified date column)
if the row is being updated.

I know how to do this in oracle plsql. I would define it as a before
insert or update trigger and reference old and new instances of the
record. Does sql server have an equivalent? Is there a better way to do
this in sql server?

Thanks
eric

this is what i do in oracle that i'm trying to do in sqlserver...

CREATE OR REPLACE TRIGGER tr_temp_biu
before insert or update
on temp
referencing old as old new as new
for each row
begin
if inserting then
:new.created_date := sysdate;
end if;
:new.modified_date := sysdate;
end tr_temp_biu;On Thu, 07 Oct 2004 13:02:41 -0400, efinney wrote:

>Hi,
>I'm a newbie to sql server and this may be a really dumb question for
>some you. I'm trying to find some examples of sql server triggers that
>will set columns (e.g. the created and modified date columns) if the row
>is being inserted and set a column (e.g. just the modified date column)
>if the row is being updated.
>I know how to do this in oracle plsql. I would define it as a before
>insert or update trigger and reference old and new instances of the
>record. Does sql server have an equivalent? Is there a better way to do
>this in sql server?
>Thanks
>eric
>this is what i do in oracle that i'm trying to do in sqlserver...
(snip)

Hi Eric,

Don't try to do a one on one translation from Oracle to SQL Server. The
differences are too big (especially when it comes to triggers and other
non-ANSI-standard syntax).

For the creation date, no trigger is needed. Simply use a default. Use the
same default for the modified date if you want that set at the time a row
is inserted as well. If you rather leave the moodified_date NULL until the
row actually is updated, remove the default and change NOT NULL to NULL.

CREATE TABLE MyTable (KeyCol1 int NOT NULL,
KeyCol2 char(6) NOT NULL,
DataCol1 varchar(130) NULL,
DataCol2 datetime NOT NULL,
Created_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
Modified_Date datetime NOT NULL
DEFAULT (CURRENT_TIMESTAMP),
PRIMARY KEY (KeyCool1, KeyCol2)
)

There are no BEFORE triggers in SQL Server. Only AFTER and INSTEAD OF
triggers are supported. In your case, I'd use an AFTER trigger. A very
fundamental difference that you should really be aware of, is that SQL
Server executes a trigger once per update (insert, delete) statement, with
all affected rows in the inserted and deleted pseudo-tables. All Oracle
triggers I've seen so far are processed for each individual row - a very
big difference that can lead to spectacular errors.

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
-- Prevent recursion!
IF NOT UPDATE(Modified_Date)
BEGIN
UPDATE MyTrigger
SET Modified_Date = CURRENT_TIMESTAMP
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.KeyCol1 = MyTable.KeyCol1
AND i.KeyCol2 = MyTable.KeyCol2)
END

(untested)
UPDATE MyTable

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating SSIS Package to Extract Data from MySQL and Append to SQL 2005

Hi guys,

I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.

I hope you guys can help me with this.

Hoping to hear from you soon.

Thank you so much.

Kind regards,
Neil
I'm sure everyone on the forum will be happy to help. I'd suggest starting by going through the SSIS Tutorials in SQL Server Books Online. Then, as you encounter specific problems or design questions, we'll be happy to answer them.|||

Yep, I agree with John.

Just a hint though, you'll be using a DataReader Source against your MySQL ODBC driver to retrieve the results.

|||

Actually, i have gone through the BOL tutorial and have started creating the SSIS package using DataReader Source. i then provided the connection manager which is in my case using ADO.Net connection (MySQL ODBC 3.51 Driver) but as soon as i enter the SQLCommand and click ok this error displays on the bottom part of the Advanced Editor for DataReader Source.

Error at Data Flow Task [DataReader Source[1]]: System.Data.Odbc.OdbcException: ERROR [HY010]

[MySQL][ODBC 3.51 Driver][mysqld-4.1.12-Debian_1ubuntu3.4-log]

What do u think is causing this?

Thanks,

Neil

|||

If you could provide the query, that would help troubleshoot the issue.

If you are trying to use parameters or a complex query, try building it in a variable and using an expression on the data flow to set the SQL source. Expressions for the data flow can be found by clicking the data flow, opeing the properties window (F4), and looking for the expressions item.

|||

Below is the SQLCommand i am using. What's troubling me is that the DTS for this in SQL 2000 is running smoothly that is why i was thinking maybe i am using the wrong steps in SSIS.

SELECT chatrequestlogs.`chat_session`,
chat_admin.`name`,
chatrequestlogs.`ip`,
FROM_UNIXTIME(chatrequestlogs.created) AS Time_Requested,
FROM_UNIXTIME(chattranscripts.created) AS Time_Created,
chatrequestlogs.status,
LTRIM(substring(chattranscripts.plain, 1, locate(':',chattranscripts.plain, 1) - 1)) AS Name_Registered,
LTRIM(substring(substring(chattranscripts.formatted, locate('<question>', chattranscripts.formatted,1), locate('</question>', chattranscripts.formatted, locate('<question>', chattranscripts.formatted, 1)) - locate('<question>', chattranscripts.formatted, 1)),11)) AS Question,
LTRIM(substring(chattranscripts.plain, locate(':', chattranscripts.plain, 1) + 1)) AS Comments,
chatrequestlogs.deptID,
substring(substring(formatted, locate('<tstamp (', formatted), locate(') tstamp>', formatted) - locate('<tstamp (', formatted)), 10) AS Time_Started,
reverse(substring(substring(reverse(formatted), locate('>pmatst )', reverse(formatted)), locate('( pmatst<', reverse(formatted)) - locate('>pmatst )', reverse(formatted))), 10)) AS Time_Ended,
chat_admin.`email` AS CSCEmail,
chattranscripts.`email` AS CustomerEmail
FROM chatrequestlogs
JOIN chat_admin ON chatrequestlogs.userID = chat_admin.`userID`
JOIN chattranscripts ON chattranscripts.chat_session = chatrequestlogs.chat_session
WHERE FROM_UNIXTIME(chatrequestlogs.created) >= DATE_ADD(NOW(), INTERVAL - '1440' MINUTE)
AND FROM_UNIXTIME(chatrequestlogs.created) < NOW()

|||

Hi guys,

The problem went away after I turned the ValidateExternalMetadata property of the datasource reader to false.

Thank you so much for the replies.

Forums like these are of great help to newbies like me.

Keep up the good work guys.

Kind regards,

Creating Log file & Attaching db?

Bit of a SQL newbie, having taken over a support role for a piece of software that my firm sells. It is basically a DB that runs on MSDE or SQL Server in either desktop or enterprise scenarios.

We do sell our software to clients who may or may not already have SQL on their machines. During installation, it searches for sqlservr.exe for existing SQL installations and if it finds nothing, will then install MSDE 1.0 to house the db files.

My question is this -

I can manually create a db or manually attach an existing db through sql query analyser where there is a full-blown SQL installation. Is there any way I can do the same where there is only MSDE installed? The simple solution, I guess, would be to download one of the freeware or shareware MSDE "Enterprise Managers", but this won't always be suitable for a client installation.

Many thanks for your assistance all.

:cool:you could use sp_attach_db to attach a sqlserver database to sqlserver. Don't know about msde though.|||you could use sp_attach_db to attach a sqlserver database to sqlserver. Don't know about msde though.

Thanks

Yep, this is what I would use if I was doing it through the SQL. Still wondering about MSDE tho.
:confused:|||You can look into SQL-DMO and write up a quick routine that would do the attaching.