Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topic Creating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topicCreating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need assistance with SQL query

Hi,

I have a table which I want to analyse and get the following results from:

The table contains is a helpdesk table and contains information on a job, who it is allocated to and when it was submitted.

I'm trying to break down the table and find out what jobs are submitted to a certain group of people and the length of time the job was sitting there.

So for example if there are 10 jobs in the queue, I want to be able to say ok out of the 10 jobs, 5 of them were logged in the last 7 days, 2 were logged in the last fortnight and the rest in the last month.

SELECT ticket_id, priority,submitted_on,deadline,assigned_to
FROM pt6hd_ticket_view
WHERE (NOT status = 'closed' and (assigned_to = 'sean' or assigned_to = 'andrew' or assigned_to = 'matthew' or assigned_to = 'kevin' or assigned_to = '!service_desk' or assigned_to = 'belinda'))
ORDER BY submitted_on ASC

The above is my code so far...

Any help would be appreciated.. thanks.very decent way of doing this in the arena of data warehousing or other wise is to have a table to define week,quarter,half yearly,yearly fields
and then you can run joins to run between any two dates/weekly or etc... I guess it would be of help.. Sorry I cant give you any code as it is boring to code some old sql...|||Hi,
Your explanation and the example is a bit confusing.
Still as per my understanding.

if ur table is TRY with the following structure

Name Null? Type
---------- --- --
TID NUMBER
SUBMITiON DATE
DEADLINE DATE
ASSIGNEDTO VARCHAR2(4)
STATUS VARCHAR2(1)
PRIORITY NUMBER

The following query will give the GROUP ASSIGNED TO, NUMBER OF DAYS PENDING FROM , NUMBER OF JOBS Pending.

SELECT ASSIGNEDTO ,COUNT(TID), TRUNC(SYSDATE-SUBMITION) FROM TRY
WHERE STATUS !='C'
GROUP BY ASSIGNEDTO , TRUNC(SYSDATE-SUBMITiON);

I hope this helps
Regards

need assistance with project

I have a project that is going to use ADP as a front end and SQL server as
the database. Now I need to import flat text files to a temptable in SQL. I
been reading that I should use DTS packages but unfor, from what I have
heard, DTS can only be triggered if scheduled. I am creating a form where th
e
user will have to trigger it. Wonder if there are any other suggestions out
there. Right now, what I created is a macro in ADP that will call a batch
file on the drive that will open up an MDB database and import the files tha
t
way onto sql. Few problems I have right now
1) I have a form where the user clicks on "Locate files" and a subform
opens. It shows all the files in that particular directory. Right next to
each files there is an upload check box. If it check box is ckecked, then
files will be uploaded. There are 2 drop downs in the subform where user wil
l
have to fill in. What I need is if
the check box has been checked but either or both drop downs hasn't been
filled that an error message should pop up before procedure can execute. I a
m
tryin to have to msg box be like a list of all the files that is missing a
drop down. My message box right now only tells one file name but not the
others, if applicable.
for instance, if there are 7 files tha are to be uploaded and 2 of them i
didn't have any drop downs for, i have a message to say "file name text 1 an
d
text 8 are missing..."
2) everytime I open up that database, I get SQL server login error
Connection Failed
SQL state '28000'
Login failed for user(null). Reason, not associated with trusted SQL server
connection.
If i click on "ok" connection pops up and I have to manually enter the
infor. Can I somehow add this in a vb code so user wont' have to keep
manually typing info.
3) Currently, after the import, the MDB kills itself and you are back in the
ADP. Wonder if before the MDB kills itself, opens up an form in ADP and then
kills. THe form is a summary form that shows all the data that was just
imported.(if any other way, suggestions are welcomed).
please help"Justin" <Justin@.discussions.microsoft.com> wrote in message
news:F4E455A7-49AC-4E29-8178-9FE43071C905@.microsoft.com...
>I have a project that is going to use ADP as a front end and SQL server as
> the database. Now I need to import flat text files to a temptable in SQL.
> I
> been reading that I should use DTS packages but unfor, from what I have
> heard, DTS can only be triggered if scheduled. I am creating a form where
> the
> user will have to trigger it. Wonder if there are any other suggestions
> out
> there. Right now, what I created is a macro in ADP that will call a batch
> file on the drive that will open up an MDB database and import the files
> that
> way onto sql. Few problems I have right now
> 1) I have a form where the user clicks on "Locate files" and a subform
> opens. It shows all the files in that particular directory. Right next to
> each files there is an upload check box. If it check box is ckecked, then
> files will be uploaded. There are 2 drop downs in the subform where user
> will
> have to fill in. What I need is if
> the check box has been checked but either or both drop downs hasn't been
> filled that an error message should pop up before procedure can execute. I
> am
> tryin to have to msg box be like a list of all the files that is missing a
> drop down. My message box right now only tells one file name but not the
> others, if applicable.
> for instance, if there are 7 files tha are to be uploaded and 2 of them i
> didn't have any drop downs for, i have a message to say "file name text 1
> and
> text 8 are missing..."
> 2) everytime I open up that database, I get SQL server login error
> Connection Failed
> SQL state '28000'
> Login failed for user(null). Reason, not associated with trusted SQL
> server
> connection.
> If i click on "ok" connection pops up and I have to manually enter the
> infor. Can I somehow add this in a vb code so user wont' have to keep
> manually typing info.
> 3) Currently, after the import, the MDB kills itself and you are back in
> the
> ADP. Wonder if before the MDB kills itself, opens up an form in ADP and
> then
> kills. THe form is a summary form that shows all the data that was just
> imported.(if any other way, suggestions are welcomed).
> please help
There are a number of options for executing DTS packages. They don't have to
be scheduled and they can be run from your code or using the DTSRUN.EXE
executable. See Books Online for details of DTSRUN. See the following link
for other options:
http://www.sqldts.com/default.aspx?104
Depending on the format of your source file another possibility could be to
use BCP or BULK INSERT to load the data. Again, see BOL for details.
For the rest of your questions you might get more help in an Access group.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need Assistance with Dynamic Properties and FTP Task

