Database

executing a DTS from a stored proc....

9-Aug-02 20:41

Is it possible to run UDF from C# or VC application?

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Thesisus8-Aug-02 5:15

Thesisus

8-Aug-02 5:15

I have a stituation where I need to be able to run a DTS package to clean up some data. The only thing that changes in the DTS is one number. Unable to find away to pass this variable to the package directly I made a table and a stored proc that updates this tables value. Then I run the DTS pack. This all works fine but is manual. If I could fire the DTS from the stored proc then this application would rebuild it's corrupt data on the fly. But I can't for the life of me figure out how to fire the DTS without clicking on the lil green arrow, running from a cmd prompt or using windows run utility.

Any ideas?

-----------------------------------------
Real programmers don't document. If it was hard to write, it should be hard to understand.

Nick Parker8-Aug-02 8:07

Nick Parker

8-Aug-02 8:07

You should be able to run the DTS package from code, so lets say that your recordset returns 0 which means you would need to fire your DTS package to rebuild the data you could do something like this, (at least in VB6 OMG | :OMG:

)

'=========================================================
'Include Microsoft DTSPackage Object Library in References
<p>
Dim dts As DTS.ExecutePackageTask
dts.PackageName = "[YourPackageNameHere]"
dts.RepositoryDatabaseName = "[YourDatabaseNameHere]"
dts.ServerUserName = "[YourUserNameHere]"
dts.ServerPassword = "[YourPasswordHere]"
dts.Execute(Package, PackageEvents, PackageLog, DTSTaskExecResult_Success)

HTH

Nick Parker

Thesisus9-Aug-02 5:46

Thesisus

9-Aug-02 5:46

Thanks, but this woundn't work from within the stored proc. I think this is the perverbial dead-end; where I need to look back and see how I got in this situation. The problem is probably my implimentation. The DTS takes approx. 4-5 hours to run, so anything through IIS is out. Thanks again though. Smile | :)

Real programmers don't document. If it was hard to write, it should be hard to understand.

Nick Parker9-Aug-02 6:01

Nick Parker

9-Aug-02 6:01

pmenefee wrote:
Thanks, but this woundn't work from within the stored proc. I think this is the perverbial dead-end;

Actually, if you have your DTS designated to a job then I think what you should check into is sp_start_job. This should work from within a stored procedure. Smile | :)

HTH

Nick Parker

How can I change properties for keys (ie: Caption,input mask,ect) in access using ADO or ADOX?

klateno11-Aug-02 3:39

klateno

11-Aug-02 3:39

You can use: xp_cmdshell and dts run utility to execute DTS package from store procedure. You can create statement like
exec master..xp_cmdshell 'dtsrun /Uuser_name /Ppassword /Sserver_name /Npackage_name /Mpackage_password'

Please read more about xp_cmdshell & dtsrun from SQL Server Books Online.

Regards

Bart-Man8-Aug-02 4:53

Bart-Man

8-Aug-02 4:53

Greetings,

How can I change properties for keys (ie: Caption,input mask) in access using ADO or ADOX?

I can use the code below to change a description:
------------------------------------------------
tblNew->Columns->GetItem(_bstr_t("TestKey"))->
Properties->GetItem("Description")->
Value = "Test";

But if I try to change the caption, it says:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
-------------------------------------------------
tblNew->Columns->GetItem(_bstr_t("TestKey"))->
Properties->GetItem("Caption")->
Value = "FOO";

Why is the "caption" property not available, but the description is?
Is there a way to do this?

Thanks in advance

Foreign Key with Composite Primary Key

Hitu7-Aug-02 20:39

Hitu

7-Aug-02 20:39

I have a table (Table A) in which I have Composite Primary Key on 2 columns. I have another table (Table B) where I want to refer a column as the foreign key reference to one of the columns of Composite Primary Key of table A.

How to define this relationship?

Thanks
Hitesh

Re: Foreign Key with Composite Primary Key

SimonS8-Aug-02 5:08

SimonS

8-Aug-02 5:08

Wny not do it visually through the diagram view?

Cheers,
Simon

"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)

Some URL needed!!!

Mazdak7-Aug-02 19:37

7-Aug-02 19:37

Does anybody know some sites about SQL Server2000?I need some tutorials and examples about SQLServer2000 and SQL language.Triggers,stored procedures,user defined functions and ....

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Richard Deeming8-Aug-02 2:04

8-Aug-02 2:04

SQL Server Books Online: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp[^]
SQL Server Central: http://www.sqlservercentral.com/[^]

Mazdak8-Aug-02 20:06

8-Aug-02 20:06

Thanks Richard. Smile | :)

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Nick Parker8-Aug-02 8:11

Nick Parker

8-Aug-02 8:11

Mazy,
If your still looking for information SQL Server Magazine [^] is a good site with info too.

HTH

Nick Parker

Mazdak8-Aug-02 20:05

Problem with SQLserver and updating recordset

8-Aug-02 20:05

Thank uo Nick. Smile | :)

Garth5-Aug-02 15:53

Garth

5-Aug-02 15:53

I am trying to update an existing record in a table using the following code in an ASP page. The table (tblProjectCats) has an ID value (integer, autoincrement), and Title and Description fields (both set to varchar). The following code just doesn't work...doesn't give an error message...

