How to execute a selectcommand on button click?

Hello friends

Can anyone tell me the means by which I can populate a label or a datagrid by executing a selectcommand statement on the form of my webpage. And what is the best way to retrieve data from a SQL datasource, is it by using separate sqldatasources for each different parts of a page that requires different data from different tables in the same database or by using a tableadapter to retrieve the data for the best performance?

Thanks for replying for my previous questions. It was helpful and great in my program implementation.

Thank you

Hi

When you bind the sqldatasource to datagrid selectcommand will be automatically executed if you call databind().

As for difference of SqlDataSource and tableadapter it largely depends on the implementation you could take a look at:

DataSets vs. Collections

How to enumerate the databases on the server

Given that you have an interface with a connection to SQL Server Express, is there some T-SQL script or statement that can enumerate the available database names on the server ?

For example:

CString clQuery;

clQuery.Format("SELECT SERVERPROPERTY('Databases')");

m_db.Execute(clQuery);

or something of the sort ?

This will work in both SQL 2000 and SQL 2005:

Code Snippet

SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA

|||

Thanks, Arnie.

For some reason, that statement only yields about 16 'Master' records in the CATALOG_NAME column. I really need a way to enumerate all the databases on the server.

Any other ideas ?

Mike

|||

Sorry Mike, my mistake. That Information VIEW 'changed' between SQL 2000 and SQL 2005.

In SQL 2005, use:

SELECT name

FROM sys.databases

|||

Thanks, Arnie. That works perfectly.

BTW, I have had trouble responding as your server appears to be down quite a bit of the time.

Regards,

Mike

|||There seems to have been some 'replication' issues this week. We all appreciate everyone's patience/

How to enter this command line sql statement...??

:confused: I am trying to use a program called ODBCVIEW to query a database from the command line, and write it to text file. The instructions look easy, But I am illiterate to translating command line syntax.
I am not sure if I use the ">" , "[", etc... or if I leave them out. Can some one show me exactly how the finished code is supposed to look (in laymans)...

The program I am using to query the DB is called ODBCVIEW. Here is a link to the page with the syntax(located at the bottom) - http://www.slik.co.nz/HTML_help/odbc_view.htm

...and I have also pasted it below:

-COPIED FROM WEBSITE:
ODBCView also supports a non-interactive command line mode to execute a query and save the results to a text file. The syntax is as follows:

ODBCView.exe <DSN=DataSourceName;UID=User;PWD=Password> [<SQLScript.sql>] [<OutputFile.htm|csv|txt>]

Where:

DataSource
The desired datasource name.

UID
An optional user name to logon to the database

PWD
An optional user password

SQLScript
The query to execute.

