Dumb Stored Procedure Question

I've designed a very basic SQL Server Stored Procedure that I'm using
via a Visual Basic 6.0 front-end file in retrieving records into a
data entry form.

I can't for the life of me get the records retrieved via the Stored
Procedure to be edited.

I can't even run the stored procedure in MSDE's T-SQL utility and then
edit any of the records shown to me.

Any ideas?

Thanks!

Brad McCollum
bmccoll1@.midsouth.rr.comBrad H McCollum (bmccoll1@.midsouth.rr.com) writes:
> I've designed a very basic SQL Server Stored Procedure that I'm using
> via a Visual Basic 6.0 front-end file in retrieving records into a
> data entry form.
> I can't for the life of me get the records retrieved via the Stored
> Procedure to be edited.
> I can't even run the stored procedure in MSDE's T-SQL utility and then
> edit any of the records shown to me.

Since I don't know how your procedure looks like, I have to guess.

The problem is likelyl to be that ADO is to smart for its own good.
It thinks that each column must be associated with a table, so if you
say:

SELECT a, b + 'j' FROM tbl

The first column will be editable, the second not.

If you get the result set into a temp table and return from that, you
are OK. Nevermind that the temp table dies with the procedure. ADO is
easy to fool.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DTSX file

A developer asked for help to schedule a xxxx.dtsx file developed via Visual Studio 2005 on SQL Server 2005 Management Studio Job Agent? Not too familiar just yet with VS2005 and SQL2005. Any help would be appreciated.

ThanksOK, you're in VS, right?

Save your file (File/Save xxxx.dtsx)

Save a copy of your package to the SQL Server (File/Save Copy of xxxx.dtsx As...)

