Re: SQL SERVER - Database Discussion Boards

Re: add stored procedures .sql to database

Colin Angus Mackay4-Oct-05 12:44

4-Oct-05 12:44

You can actually hurl any SQL (including a code-generated script) through a regular SqlCommand object.

A few months ago I wrote a simple application to demonstrate that - It picks up a file and dumps the contents to a SQL Server via the SqlCommand object in .NET

You can find the download here[^]

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: add stored procedures .sql to database

theStorminMormon5-Oct-05 2:57

theStorminMormon

5-Oct-05 2:57

OK, I don't really understand your response, to tell the truth. I'll go to your site and take a look at the utility, maybe it can help.

What I'm really looking for (I think) is just the command I need to feed OSQL to get it to go find my .sql file and add it to the database. I'm not sure if that is equivalent to "hurl[ing] any SQL through a regular SqlCommand object" or not.

Thanks for the reply though!

The ends can never justify the means. It is the means that determine the ends.

Re: add stored procedures .sql to database

Scott Serl6-Oct-05 11:14

Scott Serl

6-Oct-05 11:14

I think what he meant was "why don't you just go read the .sql file into the commandtext property of a sql command object and executenonquery". The only thing you need to be careful of is "go", but I also read in another post that colin wrote a utility to help with sql containing "go" in it.

Re: add stored procedures .sql to database

Luis Alonso Ramos6-Oct-05 18:45

Luis Alonso Ramos

6-Oct-05 18:45

If it's a local instance of MSDE, use the following command:

osql -E -i filename.sql

The -E switch indicates to use Windows authentication, and the -i filename.sql indicates which file contains the statements to run. Run osql /? to see other options.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

Using SqlConnection without Time out

thainam3-Oct-05 23:13

thainam

3-Oct-05 23:13

I use ADO.NET to execute sql. But it takes a lot of time, so after 30 seconds program show error message "Timeout expired!" (althrough I declare "Connect Timeout=600" in connect string). How to use SqlConnection without Timeout or with a long time?
Please help me!!!
Thanks!

Re: Using SqlConnection without Time out

Colin Angus Mackay4-Oct-05 1:19

Colin Angus Mackay

4-Oct-05 1:19

There are two timeouts. The one you are using is for the connection. In other words, how long should the .NET application wait to connect (not do any work, just connect) to the database.

The other timeout it on the command object, CommandTimeout. This timeout is the amount of time that the .NET application will wait for it to perform what ever command you sent it.

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

SQL SERVER

Tran Ngoc Minh3-Oct-05 18:15

Tran Ngoc Minh

3-Oct-05 18:15

Hi,
I want to learn SQL Server and use it in C# Application.
Who know about it,please help me install it.
Thanks.

Re: SQL SERVER

Colin Angus Mackay4-Oct-05 1:25

Colin Angus Mackay

4-Oct-05 1:25

Tran Ngoc Minh wrote:
Who know about it

Lots of people do.

Tran Ngoc Minh wrote:
please help me install it

What problems are you having while installing SQL Server?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Store procedure access in c++

Anonymous3-Oct-05 17:14

Anonymous

3-Oct-05 17:14

Hi,
I want to access sql store procedure.It has 3 input parameter and 8 output parameters. store procedure also retun the result set. Can you please give me reference of sample code to access store procedure. I am totally new to DB.

Thanks

Multiple Selects in SP

Russell Jones3-Oct-05 6:05

Russell Jones

3-Oct-05 6:05

Hi
I've got a login Proc that returns some data upon successful login.

I've just added some checking code before this data to verify that the user may log in etc, but the resultsets from these checks are being returned in the dataset. Is there some way to clear the results before doing my final select statement?

Thanks

Russell

Re: Multiple Selects in SP

afronaut3-Oct-05 6:45

afronaut

3-Oct-05 6:45

Not too sure if this will help but when I have a structure like that I use a reader. I can then write the following:

if(r.Read()){
// they are valid, get user info
if(r.NextResultSet()){
while(r.Read()){
// return additional data for that person
}
}
}
r.close();

*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";

-- modified at 12:45 Monday 3rd October, 2005

Re: Multiple Selects in SP

Anonymous3-Oct-05 6:54

Anonymous

3-Oct-05 6:54

I'm trying to get SQL to not return the recordsets that the SP uses internally and just return the data i really want.

There's quite a bit of branching in the checking code which means i don't know exactly how many select statements will be executed prior to the select that i want executed.

Re: Multiple Selects in SP

Michael Potter3-Oct-05 11:57

Michael Potter

3-Oct-05 11:57

The easiest way around this is to not execute queries that return rows.

Instead of:

SELECT *
FROM Users
WHERE ID = "Tom"

Do:

DELCARE @i INTEGER
SELECT @i = COUNT(*)
FROM Users
WHERE ID = "Tom"

You can use logic to determine if @i > 0 then process the rest of the login. You can use any number of variables in the SELECT. Just make sure your query only returns 1 row.

You can also use EXISTS:

IF EXISTS(SELECT * FROM Users WHERE ID = "Tom")
BEGIN
   -- Do your work here
END

Query question

afronaut3-Oct-05 5:25

afronaut

3-Oct-05 5:25

Hey all, I've got data structured like this:

ID - Contact - Company - Email

1 - Jeff - Acme - Jeff@Acme.com
2 - Hester - Acme - Hester@Acme.com
3 - Mort - Acme - Mort@Acme.com
4 - Jason - Widget - Jason@Widget.com
5 - Harriet - Widget - Harriet@Widget.com

I need to write a query that will return only the first contact for each company, in this case it should return:
----------------------------------------
1 - Jeff - Acme - Jeff@Acme.com
4 - Jason - Widget - Jason@Widget.com

Thanks in advance!

*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";

Re: Query question

-Dr_X-3-Oct-05 5:38

-Dr_X-

3-Oct-05 5:38

Not Tested but give it a try.

<br />
SELECT ID, Contact, Company, Email<br />
FROM   [table]<br />
GROUP BY Company<br />
HAVING MAX(ID) = ID<br />

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Re: Query question

miah alom3-Oct-05 6:55

miah alom

3-Oct-05 6:55

Just a little correction

SELECT ID, Contact, Company, Email
FROM [table]
GROUP BY Company
HAVING MIN(ID) = ID

Re: Query question

Johny Ng3-Oct-05 9:12

Johny Ng

3-Oct-05 9:12

Nope

something like
select * from [table] t where id=(select min(id) from [table] tt where t.company=tt.company)

HTH

Re: Query question

miah alom3-Oct-05 9:51

miah alom

3-Oct-05 9:51

Check this

SELECT *
FROM DeleteMe
GROUP BY Company
HAVING MIN(ID) = ID

Re: Query question

Johny Ng3-Oct-05 11:35

Johny Ng

3-Oct-05 11:35

It will nerver work. Read BOL about GROUP BY first before posting.

Re: Query question

miah alom3-Oct-05 11:37

miah alom

3-Oct-05 11:37

Why dont you try it first then reply. I have already tested it and it works fine.
I think you need some lesson on Group By.

Re: Query question

Anonymous4-Oct-05 4:13

Anonymous

4-Oct-05 4:13

well the query by alam_pune looks fine to me as well..... its a nice and simple sql and works fine too.....

Re: Query question

Anonymous4-Oct-05 19:09

Anonymous

4-Oct-05 19:09

select * from [table] where ID in
(select min(ID) from [table] group by company )

Updating multiple tables in Data Set

Member 21844333-Oct-05 1:08

Member 2184433

3-Oct-05 1:08

I am having a Data Set which has multiple tables. I want the changes to be updated in the database(SqlServer 2000) which i perform in dataset.
I want to use only single DataAdapter for that. Please suggest me how to proceed regarding this.

Mansimranjit

SQL : how to change the data type of a column

Jerome Conus3-Oct-05 0:55

Jerome Conus

3-Oct-05 0:55

Hi !

My app is using SQLite as a database. I'd like to change the datatype of a column of several tables (for example changing from VARCHAR(10) to VARCHAR(50)) without loosing the existing datas.

How can I do this in SQL ? I didn't find anything ! Is it possible ?

Thanks for your help !
Jerome

Re: SQL : how to change the data type of a column

afronaut3-Oct-05 6:14

afronaut

3-Oct-05 6:14

Just use the ALTER COLUMN syntax. If I had the following table:

CREATE TABLE DContact(
CONTACTID INT IDENTITY(1,1),
CONTACT VARCHAR(50),
COMPANY VARCHAR(50),
EMAIL VARCHAR(50)
)

I could change things like so:

ALTER TABLE DContact
ALTER COLUMN Email VARCHAR(100)
GO

This is TSQL that I wrote and tested, but it should be generic enough to work with your database.

*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.