Hello,

I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:

CDNSC.CDNSC.SC00015.04012007

The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.

My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.

What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?

Thank you for your help!

cdun2On Apr 4, 5:35 am, "cdun2" <ChrisDunnM...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:
>
CDNSC.CDNSC.SC00015.04012007
>
The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.
>
My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.
>
What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?
>
Thank you for your help!
>
cdun2

I wound up creating a script solution that was pretty straight
forward. I basically set up string variables using DATE to get the
data portion of the file name, concatenated that to the first part of
the filename, and set the whole string equal to the SourceFileName of
the FTP Task. It seems to work fine.

cdun2sql

Need assistance with Duplicate Select Statment

Any help would be appreciated here. My question is two part:

1.) Select duplicates that match any of a number of columns for example...

The email is the same OR

The homephone is the same OR

The mobilephone is the same OR

The address1 is the same

The uniqueID is ConsIntID

My Select Statement: (Which does not work)

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS

WHERE CONSINTID IN (SELECT CONSINTID

FROM CONSULTANTS

HAVING COUNT(HOMEPHONE) > 1 OR COUNT(MOBILEPHONE) >1 OR etc.... )

-

Once the Select Duplicates Statement is corrected I will insert those into a ConsultantsDupe Table

The Second Question is: If I have more then one duplicate my stored_procedure fails

Here is my cursor and logic. The problem is getting multiple results in the subquery and I don't know the best way to correct this.

DECLARE DUPES CURSOR FOR

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS_DUPS

ORDER BY CREATEDATE DESC

OPEN DUPES

FETCH NEXT FROM DUPES

INTO @.CONSINTID, @.FIRSTNAME, @.LASTNAME, @.EMAIL1, @.ADDRESS1, @.HOMEPHONE, @.MOBILEPHONE, @.CREATEdATE, @.USERIDS

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.FOUNDCONSINTID = (SELECT DISTINCT CONSINTID FROM CONSULTANTS_DUPS

WHERE CONSINTID <> @.CONSINTID AND HOMEPHONE = @.HOMEPHONE "NEED TO ADD COMPARISON FOR MOBILEPHONE, EMAIL ADDRESS HERE") <== This can provide more then one result which messes me up.

I'll take a couple shots at this. Hopefully somebody can post something even better. You'll have to forgive me. Without any sample tables I had to "shoot from the hip" and write it without any syntax checking.

SELECT * FROM Consultants C

WHERE EXISTS (SELECT * FROM Consultants CPhone

WHERE (C.HomePhone = CPhone.HomePhone

OR C.HomePhone = CPhone.CellPhone

OR C.CellPhone = CPhone.HomePhone

OR C.CellPhone = CPhone.CellPhone)

AND C.CONSINTID <> CPhone.CONSINTID

)

OR EXISTS (SELECT * FROM Consultants CEmail

WHERE C.EMAIL1 = CEmail.EMAIL1

AND C.CONSINTID <> EMAIL1.CONSINTID)

OR EXISTS ...

Here's another shot:

SELECT * FROM Consultants

WHERE HomePhone IN (SELECT HomePhone

FROM Consultants

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT CellPhone

FROM Consultants

WHERE CellPhone IS NOT NULL

GROUP BY CellPhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

This method suffers from the fact that you can't check HomePhone and CellPhone at the same time (with UNION ALL) because of a contact has the same phone number listed as home and cell then it could show up as a duplicate.

Here's where you might take the previous query as a natural transition to cross-check home phone against cell phone:

SELECT * FROM Consultants C

WHERE HomePhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

You may also be better off with multiple SELECT statements as it's hard to tell the cause of your duplicate in a query like this. Keep in mind that all of these queries are very read-intensive as you're dealing with multiple scans across your table. You may benefit, instead, by placing your duplicates in a temporary table (or table variable) and then querying against the temporary table.

|||something like this:

select *
from consultants
where consintid in
(select consintid
from consultants
group by email
having count(*)>1
union all
select consintid

from consultants

group by homephone

having count(*)>1

union all
select consintid

from consultants

group by cellphone

having count(*)>1

union all
select consintid

from consultants

group by address1

having count(*)>1
)|||

Jared,

Yours worked! This gives me what I need for the duplicate temp table and the assistance is greatly appreciated.

Any feedback on the second portion?

Need assistance with Data Flow Task error - please help

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

One of the error messages indicates that the Flat File source will fail when the data in column 0 is truncated. Have you tried setting the source to not fail when data is truncated?

1. Right-click the Flat File Source component on the Data Flow tab in SSIS, and then click Edit.

2. Click Error Output in the left-hand pane.

3. In the Truncation drop-down list, select Ignore failure.

4. Click OK.

Also, if you're configuring the Flat File Connection Manager to use a specified code page (see Error 0xC02020A1 above), try configuring the Connection Manager for Unicode:

1. Right-click the Flat File Connection Manager, and then click Edit.

2. Select the Unicode checkbox.

|||

Actually i was having the same rpoblem and i was playing with diferent options.

Go to your Flat File Source in Connection Manager

Go to Advanced and verify outputColumn width and match with your table datatype / the size of data you have it on file

make it sure you donot want to ignore the data just becasue of some errors .

Satish Shrikhande

satish_isi@.hotmail.com

|||Yeah, also watch out for NULL (hex 00) characters in your string fields. NULLs terminate DT_STR and DT_WSTR data types -- even if there is more data after the NULL character.

Need assistance with Data Flow Task error - please help

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

One of the error messages indicates that the Flat File source will fail when the data in column 0 is truncated. Have you tried setting the source to not fail when data is truncated?

1. Right-click the Flat File Source component on the Data Flow tab in SSIS, and then click Edit.

2. Click Error Output in the left-hand pane.

3. In the Truncation drop-down list, select Ignore failure.

4. Click OK.

Also, if you're configuring the Flat File Connection Manager to use a specified code page (see Error 0xC02020A1 above), try configuring the Connection Manager for Unicode:

1. Right-click the Flat File Connection Manager, and then click Edit.

2. Select the Unicode checkbox.

|||

Actually i was having the same rpoblem and i was playing with diferent options.

Go to your Flat File Source in Connection Manager

Go to Advanced and verify outputColumn width and match with your table datatype / the size of data you have it on file

make it sure you donot want to ignore the data just becasue of some errors .

Satish Shrikhande

satish_isi@.hotmail.com

|||Yeah, also watch out for NULL (hex 00) characters in your string fields. NULLs terminate DT_STR and DT_WSTR data types -- even if there is more data after the NULL character.

Need assistance restoring MS SQL 6.5 databases

We had a server running NT 4 and SQL 6.5, it was running an RMS database. We
attempted to move the databases to SQL 7.0 using the upgrade wizard and then
move them to SQL 2000 on another server. This all worked correctly, however
the client app that accesses the data cannot do so on the SQL 2000 box
(probably not compatible). So now we want to revert back to the old SQL 6.5
box however we can longer get SQL 6.5 to start, it is giving the error "
Cannot start SQL, cannot find the path specified'. Our next move was to build
a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
What we need is step by step instructions on how to accomplish this.
Alternatively, our first choice would be to restore the original server
however as stated we cannot get SQL 6.5 to start. Any assistance would be
appreciated
Hi,
With SQL 2000, set the database compatibility mode to SQL 6.5 and try your
application.
Execute the below command from query analyzer
sp_dbcmptlevel 'DBNAME',65
replace the dbname with actual.
If you still have issues then you can Install sql6.5 in Winnt and restore
from backup.
If you do not have backup then With DAT files you have use :-
Use the DISK REINIT and DISK REFIT commands.
Steps:-
1. Restart in Single-user mode
2. DISK REINIT and DISK REFIT against each device
3. Restart in normal mode
See books onlne for command syntax
Thanks
Hari
SQL Server MVP
"webby" <webby@.discussions.microsoft.com> wrote in message
news:6B14351A-B3E5-4130-8CBD-C16A5B80240A@.microsoft.com...
> We had a server running NT 4 and SQL 6.5, it was running an RMS database.
> We
> attempted to move the databases to SQL 7.0 using the upgrade wizard and
> then
> move them to SQL 2000 on another server. This all worked correctly,
> however
> the client app that accesses the data cannot do so on the SQL 2000 box
> (probably not compatible). So now we want to revert back to the old SQL
> 6.5
> box however we can longer get SQL 6.5 to start, it is giving the error "
> Cannot start SQL, cannot find the path specified'. Our next move was to
> build
> a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
> What we need is step by step instructions on how to accomplish this.
> Alternatively, our first choice would be to restore the original server
> however as stated we cannot get SQL 6.5 to start. Any assistance would be
> appreciated

Need assistance restoring MS SQL 6.5 databases

We had a server running NT 4 and SQL 6.5, it was running an RMS database. We
attempted to move the databases to SQL 7.0 using the upgrade wizard and then
move them to SQL 2000 on another server. This all worked correctly, however
the client app that accesses the data cannot do so on the SQL 2000 box
(probably not compatible). So now we want to revert back to the old SQL 6.5
box however we can longer get SQL 6.5 to start, it is giving the error "
Cannot start SQL, cannot find the path specified'. Our next move was to build
a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
What we need is step by step instructions on how to accomplish this.
Alternatively, our first choice would be to restore the original server
however as stated we cannot get SQL 6.5 to start. Any assistance would be
appreciated :)Hi,
With SQL 2000, set the database compatibility mode to SQL 6.5 and try your
application.
Execute the below command from query analyzer
sp_dbcmptlevel 'DBNAME',65
replace the dbname with actual.
If you still have issues then you can Install sql6.5 in Winnt and restore
from backup.
If you do not have backup then With DAT files you have use :-
Use the DISK REINIT and DISK REFIT commands.
Steps:-
1. Restart in Single-user mode
2. DISK REINIT and DISK REFIT against each device
3. Restart in normal mode
See books onlne for command syntax
Thanks
Hari
SQL Server MVP
"webby" <webby@.discussions.microsoft.com> wrote in message
news:6B14351A-B3E5-4130-8CBD-C16A5B80240A@.microsoft.com...
> We had a server running NT 4 and SQL 6.5, it was running an RMS database.
> We
> attempted to move the databases to SQL 7.0 using the upgrade wizard and
> then
> move them to SQL 2000 on another server. This all worked correctly,
> however
> the client app that accesses the data cannot do so on the SQL 2000 box
> (probably not compatible). So now we want to revert back to the old SQL
> 6.5
> box however we can longer get SQL 6.5 to start, it is giving the error "
> Cannot start SQL, cannot find the path specified'. Our next move was to
> build
> a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
> What we need is step by step instructions on how to accomplish this.
> Alternatively, our first choice would be to restore the original server
> however as stated we cannot get SQL 6.5 to start. Any assistance would be
> appreciated :)

Need assistance restoring MS SQL 6.5 databases

We had a server running NT 4 and SQL 6.5, it was running an RMS database. W
e
attempted to move the databases to SQL 7.0 using the upgrade wizard and then
move them to SQL 2000 on another server. This all worked correctly, however
the client app that accesses the data cannot do so on the SQL 2000 box
(probably not compatible). So now we want to revert back to the old SQL 6.5
box however we can longer get SQL 6.5 to start, it is giving the error "
Cannot start SQL, cannot find the path specified'. Our next move was to buil
d
a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
What we need is step by step instructions on how to accomplish this.
Alternatively, our first choice would be to restore the original server
however as stated we cannot get SQL 6.5 to start. Any assistance would be
appreciated Hi,
With SQL 2000, set the database compatibility mode to SQL 6.5 and try your
application.
Execute the below command from query analyzer
sp_dbcmptlevel 'DBNAME',65
replace the dbname with actual.
If you still have issues then you can Install sql6.5 in Winnt and restore
from backup.
If you do not have backup then With DAT files you have use :-
Use the DISK REINIT and DISK REFIT commands.
Steps:-
1. Restart in Single-user mode
2. DISK REINIT and DISK REFIT against each device
3. Restart in normal mode
See books onlne for command syntax
Thanks
Hari
SQL Server MVP
"webby" <webby@.discussions.microsoft.com> wrote in message
news:6B14351A-B3E5-4130-8CBD-C16A5B80240A@.microsoft.com...
> We had a server running NT 4 and SQL 6.5, it was running an RMS database.
> We
> attempted to move the databases to SQL 7.0 using the upgrade wizard and
> then
> move them to SQL 2000 on another server. This all worked correctly,
> however
> the client app that accesses the data cannot do so on the SQL 2000 box
> (probably not compatible). So now we want to revert back to the old SQL
> 6.5
> box however we can longer get SQL 6.5 to start, it is giving the error "
> Cannot start SQL, cannot find the path specified'. Our next move was to
> build
> a NT 4 box, install SQL 6.5 and attempt to restore, we have the .DAT file.
> What we need is step by step instructions on how to accomplish this.
> Alternatively, our first choice would be to restore the original server
> however as stated we cannot get SQL 6.5 to start. Any assistance would be
> appreciated