'//start code
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "UPDATE tblProjectCats SET Title ='Title', Description = 'Desc' WHERE ID=1", objConn, adOpenDynamic, adLockReadOnly, adCmdText
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
'//end code

Re: Problem with SQLserver and updating recordset

Richard Deeming5-Aug-02 23:06

i need your help ...¤Ð__¤Ð;

5-Aug-02 23:06

Try:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect

oConn.Execute "UPDATE tblProjectCats SET Title ='Title', Description = 'Desc' WHERE ID=1", , adExecuteNoRecords Or adCmdText

objConn.Close
Set objConn = Nothing

kims10305-Aug-02 4:56

kims1030

5-Aug-02 4:56

I'm looking for sample code..
using ADO not ODBC to access DB....
i want to just a simple code...

1. id and password checking on Dialog before application start
2. and then access to DB
3. member table id char(10) password char(10)
4. Is right or not
=======================================================================
CString tempID, tempPW; //
// int result;

tempID = m_strID; // ·Î±×ÀÎ Ã¢¿¡ ÀÔ·ÂµÈ ID ÀúÀå
tempPW = m_strPW; // ·Î±×ÀÎ Ã¢¿¡ ÀÔ·ÂµÈ ÆÐ½º¿öµå ÀúÀå

// µ¥ÀÌÅÍº£ÀÌ½º ¿¬°á ¹®ÀÚ¿°ú SQL ¸í·É ¹®ÀÚ¿À» ¼ÂÆÃÇÑ´Ù.
m_strConnection =_T("Provider=SQLOLEDB; Data Source=192.168.3.212; Initial catalog = jok_db; User ID = sa; Password = kiss;");

CString check_id = "EXEC check_id";
m_strCmdText=_T(check_id + " " + "'" + tempID + "'" + "," + "'" +tempPW + "'");

long result = m_pRs->Fields->Item["certify"]->Value;
==========================================================================
environment
MFC, SQL2000

i want to full source code.....
plz i need your help.
thank you for reading.... Confused | :confused:

Have a nice day!!!

memo fields and other big fields

Roman Nurik4-Aug-02 19:18

Roman Nurik

4-Aug-02 19:18

i have a MSAccess database (soon to be upgraded to SQL Server db)... which has Memo fields and some OLE and maybe even picture fields

in ASP... given a connection object (set cn = server.createobject("adodb.connection")), how do i update the data in the memo field to that of a variable

e.g. i want to do "UPDATE myTable SET myMemoField=<here i="" need="" to="" put="" like="" 50k="" of="" data=""> WHERE ... " but of course i can't fit that all into the string

and even if it did fit in the string, it screws up when i have SQL commands in the text data itself

thanks in advance for your help

- Roman -

Re: memo fields and other big fields

Martin Ziacek5-Aug-02 23:23

Martin Ziacek

5-Aug-02 23:23

I think you have to do it manually - write code to open recordset, call method Edit(), set value for your memo field and call Update().

When you will move to SQL server you might want to write stored procedures to update your database - you will get it very robust and probably faster.

Martin

--------------------------------------------
C'mon we all know computers are experimental devices and should only be used for playing games.
Using them for alternative stuff like business, is clearly not using them for what they are intended.

Colin Davies

Re: memo fields and other big fields

Roman Nurik6-Aug-02 6:05

Roman Nurik

6-Aug-02 6:05

thanks, i will try that method

- Roman -

Collation

Mazdak3-Aug-02 5:30

3-Aug-02 5:30

With which SQL stored procedure I can get/set table or databse collation?

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Re: Collation

Richard Deeming5-Aug-02 23:13

5-Aug-02 23:13

From SQL BOL:

Changing Collations

You can change the collation of a column by using the ALTER TABLE statement:

CREATE TABLE MyTable (
   PrimaryKey   int PRIMARY KEY,
   CharCol      varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable 
   ALTER COLUMN CharCol varchar(10)
   COLLATE Latin1_General_CI_AS NOT NULL
GO

You cannot alter the collation of a column that is currently referenced by:

A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint.

You can also use the COLLATE clause on an ALTER DATABASE to change the default collation of the database:

ALTER DATABASE MyDatabase COLLATE French_CI_AS

Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.
All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.
The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.

After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft® SQL Server™ 2000 you must:

Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.
Export all of your data using a tool such as bulk copy.
Drop all of the user databases.
Rebuild the master database specifying the new collation.
Create all of the databases and all of the objects in them.
Import all of your data.

Note Instead of changing the default collation of an instance of SQL Server 2000, you can specify a default collation for each new database you create.

Re: Collation

Richard Deeming5-Aug-02 23:32

5-Aug-02 23:32

This example returns the name of the default collation for the server.

SELECT SERVERPROPERTY('Collation')

This example returns the name of the default collation for the Northwind database.

SELECT DATABASEPROPERTYEX('Northwind', 'Collation')

Getting the collation for a column is not as easy. The only way I have found is to use the SQL_VARIANT_PROPERTY function. It's only supposed to work on sql_variant types, but it seems to work on other types as well.

SELECT TOP 1 SQL_VARIANT_PROPERTY(Column, 'Collation') FROM Table

Re: Collation

Mazdak7-Aug-02 0:36