You then get a 'Save copy of Package' dialog.
Enter the servername that you want to store the package in (you're saving it right in the msdb database)

Leave Authentication type as Windows

Got to package path box and click on the ellipsis.
This brings up the SSIS Package dialog. Give the package a name, and press OK to store it under SSIS packages (it's a very good plan to organise folders under SSIS packages, and store packages systematucally, but lets just try to get this going)

You're back in 'Save copy of Package' dialog.. Got to 'Protection Level', press the ellipsis, and from the 'Package protection level' dialog, choose 'Rely on server storage...' (If you're just trying to get this working, all the encryption business is hassle you don't need)

You're back in 'Save copy of Package' dialog. Hit OK. You've saved the package.

Now go into Management Studio. Create a new job. Create a new step. In teh Type drop down, choose SQL Server Integration Services Package.
Leave Package Source as SQL Server.
In Server, enter the name of the server that you entered when you saved the package.
In Package, press teh ellipses, and select the package that you just saved.

Press OK.

As for teh rest, just continue as in any normal job, with scheduling etc.

Now, I can tell you now that you'll almost certainly run into problems with security etc, you'll have to chase all that out on your own. But that's the basic technique that runs all our job / packages.

HTH

If you get really stuck, hit me up with specifics on sammessengerathotmaildotcom
|||

Sam,

I still get an error. The package execution failed

DTSX file

A developer asked for help to schedule a xxxx.dtsx file developed via Visual Studio 2005 on SQL Server 2005 Management Studio Job Agent? Not too familiar just yet with VS2005 and SQL2005. Any help would be appreciated.

ThanksOK, you're in VS, right?

Save your file (File/Save xxxx.dtsx)

Save a copy of your package to the SQL Server (File/Save Copy of xxxx.dtsx As...)

You then get a 'Save copy of Package' dialog.
Enter the servername that you want to store the package in (you're saving it right in the msdb database)

Leave Authentication type as Windows

Got to package path box and click on the ellipsis.
This brings up the SSIS Package dialog. Give the package a name, and press OK to store it under SSIS packages (it's a very good plan to organise folders under SSIS packages, and store packages systematucally, but lets just try to get this going)

You're back in 'Save copy of Package' dialog.. Got to 'Protection Level', press the ellipsis, and from the 'Package protection level' dialog, choose 'Rely on server storage...' (If you're just trying to get this working, all the encryption business is hassle you don't need)

You're back in 'Save copy of Package' dialog. Hit OK. You've saved the package.

Now go into Management Studio. Create a new job. Create a new step. In teh Type drop down, choose SQL Server Integration Services Package.
Leave Package Source as SQL Server.
In Server, enter the name of the server that you entered when you saved the package.
In Package, press teh ellipses, and select the package that you just saved.

Press OK.

As for teh rest, just continue as in any normal job, with scheduling etc.

Now, I can tell you now that you'll almost certainly run into problems with security etc, you'll have to chase all that out on your own. But that's the basic technique that runs all our job / packages.

HTH

If you get really stuck, hit me up with specifics on sammessengerathotmaildotcom|||

Sam,

I still get an error. The package execution failed

DTS: Visual FoxPro to SQL Server

Hi,

Today was my first day to use DTS. I tried to transfer a Visual FoxPro Database to my SQL Server. I noticed that I am getting errors on the data that contains date fields. So, for testing purposes I transformed the Visual FoxPro fields that contained datefields into char fields. And this works, but obviously I cannot hunt around in a huge database looking for datefields in tables and change them manually. Plus, by changing to char fields I noticed that those fields that are empty are transformed as 1899-12-30. :p

Does anyone have any ideas?

Thanks,
LauraWhat's the source field datatype?|||The field types are smalldate.|||To make it as paifull as possible, I'd DTS the FP table into a staging table and go from there.

DTS.Package, where is it?

I created a DTS package, and saved it a as Visual Basic program.
But when I try to run it, it does not recognize DTS.Package, DTS.Package2.
Where can I find these?thanks for all your responses.

DTS woes

I've created a DTS package with the data-export wizard and customized it in visual basic (to do some transformations) When I run the code, No errors are logged, but I'm loosing a massive amount of rows on some of my tables. I'm just moving data from one db to another on the same server. When I do this twice with the same source, I get different results. I'm completely lost here :-(Duh! Nothing to to with my DTS-Script. A lot to do with me confusing backup names on my test server. Stupid me.

DTS to start a VB job on another server

When an entry is done on a table that has a trigger tied
to it, I would like to execute a Visual Basic program that
resides on another server. I tries making a DTS package
with the Execute Process task property. There is no
ability to pass a userid/password for a logon. I'm also
not sure, if this was even possible, would the program run
on that server or would is be pulled to the SQL Server and
actually run there?
Any info on if this is possible would be appreciated. If
there is some other method to allow an update to an entry
in the database trigger the running of the .exe on a
different server, that would be great to know.
TIA,
Doug
Try using the extended stored proc 'xp_cmdshell' to execute you exe...either map a drive to you sql server or provide the unc path.......
Brad Feaker
DBA
|||On the Win2k developement kit CD there is a file call exec.vbs which will
start a program on a remote server. The program will run in the remote
server's environment not the calling server. Use xp_cmdshell to start
exec.vbs.
"Brad Feaker" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7FD9BF-8DBC-407E-B34C-44D82FBC5410@.microsoft.com...
> Try using the extended stored proc 'xp_cmdshell' to execute you
exe...either map a drive to you sql server or provide the unc path.......
> Brad Feaker
> DBA
>

DTS to start a VB job on another server

When an entry is done on a table that has a trigger tied
to it, I would like to execute a Visual Basic program that
resides on another server. I tries making a DTS package
with the Execute Process task property. There is no
ability to pass a userid/password for a logon. I'm also
not sure, if this was even possible, would the program run
on that server or would is be pulled to the SQL Server and
actually run there?
Any info on if this is possible would be appreciated. If
there is some other method to allow an update to an entry
in the database trigger the running of the .exe on a
different server, that would be great to know.
TIA,
DougTry using the extended stored proc 'xp_cmdshell' to execute you exe...eithe
r map a drive to you sql server or provide the unc path.......
Brad Feaker
DBA|||On the Win2k developement kit CD there is a file call exec.vbs which will
start a program on a remote server. The program will run in the remote
server's environment not the calling server. Use xp_cmdshell to start
exec.vbs.
"Brad Feaker" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7FD9BF-8DBC-407E-B34C-44D82FBC5410@.microsoft.com...
> Try using the extended stored proc 'xp_cmdshell' to execute you
exe...either map a drive to you sql server or provide the unc path.......
> Brad Feaker
> DBA
>

DTS to start a VB job on another server

When an entry is done on a table that has a trigger tied
to it, I would like to execute a Visual Basic program that
resides on another server. I tries making a DTS package
with the Execute Process task property. There is no
ability to pass a userid/password for a logon. I'm also
not sure, if this was even possible, would the program run
on that server or would is be pulled to the SQL Server and
actually run there?
Any info on if this is possible would be appreciated. If
there is some other method to allow an update to an entry
in the database trigger the running of the .exe on a
different server, that would be great to know.
TIA,
DougTry using the extended stored proc 'xp_cmdshell' to execute you exe...either map a drive to you sql server or provide the unc path......
Brad Feake
DB|||On the Win2k developement kit CD there is a file call exec.vbs which will
start a program on a remote server. The program will run in the remote
server's environment not the calling server. Use xp_cmdshell to start
exec.vbs.
"Brad Feaker" <anonymous@.discussions.microsoft.com> wrote in message
news:8B7FD9BF-8DBC-407E-B34C-44D82FBC5410@.microsoft.com...
> Try using the extended stored proc 'xp_cmdshell' to execute you
exe...either map a drive to you sql server or provide the unc path.......
> Brad Feaker
> DBA
>