need assistance on connecting to sql server

Connect to Sql Server[vbcol=seagreen]
Connect using:
[ ] Window authentication
[ ] Sql Server authentication
Login Name : [____________]
Password : [____________]
----
--
I want to get the above senario for sql server name and connect using
different authentication from dos mode using Visual dialog script or winbatc
h
program.
Is there any way to get the following from dos prompt or using sql command
or using registry key --
* server name
* connect using >>>
*-- Window authentication
*-- Sql Server authentication
if Sql Server authentication
then check for user and password
--
Makes Yor Life Easy,MicrosoftHey i got it guys .,.,take it easy .,i found the way in osql
in command prompt type the following steps
if you want the connection in window authentication
c:\>osql -E -i *.txt -o *.txt -n -dmaster
if you want the connection in sql authentication
c:\>osql -U username
password :
> select * from master
>exit
Or
c:\>osql -Uusername -Ppassword -i *.txt -o *.txt -n -h-1 -dmaster
Makes Yor Life Easy,Microsoft
"MrRAO" wrote:

> Connect to Sql Server
> Connect using:
> [ ] Window authentication
> [ ] Sql Server authentication
> Login Name : [____________]
> Password : [____________]
> ----
--
> I want to get the above senario for sql server name and connect using
> different authentication from dos mode using Visual dialog script or winba
tch
> program.
> Is there any way to get the following from dos prompt or using sql command
> or using registry key --
> * server name
> * connect using >>>
> *-- Window authentication
> *-- Sql Server authentication
> if Sql Server authentication
> then check for user and password
> --
> Makes Yor Life Easy,Microsoft

