Database

29-Nov-02 9:36

Hi

I let SQL create me a create script for some stored procedures that has changed. The problme is how do I run this. I thought dropping this as CommandText for a SqlCommand would be OK, but this is not the case. Lotsa errors with GO statement. This what I get from SQL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectUser]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.SelectUser
	@id bigint
AS
	SET NOCOUNT ON;
SELECT     ID, Username, Password, Email, Name, Sex, HomeTel, Language, WorkTel, CellTel, Address, City, Province, IDNr, PostalCode, JoinDate, Views, 
                      HomeShow, WorkShow, CellShow
FROM         Users
WHERE     (ID = @id)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I have pasted this code in query analyser and it runs flawlessly. What am I doing wrong?

Cheers Smile | :)

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Sorted, kind of....

leppie29-Nov-02 12:14

29-Nov-02 12:14

The problem seems to be the GO statement. When I select all the commands between the GO's , it can run them individually. But thats a bit Eek! | :eek:

too much work.

Any suggestions how to run it as a batch?

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Not Active29-Nov-02 12:39

29-Nov-02 12:39

You should be calling a stored proc instead of trying build the script each time and call it.

leppie29-Nov-02 13:03

29-Nov-02 13:03

Mark Nischalke wrote:
You should be calling a stored proc instead of trying build the script each time and call it.

I'm trying to run a create script (that Enteprise Manager creates ) so changes in my stored procedures on the development machine can be reflected on the production machine. As simple as that! Just a one time run to make sure the production server is up to date really.

After that, the webserver can call the modified procedures properly.

Smile | :)

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Not Active29-Nov-02 14:54

29-Nov-02 14:54

Just so I understand. You are creating stored procs on your development machine and want them reflected on the production machine?

Ever heard of replication?

leppie30-Nov-02 4:41

30-Nov-02 4:41

Mark Nischalke wrote:
Just so I understand. You are creating stored procs on your development machine and want them reflected on the production machine?

No, I am changing, and perhaps creating new ones...

Mark Nischalke wrote:
Ever heard of replication?

How do you propose I use replication over the web? Especially when the SQL server is provided by the ISP?

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Not Active30-Nov-02 7:31

30-Nov-02 7:31

leppie wrote:
How do you propose I use replication over the web?

I don't know. I guess all the updating I've been doing is being completed by the SQL Server fairies. If you ISP isn't provding you access via EM or some other control panel type app then you seriously need to look elsewhere. I would also suggest you do a little more research on SQL Server and how to use it.

leppie30-Nov-02 13:34

30-Nov-02 13:34

Mark Nischalke wrote:
If you ISP isn't provding you access via EM or some other control panel type app then you seriously need to look elsewhere.

O well I wrote a web interface for what I needed in about 30 min.

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Not Active30-Nov-02 19:50

30-Nov-02 19:50

Your parents must be proud that you have reinvented the wheel. Eventually it may even turn.

Go away, I say, go away boy you bother me. -- Foghorn Leghorn

@RETURN_VALUE

leppie29-Nov-02 8:04

29-Nov-02 8:04

Hi all

What is this parameter? Why do I need it? SHould I need it? I have never had to. Is seem to been auto created. Why is that so?

Appreciate all your help Smile | :)

Cheers

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Jamie Nordmeyer29-Nov-02 9:18

Jamie Nordmeyer

29-Nov-02 9:18

Hey Leppie. The @RETURN_VALUE parameter is auto-created, as you guessed, by SQL Server when you Return something. For Example:

SQL

ALTER PROCEDURE uspIsValidUser
   @UserName VarChar(20)
AS
BEGIN
   IF (SELECT COUNT(*) FROM tblUsers WHERE UserName = @UserName) = 1
   BEGIN
      SELECT UserName, Address, City, State, ZipCode
         FROM tblUsers
         WHERE UserName = @UserName
      RETURN True
   END
   ELSE
      RETURN False
END

In your code, you can then check the value of @RETURN_VALUE value to see what was returned. It's possible to return datasets at the same time due to the fact that return VALUES are automatically stored in @RETURN_VALUE.

Jamie Nordmeyer
Portland, Oregon, USA

leppie29-Nov-02 9:40

29-Nov-02 9:40

Thanks, I assume I wont use it in the near future, but will keep it in the back of my mind Smile | :)

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Not Active29-Nov-02 12:34

29-Nov-02 12:34

Interesting. You write an app to generate stored procs but still need to ask what @RETURN_VALUE is and what it's for. Confused | :confused:

leppie29-Nov-02 12:56

29-Nov-02 12:56

Mark Nischalke wrote:
Interesting. You write an app to generate stored procs but still need to ask what @RETURN_VALUE is and what it's for.

I'm pretty new to the SQL world, so thats why I'm asking... I dont really every have had the need to use it (yeah, I know its a bit more effiecient, but I prefer to do what I know works).[edit]And the fact that VS.NET creates it automatically Confused | :confused:

[/edit]

Can I ask you this? Did you even bother to read the article and download the project? Have you tried it perhaps on a database of your own and see the results?

Cheers Smile | :)

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Not Active29-Nov-02 14:49

29-Nov-02 14:49

I'm not quite interested in reading an article titled "SQL Stored Procedure Wrapper" written by someone who knows nothing about stored procedures. Mad | :mad:

leppie29-Nov-02 23:21

Accessing a compiled Access dB

29-Nov-02 23:21

Mark Nischalke wrote:
I'm not quite interested in reading an article titled "SQL Stored Procedure Wrapper" written by someone who knows nothing about stored procedures

Thats poor attitude! Who says I know nothing of SP's? YOU, because I dont know what @RETURN_VALUE does, and why it get auto generated by VS.NET. Why not try being a bit helpful, and less ignorant?

And please, being all knowing (as it appears), why not give me an example of the generated @RETURN_VALUE usage? MSDN sure doesnt prove to be helpful (in this aspect).

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Paul Watson29-Nov-02 2:33

Paul Watson

29-Nov-02 2:33

A client is in a bind because one of their contract developers ran off to London with the source code to an Access project.

From what they are telling me they have a directory with an MDB file, some EXEs and setup app. It creates a shortcut icon with a target like this C:\SPP05\MSARN200.EXE C:\SPP05\spp05.mdb /ini C:\WINDOWS\spp05.ini

The client tells me the file is compiled (have not got a hold of it yet) and I was wondering; Can you get into the VBA code as well as the actual database structure? They want some modifications. Does Access ever compile right down to an executable? Or is more of an interpreted type environment?

Sorry for the lack of info and thanks for any help.

Paul Watson
Bluegrass
Cape Town, South Africa

Colin Davies wrote:
...can you imagine a John Simmons stalker !

Re: Accessing a compiled Access dB

Nick Parker4-Dec-02 9:13

Nick Parker

4-Dec-02 9:13

Paul Watson wrote:
Can you get into the VBA code as well as the actual database structure? They want some modifications. Does Access ever compile right down to an executable? Or is more of an interpreted type environment?

Paul, from what I remember and what I just checked, most code should be contained inside a module(s) if it is strickly VBA which is in the Database itself for you to look at, however if they actually have an executable then it is more likely a VB app that is making ADO/DAO calls into that particular datbase. The modules inside Access themselves are meant to run within Access. Hope this helps.

Nick Parker

Not everything that can be counted counts, and not everything that counts can be counted. - Albert Einstein

Access, CCommand and passwords

LukeV28-Nov-02 5:59

LukeV

28-Nov-02 5:59

If I set a password for my Access DB and I try to connect using CCommand, it won't work!

<br />
HRESULT		hr;<br />
<br />
		CDBPropSet	dbinit(DBPROPSET_DBINIT);<br />
<br />
		dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true);<br />
		dbinit.AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false);<br />
		dbinit.AddProperty(DBPROP_AUTH_MASK_PASSWORD, false);<br />
		dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("pwd"));<br />
		dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));<br />
		dbinit.AddProperty(DBPROP_INIT_DATASOURCE, m_sSource );<br />
		dbinit.AddProperty(DBPROP_INIT_MODE, (long)19);<br />
		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);<br />
		dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(""));<br />
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);<br />
		hr = m_db.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit);<br />
		if (FAILED(hr))<br />
			return hr;<br />
<br />
		return m_session.Open(m_db);<br />

I used to do this a hundred times with ASP and password-protected DB with no problem. Why is it like that with CCommand?

Thanks!

Mazdak28-Nov-02 23:02

Mazdak

28-Nov-02 23:02

You have to give the password and user in connection string.

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

LukeV29-Nov-02 3:00

LukeV

29-Nov-02 3:00

ok... could you be more explicit please? Smile | :)

Thanks for your help!

Mazdak29-Nov-02 8:31

Mazdak

29-Nov-02 8:31

I'll search in my codes and send you and example. Smile | :)

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits