Make SQL Agent use drive mapping?

Good day,
I'm migrating our current SQL 2k box (running on win 2000) to a new Windows 2003 server. I'm having trouble getting the SQL agent to use the servers drive mapping when running jobs. The odbc driver that I'm using does not support UNC paths. I know, dri
ve mapping are not cool becuase we have to keep the server logged in but it's the only way and it does work on our old box. The weird thing is that I can't get this working on the new server. On the old box the SQL Agent is set to run as the same accoun
t as the logged in user and it can access the drive mappings fine, it's been running this way for quite some time. However, on my new box this does not work...is this a windows 2003 server thing? I'm runing SQL 2k SP3 on both boxes. Also, the new box i
n not in a domain so the account I'm trying to use is a local admin acount.
Any ideas?
Drive mappings are not guaranteed to work when being accessed from a
service. There is no way to force them to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I figured it out. I simply added a step in each scheduled job to create and then delete the drive mapping.
For example, I just use the "net use f: \\server\volume" to create the drive ampping an then excute the rest of my job steps and then use another step with this command "net use f: /delete" to delete it. Becuase the SQL Agent service is excuting the comm
and the drive mapping is in it's session and it works every time. It's nice becuase the drive mapping isn't even seen by the desktop and works when the server in not logged in. I remove the drive mapping at the end of each job becuase the drive mapping
remains in tack as long as the SQL agent service stays running and therefore if another job runs and tries to re-create the drive mapping the job will fail.

Make SQL Agent use drive mapping?

Good day,
I'm migrating our current SQL 2k box (running on win 2000) to a new Windows
2003 server. I'm having trouble getting the SQL agent to use the servers dr
ive mapping when running jobs. The odbc driver that I'm using does not supp
ort UNC paths. I know, dri
ve mapping are not cool becuase we have to keep the server logged in but it'
s the only way and it does work on our old box. The weird thing is that I c
an't get this working on the new server. On the old box the SQL Agent is se
t to run as the same accoun
t as the logged in user and it can access the drive mappings fine, it's been
running this way for quite some time. However, on my new box this does not
work...is this a windows 2003 server thing? I'm runing SQL 2k SP3 on both
boxes. Also, the new box i
n not in a domain so the account I'm trying to use is a local admin acount.
Any ideas?Drive mappings are not guaranteed to work when being accessed from a
service. There is no way to force them to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I figured it out. I simply added a step in each scheduled job to create and
then delete the drive mapping.
For example, I just use the "net use f: \\server\volume" to create the drive
ampping an then excute the rest of my job steps and then use another step w
ith this command "net use f: /delete" to delete it. Becuase the SQL Agent s
ervice is excuting the comm
and the drive mapping is in it's session and it works every time. It's nice
becuase the drive mapping isn't even seen by the desktop and works when the
server in not logged in. I remove the drive mapping at the end of each job
becuase the drive mapping
remains in tack as long as the SQL agent service stays running and therefore
if another job runs and tries to re-create the drive mapping the job will f
ail.

Make a horizontal table into a vertical table

Hi All,
Any assistance would be greatly appreciated.

I have a current table which I create on a regular basis from a text file with a layout similar to this:
Type Policy # Amount Rider 1 Amt Rider 2 Amt
B 1112H 24.34 12 12.34

This text file is brought into a staging table with each field (even the amount field) as a varchar (12). I then assign types in a later step in my DTS package.

What I need to do is stack the riders under each policy so for each policy where there is a rider, there is a new row for every rider.
So in the example I've given, there would be 2 additional rows for the original first row since there are two riders.
Type Policy # Amount
B 1112H 24.34
R1 1112H 12
R2 1112H 12.34

I plan on doing this by first creating a table with just the Type, Policy #, and Amt fields, and then using a series of insert queries where I take the rider (if there is one) and append it onto the table.

However, I'm getting the following error message when I try:
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Basically, it wouldn't let me put an 'R1' in the Type column.
How can I get this to work!?!?

Thanks in advance for your helpIf the number of rider columns is constant, you can use a Union query like this:
Insert into DESTINATIONTABLE (Type, [Policy #], Amount)
Select [Type], [Policy #], [Amount Rider 1] as Amount from SOURCETABLE where [Amount Rider 1] is not null
UNION
Select [Type], [Policy #], [Amount Rider 2] as Amount from SOURCETABLE where [Amount Rider 2] is not null
UNION
.
.
.
etc|||I can't get past the first INSERT statement for the first rider.
I'm still getting this error message:

Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

I have the same number of columns, the data types shouldn't be an issue. I'm at a stand still!|||It's probably something simple, like a missing comma that is causing the compiler to miscount the columns.

And the datatypes may be an issue. Double check them.

If you want more help, post the layout of your tables (DDL code is best), and the SQL you are trying to run.|||Wow blindman, you are a genius, and I'm a big retarded idiot. Yup, stoopid mistake. Importing literally a hundred columns, and one of them was missing a comma. Had to search through the bowels of my dts package, but finally found it. Thanks very much for your help.

Oh, and I didn't need to use the UNION in mine. Just INSERT INTO worked. Once I located that rogue comma, of course.|||Genius? How do you think I knew what your problem was?

"An expert is a man who has made all the mistakes which can be made in a very narrow field."
-Niels Bohr|||Whatever you say... I still think you have at least above average intelligence. ;)