OutputFile
The path to the output file. The files extension determines the format. Use htm or html to HTML, csv for CSV.If you are only trying to query a database and write the results to a file, better try WinSQL (http://www.download.com/3001-10255_4-10213451.html). ;)|||:D Just what I was looking for!

I'm installing it now - Does it have a 'command-line' mode?

How to ensure the rest of the records will be inserted even if there is an error

Hi,
I have a sql statement that perform bulk insert into another table.
How can I ensure that if an error occurs, maybe due to primary key
constraint, the rest of the records will be inserted.
Thanks alot.Hi
Take a look at this example, even this transaction will generate a violation
of primary key constraint the rest of the data will be inserted
Please read SET ARITHABORT commant in the BOL to get a whole picture
create table #t (col int not null primary key)
begin tran
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
commit
select * from #t
drop table #t
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:%23X6PcA6XGHA.3868@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a sql statement that perform bulk insert into another table.
> How can I ensure that if an error occurs, maybe due to primary key
> constraint, the rest of the records will be inserted.
> Thanks alot.
>
>
>|||That depends on the type of error that occures. Fatal Errrors for
example will always abort the entire procedure and rollback all
transactions. There might be a way for non-fatal errors using if-blocks
like
if @.err<>0
// do something else with the datasql

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?
I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
trick doesn't work for 2005...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
>I have seen when i run a select statement written by some devs which has some joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>
|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl. ..
>
>
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
trick doesn't work for 2005...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
>I have seen when i run a select statement written by some devs which has some joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
> >I have seen when i run a select statement written by some devs which has some joins,etc.. , that
> >when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
> >commented out that states its encrypted.
> > So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> > profiler it shows something like ..
> > -- the text has been encrypted for security reason.
> > I found that magical.
> > Can someone tell me how its done ?
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005sql

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?I think that having sp_password anywhere in the text (possibly in a comment)
will do this. But this
trick doesn't work for 2005...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...

>I have seen when i run a select statement written by some devs which has so
me joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in t
he textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query An
alyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> I think that having sp_password anywhere in the text (possibly in a commen
t) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSF
TNGP03.phx.gbl...
>
>
>
>
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005

How to encorporate IF

How can I put an If statement saying If # RECORDS returned from the query below
IF # records returned is > 1 then
SUM(rmstranamt) AS rmstranamt10
ELSE
rmstranamt AS rmstranamt10

here's my statement

-

SELECT RMSFILENUM,
rmstranamt AS rmstranamt10 <--If statement goes here base on if the amount of records found in select is >1 or not
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

I want to return sum(rmstranamt) AS rmstranamt10 in cases where the same query produces > 1 records
In other words if this:
SELECT RMSFILENUM,
rmstranamt AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
produces > 1 records, then I don't want to return rmstranamt AS rmstranamt10, I want to return sum(rmstranamt) AS rmstranamt10

Wouldn't

SUM(rmstranamt)
be the same as
rmstranamt

in the case of one record? Think you should just include the sum and not worry about it.

- Brad
|||

you would think....I've spent 3 days on this...check it out more here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21692997.html

|||I see your issue now, but the problem isn't the SUM function rather the GROUP BY clause.

Just remmove the rmstranamt from your GROUP BY to achieve the desired results, otherwise each row returned represents a SUM applied to a GROUP that has one row.

- Brad|||Brad, you mean remove rmstranamt from my last Group By right? If I do that then It errors out saying that rmstranamt needs to be in it...so what do I do?|||Brad, ok yes, that works but to a certain point. My main problem is when I plug in an account that I know has more than 1 results from my first INNER JOIN query, it throws off my end aggregation and I end up with 2 account rows rather than one for that account|||SELECT RMSFILENUM, sum(rmstranamt) rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM|||

Hi,

SELECT RMSFILENUM
into #tmp

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) <= 1

SELECT RMSFILENUM,
sum(rmstranamt )AS rmstranamt10

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) > 1

union

SELECT r.RMSFILENUM,
rmstranamt AS rmstranamt10

FROM RFINANL r inner join #tmp t on r.RMSFILENUM = t.RMSFILENUM
GROUP BY r.RMSFILENUM, r.rmstranamt

Regards

How to encorporate IF

How can I put an If statement saying If # RECORDS returned from the query below
IF # records returned is > 1 then
SUM(rmstranamt) AS rmstranamt10
ELSE
rmstranamt AS rmstranamt10

here's my statement

-

SELECT RMSFILENUM,
rmstranamt AS rmstranamt10 <--If statement goes here base on if the amount of records found in select is >1 or not
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

I want to return sum(rmstranamt) AS rmstranamt10 in cases where the same query produces > 1 records
In other words if this:
SELECT RMSFILENUM,
rmstranamt AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
produces > 1 records, then I don't want to return rmstranamt AS rmstranamt10, I want to return sum(rmstranamt) AS rmstranamt10

Wouldn't

SUM(rmstranamt)
be the same as
rmstranamt

in the case of one record? Think you should just include the sum and not worry about it.

- Brad
|||

you would think....I've spent 3 days on this...check it out more here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21692997.html

|||I see your issue now, but the problem isn't the SUM function rather the GROUP BY clause.