Need assistance on conditional update Trigger

I need some help here in creating a conditional update trigger. The purpose of this trigger would check to see if a contact already exist in the database on an insert and update only the fields that are null.

So How would I compare each field from the CONTACTS Table against my INSERTED Table?

Inserted.FirstName (COMPARE) Contacts.Firstname

Inserted.LastName (COMPARE) Contacts.LastName

Inserted.Email (COMPARE) Contacts.Email

I will be using the email address as the check for the duplicate record and if a duplicate is found... Instead of not allowing the insert I want to compare the existing record and update any fields that are NULL in Contacts with Inserted.

I have no idea on how to compare all of the fields.

Any help appreciated.

sadler_david@.yahoo.com

You could do the following in an INSTEAD OF trigger:

create trigger merge_contact

instead of insert

on dbo.Contacts

as

begin

update dbo.Contacts

set FirstName = coalesce(FirstName, i.FirstName),

LastName = coalesce(LastName, i.LastName)

from inserted as i

where i.Email = dbo.Contacts.Email

insert into dbo.Contacts (FirstName, LastName, Email)

select i.FirstName, i.LastName, i.Email

from inserted as i

where not exists(select * from dbo.Contacts as c

where c.Email = i.Email)

end

Ideally, you need to perform the operations in serializable transaction isolation level to avoid duplicate inserts.

Need Assistance Creating a stored procedure

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@.sccnet.com

Thanks,
ShawnHi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.

Regards,

Tyler
"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> Hi,
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
> Please email me at shawnf@.sccnet.com
> Thanks,
> Shawn|||Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn

"Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@.news.datasync.com...
> Hi Shawn,
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
> Regards,
> Tyler
> "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > Hi,
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> > Please email me at shawnf@.sccnet.com
> > Thanks,
> > Shawn|||It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@.newsreader.goldenga te.net...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >|||Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@.newsreader.goldengate.ne t>...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >

Need assistance