Just remmove the rmstranamt from your GROUP BY to achieve the desired results, otherwise each row returned represents a SUM applied to a GROUP that has one row.

- Brad|||Brad, you mean remove rmstranamt from my last Group By right? If I do that then It errors out saying that rmstranamt needs to be in it...so what do I do?|||Brad, ok yes, that works but to a certain point. My main problem is when I plug in an account that I know has more than 1 results from my first INNER JOIN query, it throws off my end aggregation and I end up with 2 account rows rather than one for that account|||SELECT RMSFILENUM, sum(rmstranamt) rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM|||

Hi,

SELECT RMSFILENUM
into #tmp

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) <= 1

SELECT RMSFILENUM,
sum(rmstranamt )AS rmstranamt10

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) > 1

union

SELECT r.RMSFILENUM,
rmstranamt AS rmstranamt10

FROM RFINANL r inner join #tmp t on r.RMSFILENUM = t.RMSFILENUM
GROUP BY r.RMSFILENUM, r.rmstranamt

Regards

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
Sren
Hi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SørenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
--
HTH
Kalen Delaney, SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>sql

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SrenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-
28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

how to drop and create Triggers from an other DB?

Hi,
I am running a stored procedure on the database DB01,
that SP got some statement looking like:
use DB02
DROP Trigger DB02_USER.BOOK_DELETE
It does not run and says:
a USE database statement is not allowed in a procedure or trigger.
If I replace the code by
DROP Trigger DB02.DB02_USER.BOOK_DELETE
then it says:
'DROP TRIGGER' does not allow specifying the database name as a prefix to
the object name.
Then how is it possible to drop a trigger in an other DB than the one the SP
is stored in?
Best regards,
Francois Malgreve.> Then how is it possible to drop a trigger in an other DB than the one the
> SP
> is stored in?
EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
A|||Thanks a lot for the information.
It works very well.
But I am still faced with a related problem. Creating triggers from an SP.
When I try to execute:
EXEC('USE DB02; CREATE Trigger CSDB_TR_SELLRATE_DELETE
On DB02_USER.BOOK_DELETE
For Delete As
Begin
--code goes here
End
')
I have an error saying:
'CREATE TRIGGER' must be the first statement in a query batch.
Do you have any trick to go around this one?
Thanks,
Francois
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPFJxy36FHA.2524@.TK2MSFTNGP10.phx.gbl...
the
> EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
> A
>

How to do it in one insert statement? Please come in

Back from the vacation, I find I can't remember something. My problem is, for example, I have 2 tables. Table 1 has 3 columns: id, saledate and amount. Table 2 has just 2 columns: id and amount. Now I want to select the latest 'amount' for each 'id' from table 1 and insert into empty table 2. Is that possible to do it in one insert statement? What's the best way to do it anyways?

Thanks a lot!In your table1, is id the primary key?|||No, id and saledate are primary key. Any thoughts? Thanks anyways.|||In the Table1, do you have a primary key from one colomn, i.e., TableID?|||insert into table2
select id, max(amount)
from table1 t1
where saledate = (select max(saledate) from table1 where id = t1.id)
group by id;

The "max(amount)" and "group by id" return the maximum amount, should multiple amounts exist for the latest salesdate for a given id.|||Yes, it works! Thanks a lot!

How to do a USE statement inside a stored proc?

Hi,
I have a general purpose stored proc that could be used in several
databases. I need to tell it what database to work on. My first solution was
to use the 'USE @.DB' statement at the beginning of the stored proc, where @.D
B
was a parameter passed in. Yet this does not work!
I remember having done this before, but don't recall exactly how!
Any suggestions?
--
Thanks in advance,
Juan Dent, M.Sc.EXEC('USE '+@.db+'; do something');
Please read http://www.sommarskog.se/dynamic_sql.html
"Juan Dent" <Juan_Dent@.nospam.nospam> wrote in message
news:B8CDCB05-C049-422E-AC38-60A97D7D6CCA@.microsoft.com...
> Hi,
> I have a general purpose stored proc that could be used in several
> databases. I need to tell it what database to work on. My first solution
> was
> to use the 'USE @.DB' statement at the beginning of the stored proc, where
> @.DB
> was a parameter passed in. Yet this does not work!
> I remember having done this before, but don't recall exactly how!
> Any suggestions?
> --
> Thanks in advance,
> Juan Dent, M.Sc.

How to do a like ... or statement

Can someone explain the correct way to query SQL with a like or statement.

so I want to

SELECT * from table where column like 'Something%' or like 'somethingelse%'

I tried it and get syntax errors because I don't think 'OR' is a command like 'AND' is or is it? I have been searching and yet to find any MS SQL statement that references it... So how is this done?

(right now I just have two statement and my PHP script puts them together bu thinking there has to be a better way)

Thanks for the clarification.

-- never mind found it that is should work -- must have been something wrong with my statement thats all.

Quote:

Originally Posted by sharijl

Can someone explain the correct way to query SQL with a like or statement.

so I want to

SELECT * from table where column like 'Something%' or like 'somethingelse%'

I tried it and get syntax errors because I don't think 'OR' is a command like 'AND' is or is it? I have been searching and yet to find any MS SQL statement that references it... So how is this done?

(right now I just have two statement and my PHP script puts them together bu thinking there has to be a better way)

Thanks for the clarification.

Your code is nearly fine. Think of 'like' as '=' for a moment: you need two sides two each comparison: a='foo' OR a='bar'. Same applies here: SELECT * from table where column like 'Something%' or column like 'somethingelse%'

It's usually good practice to add parentheses, though not required.|||OH so thats it... I forgot to state the name of the column again... -- thanks

How to do a JOIN statement for a table with 2, one-to-many relationships.

Hello,

I want to be able to view data from 3 tables using the JOIN statement, but
I'm not sure of how to do it. I think i don't know the syntax of the joins.I
imagine this is easy for the experienced - but Im not.

Allow me to explain:
I have 2 Tables: PERSON and SIGN

PERSON
--
PersonNo int (Primary Key)
Name varchar(50)
StarSign int
FavFood int

SIGN
--
StarSign int (Primary Key)
StarSignName varchar(50)

Relationship: SIGN has a one-to-many relationship with PERSON. The linking
field is called 'StarSign'.

Question 1:
I want to display all the peoples names, and their star sign (whether they
have one or not).
Answer 1:
SELECT PERSON.Name, SIGN.StarSignName
FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;

No problems there. But now I want to do the same thing, but have their
favourite food displayed as well. So an additional table is needed:

FOOD
--
FavFood int (Primary Key)
FavFoodName varchar(50)

Relationship: FOOD has a one-to-many relationship with PERSON. The linking
field is called 'FavFood'.

Question 2:
I want to display all the peoples names, their star signs (whether they
have one or not), and their favourite food (whether they have one or not).
Answer 1:
?

I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL
the rows from table PERSON will appear 'irrespective' of whether they have
related records in the other tables.

Jack.Since PERSON is on the many-side in both cases, it's easy. basically, this is
the case where you have several lookup values, each of which is optional.

SELECT PERSON.Name, SIGN.StarSignName
FROM PERSON LEFT JOIN
SIGN ON PERSON.StarSign = SIGN.StarSign
LEFT JOIN
FOOD ON PERSON.FavFood = FOOD.FavFood;

(presumably, this is hypothetical, and I don't need to mention table/field
naming issues)

On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <jacksmith@.nospam.co.uk>
wrote:

>Hello,
>I want to be able to view data from 3 tables using the JOIN statement, but
>I'm not sure of how to do it. I think i don't know the syntax of the joins.I
>imagine this is easy for the experienced - but Im not.
>Allow me to explain:
>I have 2 Tables: PERSON and SIGN
>PERSON
>--
>PersonNo int (Primary Key)
>Name varchar(50)
>StarSign int
>FavFood int
>SIGN
>--
>StarSign int (Primary Key)
>StarSignName varchar(50)
>Relationship: SIGN has a one-to-many relationship with PERSON. The linking
>field is called 'StarSign'.
>Question 1:
>I want to display all the peoples names, and their star sign (whether they
>have one or not).
>Answer 1:
>SELECT PERSON.Name, SIGN.StarSignName
>FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
>No problems there. But now I want to do the same thing, but have their
>favourite food displayed as well. So an additional table is needed:
>FOOD
>--
>FavFood int (Primary Key)
>FavFoodName varchar(50)
>Relationship: FOOD has a one-to-many relationship with PERSON. The linking
>field is called 'FavFood'.
>Question 2:
>I want to display all the peoples names, their star signs (whether they
>have one or not), and their favourite food (whether they have one or not).
>Answer 1:
>?
>I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL
>the rows from table PERSON will appear 'irrespective' of whether they have
>related records in the other tables.
>Jack.|||Thank-you! One thing though if possible - can you repost your solution, but
nest the brakets around the joins.

Final thanks...
Jack.

"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:4a54n11jpod53n6k9n3j401knm7d90419v@.4ax.com...
> Since PERSON is on the many-side in both cases, it's easy. basically,
> this is
> the case where you have several lookup values, each of which is optional.
> SELECT PERSON.Name, SIGN.StarSignName
> FROM PERSON LEFT JOIN
> SIGN ON PERSON.StarSign = SIGN.StarSign
> LEFT JOIN
> FOOD ON PERSON.FavFood = FOOD.FavFood;
> (presumably, this is hypothetical, and I don't need to mention table/field
> naming issues)
>
> On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <jacksmith@.nospam.co.uk>
> wrote:
>>Hello,
>>
>>I want to be able to view data from 3 tables using the JOIN statement, but
>>I'm not sure of how to do it. I think i don't know the syntax of the
>>joins.I
>>imagine this is easy for the experienced - but Im not.
>>
>>Allow me to explain:
>>I have 2 Tables: PERSON and SIGN
>>
>>PERSON
>>--
>>PersonNo int (Primary Key)
>>Name varchar(50)
>>StarSign int
>>FavFood int
>>
>>SIGN
>>--
>>StarSign int (Primary Key)
>>StarSignName varchar(50)
>>
>>Relationship: SIGN has a one-to-many relationship with PERSON. The linking
>>field is called 'StarSign'.
>>
>>Question 1:
>>I want to display all the peoples names, and their star sign (whether they
>>have one or not).
>>Answer 1:
>>SELECT PERSON.Name, SIGN.StarSignName
>>FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
>>
>>No problems there. But now I want to do the same thing, but have their
>>favourite food displayed as well. So an additional table is needed:
>>
>>FOOD
>>--
>>FavFood int (Primary Key)
>>FavFoodName varchar(50)
>>
>>Relationship: FOOD has a one-to-many relationship with PERSON. The linking
>>field is called 'FavFood'.
>>
>>Question 2:
>>I want to display all the peoples names, their star signs (whether they
>>have one or not), and their favourite food (whether they have one or not).
>>Answer 1:
>>?
>>
>>I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so
>>ALL
>>the rows from table PERSON will appear 'irrespective' of whether they have
>>related records in the other tables.
>>
>>Jack.
>|||Jack Smith (jacksmith@.nospam.co.uk) writes:
> Thank-you! One thing though if possible - can you repost your solution,
> but nest the brakets around the joins.

I'd rather not...

Personally, I would write Steve's solution as:

SELECT P.Name, S.StarSignName, F.FoodName
FROM PERSON P
LEFT JOIN SIGN S ON P.StarSign = S.StarSign
LEFT JOIN FOOD F ON P.FavFood = F.Food;

You can add parentheses to your heart's content, but for this query
it would add more confusion than necessary.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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