Am a newbie to SQL QUERIES (reporting) - Please assist
Scenario:
I have a simple table where I have columns like
empno
empname
latedate
reasonlate
justification
This table contains mulitple data for a single empno as you can see the
structure
for eg:
empno empname latedate reasonlate justification
101 Kevin 10/1/2005 Business Training day 1
101 Kevin 10/2/2005 Business Training day 2
101 Kevin 10/3/2005 Business Training day 3
103 Tracy 10/1/2005 Personal Sick
103 Tracy 10/3/2005 Business Seminar
...
...
I need to have a report like
Kevin (101)
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy
10/1/2005 Personal Sick
10/3/2005 Business Seminar
FYI: I also have another table as EmpMaster with empno and name, so
that I can fetch those data from there.
Someone please assist me in preparing this report
Thankyou in advance
Best Regards
ShaninThis script returns data for a specified employee:
declare @.emp_name <datatype>
set @.emp_name = 'Kevin'
select latedate
,reasonlate
,justification
from dbo.EmpMaster
inner join <other_table>
on <other_table>.empno = dbo.EmpMaster.empno
where (dbo.EmpMaster.empname = @.emp_name)
You could design a procedure based on the query above.
To help you find a better solution we'd need to see DDL, more sample data
and preferably a more elaborate description of expected results.
ML|||Thank you ML for the reply
I actually need to use this script in an ASP file and generate a
report, I will not be able use hard coding, should be dynamic.
I forgot to provide the information that the core data is in a table
called HRDATA where I have those columns
empno
empname
latedate
reasonlate
justification
and have another master table viz. EmpMaster just in case we need to
join or fetch empno and empname alone
By the way what's DDL ?
Regards
Shanin|||DDL = Data Definition Language
For relevant info, please see:
http://www.aspfaq.com/etiquette.asp?id=5006
If you need to access data from a web page, then using a stored procedure is
the best way to do it. The procedure itself may not be dynamic, but the
purpose it serves is far from being "hard coded".
After you provide us with better specifications, we can help you design a
better solution.
Maybe this might also be a genuine opportunity to wipe the dust from your
copy of Books Online. :)
ML|||The specification was the one I provided above, ok let me be more
precise
I have a table viz. HRDATA andthe columns to be considered from this
are
empno - float(8)
empname - varchar(50)
loccode - varchar(50)
latedate - datetime
reasonlate-varchar(100)
justification-varchar(900)
approved-numeric(9)
now each time a employee submits his data from a ASP web form for a
certain date, a record is created in this table, hence for each date he
is late or absent he shall submit his justification, and hence the
records are like below
empno empname loccode latedate reasonlate justification
approved
101 Kevin JJ 10/1/2005 Business Training
day 1 1
101 Kevin JJ 10/2/2005 Business Training
day 2 1
101 Kevin JJ 10/3/2005 Business Training
day 3 1
103 Tracy OL 10/1/2005 Personal Sick
2
103 Tracy OL 10/3/2005 Business Seminar
1
now the HR Manager needs a report like the one below, grouped by
Empname, number and loccode
Kevin (101) - JJ
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy (103) - OL
10/1/2005 Personal Sick
10/3/2005 Business Seminar
I have another master table viz. EmpMaster where I have all the
employees name, no, and loccode. Hope am repeating what I said in my
first post.
Ths is the actual scenario, I guess it's very simple for SQL GURUS..
Regards
Shanin|||Thankx guys, I have made it possible through ASP coding itself.
Regards
Shanin|||> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
The query below will provide the needed data. However, the grouping in your
specification is really report formatting (i.e. group section headers) and
is beyond the scope of SQL. The particulars depend on your reporting tool.
SELECT
empno,
empname,
loccode,
latedate,
reasonlate,
justification
FROM HRDATA
ORDER BY
empname,
empno,
loccode
The EmpMaster table isn't needed since HRDATA contains the required info.
In fact, redundant non-key data like empname is a big red flag in a
relational database. There are some cases where redundancy is deliberately
introduced but I get the feeling that poor design is the case here. Which
name would your HR Manager expect when you have different names in both
tables for the same employee (empno)?
In the future, please include the DDL scripts and sample data like the
following. Many of your fellow SQL Server community members will take the
time to develop and test a solution to your problem. Scripts eliminate
ambiguity and is quite time consuming to construct these scripts from
narrative.
CREATE TABLE HRDATA
(
empno float(8),
empname varchar(50),
loccode varchar(50),
latedate datetime,
reasonlate varchar(100),
justification varchar(900),
approved numeric(9)
)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/1/2005', 'Business', 'Training day 1', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/2/2005', 'Business', 'Training day 2', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/3/2005', 'Business', 'Training day 3', 1)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/1/2005', 'Personal', 'Sick', 2)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/3/2005', 'Business', 'Seminar', 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Cupid Shan" <shaninraja@.gmail.com> wrote in message
news:1130751379.384631.160800@.g44g2000cwa.googlegroups.com...
> The specification was the one I provided above, ok let me be more
> precise
> I have a table viz. HRDATA andthe columns to be considered from this
> are
> empno - float(8)
> empname - varchar(50)
> loccode - varchar(50)
> latedate - datetime
> reasonlate-varchar(100)
> justification-varchar(900)
> approved-numeric(9)
> now each time a employee submits his data from a ASP web form for a
> certain date, a record is created in this table, hence for each date he
> is late or absent he shall submit his justification, and hence the
> records are like below
> empno empname loccode latedate reasonlate justification
> approved
> 101 Kevin JJ 10/1/2005 Business Training
> day 1 1
> 101 Kevin JJ 10/2/2005 Business Training
> day 2 1
> 101 Kevin JJ 10/3/2005 Business Training
> day 3 1
> 103 Tracy OL 10/1/2005 Personal Sick
> 2
> 103 Tracy OL 10/3/2005 Business Seminar
> 1
> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
> Kevin (101) - JJ
> 10/1/2005 Business Training day 1
> 10/2/2005 Business Training day 2
> 10/3/2005 Business Training day 3
> Tracy (103) - OL
> 10/1/2005 Personal Sick
> 10/3/2005 Business Seminar
> I have another master table viz. EmpMaster where I have all the
> employees name, no, and loccode. Hope am repeating what I said in my
> first post.
> Ths is the actual scenario, I guess it's very simple for SQL GURUS..
> Regards
> Shanin
>