Echo sql which has been run

I am relatively new to SQL Server.

I have a command file with the following contents :
osql -E -i%1.sql -d%2 -oq:\%1.log

The sql script file has a number of insert/update statements.
The log file produced looks something like this :

1> 2> (1 row affected)
1> 2> (1 row affected)
1> 2> (0 rows affected)

Is there any setting which can be turned on such that the log file
produced from this command file will echo the statement and then
the number of rows which are affected.

TIA."Michael McGarrigle" <mjm@.barwonwater.vic.gov.au> wrote in message
news:9d0cafdc.0309111717.4dc8efaa@.posting.google.c om...
> I am relatively new to SQL Server.
> I have a command file with the following contents :
> osql -E -i%1.sql -d%2 -oq:\%1.log
> The sql script file has a number of insert/update statements.
> The log file produced looks something like this :
> 1> 2> (1 row affected)
> 1> 2> (1 row affected)
> 1> 2> (0 rows affected)
> Is there any setting which can be turned on such that the log file
> produced from this command file will echo the statement and then
> the number of rows which are affected.
> TIA.

You can try adding -e -n to your command line. It works best if each
statement is in its own batch:

update...
go
insert...
go

Like that, you get each statement with the rowcount immediately after it. If
all statements are in one batch, you'll get all the statements together then
all the rowcounts together. That might be OK for you anyway, of course.

Simonsql

Echo for SQL scripts

Hi all,

I want to see input SQL statements in the log file when I run the script in SQLPlus. I have used this set command "SET ECHO ON" for this. However, the log file looks like this -

drop table table_A
*
ERROR at line 1:
ORA-00942: table or view does not exist

7444 rows deleted.

Commit complete.

Thus, the SQL statement is not visible if it is error free. Is there a way to get around this?

ThanksDid you try to SPOOL the results?
This will do it:
SET ECHO ON
SPOOL logfile.log
@.MyScript
SPOOL OFF
;)|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

This error means what it means: your script executes a script that executes a script ...etc upto more that 20 levels deep.
:rolleyes:

Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> Is there a way to encrypt all of my custom stored procedures by issuing a
>> command from the query designer? Something like sp_ Encrypt
>> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
>> with the "WITH ENCRYPTION" option? I have too many and this will take
>> forever.
>> Thanks.
>>
>|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.com/sql_server_security_distilled_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.com/vk_sql_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

easy trigger

I create trigger no table person. When I invoke sql command
insert into person values (... bla bala bal)
trigger is autommaticaly fired.
The trigger must log sql commads, so i must insert into table log this command, whos execute triger - insert into person values (... bla bala bal).
How I can do it?Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

blindman|||This in no problem.
Problem is:
How to find out what statement is making the trigger run and get this statement in trigger code

Originally posted by blindman
Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

blindman|||The trigger has no idea what caused the insert, update, or delete. The best you can do is reference some of the nyladic functions (look them up) that return login and user information. Then you can at least see WHO or WHAT LOGIN initiated the process.

blindman|||Well...you can tell what type...

INSERT: Rows in inserted, none in deleted
DELETE: Rows in deleted, mone in inserted
UPDATE: Rows in both

...|||The following example uses a table called PARENT as the base for INSERT TRIGGER:

if object_id('dbo.tblLog') is not null
drop table dbo.tblLog
go
create table dbo.tblLog (
EventType varchar(15) null,
Parameters int null,
EventInfo varchar(8000) null)
go
if object_id('dbo.trig_parent') is not null
drop trigger dbo.trig_parent
go
create trigger dbo.trig_parent on dbo.parent for insert as
set nocount on
declare @.cmd varchar(8000)
set @.cmd = 'create table #tbl (
EventType varchar(15) null,
Parameters int null,
EventInfo varchar(8000) null);'
set @.cmd = @.cmd + 'insert #tbl '
set @.cmd = @.cmd + 'exec ('
set @.cmd = @.cmd + char(39)+'dbcc inputbuffer (' + cast(@.@.spid as varchar(25)) + ') '
set @.cmd = @.cmd + 'with no_infomsgs' + char(39)
set @.cmd = @.cmd + ');'
set @.cmd = @.cmd + 'insert dbo.tblLog select * from #tbl'
exec (@.cmd)
go

Easy Transact SQL Question

I'm a novice with SQL Querying, and can't figure out an update command.
My SELECT statement is as follows:
SELECT * FROM table1, table2
WHERE table1.keyfield = table2.keyfield and table2.field is not null
How do I convert this to an update statement on a field in table1 while
maintaining the restriction based on the field in table2?
Thanks for the help!Cindy Mikeworth wrote:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
> Thanks for the help!
For example:
UPDATE table1
SET col1 = 1234
WHERE EXISTS
(SELECT *
FROM table2
WHERE table2.keycol = table1.keycol
AND table2.col IS NOT NULL) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First learn that rows are NOT anythign like records, nor are columns
anything like a field. it is VITAL to have the right mindset in SQL
SELECT *
FROM Table1, Table2
WHERE table1.keyfield = table2.keyfield
AND table2.field IS NOT NULL;
You don't do it at all!! One of the MANY differences between a field
and column is that a column can have constraints on it. An SQL
programmer woudl have done this in the DDL (do you know what DDL is? If
not, you are sooooo screwed).
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Could you program from what you posted? HOW?!
If you have a key, in ANY table is BY DEFINITION NOT NULL, so your|||Cindy Mikeworth (CindyMikeworth@.newsgroups.nospam) writes:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
UPDATE table1
SET field = ...
FROM table1, table2
WHERE table1.keyfield = table2.keyfield
and table2.field is not null
This uses a non-standard extension of the UPDATE statement that is
proprietary to SQL Server and Sybase. As long as one is careful that
the join produces a unique value for the row to update, this is a very
practical method, not the least because it's so easy to transform a
SELECT into an UPDATE.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||UPDATE t1
SET t1.column1 = t2.value
FROM table1 t1
INNER JOIN table2 t2
ON t1.keyfield = t2.keyfield
WHERE t2.field IS NOT NULL

Dynamically adding Select Parameters (Filter)

how do i add parameters like this dynamically? do i need to change the select command? to add the @.ID part?

Although this is for delete you get the idea. This is using a sqldatasource with stored procedures.

protected void SqlPages_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{

// Wipe out the auto params and replace with the correct one.
DbParameterCollection CmdParams = e.Command.Parameters;
DbParameter oParam = null;
foreach (DbParameter cp in CmdParams)
{
//Trace.Warn(cp.ParameterName, Convert.ToString(cp.Value));
if (cp.ParameterName == "@.PageID") {
oParam = cp;
}

}
CmdParams.Clear();
CmdParams.Add(oParam);

//e.Cancel = true;
}

HTH,

|||

You can see the select i am using has a filter as well. Here is the aspx code:

<asp:SqlDataSource ID="SqlPages" runat="server"
ConnectionString="<%$ ConnectionStrings:MonkeyCon %>"
SelectCommand="Pages_SelectPagesBySite"
SelectCommandType="StoredProcedure"
...

<SelectParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:ControlParameter ControlID="ddlSiteFilter" Name="SiteID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

can i like have an option to display back all? unfiltered? like first i filter by Item Name = "Something", then i want it to be <All> now, how do i do that? something like Select * From SomeTable. no more where...

|||

You could add a branch in your SPROC where if the ID = 0 you return all. And just add an option of <Show All> with a value of 0 to your dropdownlist ..

Dynamic Tablename

Hi,
Maybe a simple Question
I have a Dataset with following SQL Command:
SELECT a.*, b.stelleText
FROM verkauf_leas200510 a LEFT OUTER JOIN
Vregion_stelle b ON a.stellelevel = b.stelleLevel AND
a.stellekey = b.stelleKey
WHERE (a.stellelevel = @.pStelleLevel) AND (a.stellekey = @.pStelleKey)
ORDER BY a.stellelevel, a.stellekey, a.sort
Is it possible to change theTablename (verkauf_leas200510 ) also
dynamically, maybe also with a parameter like in the where-clause
Have not found a solution yet, because i want to generate a report with the
tablename as parameter.
Thanks in advance
DieterIf you can use a stored procedure as a datasource for the report you can
solve the problem by creating dynamic SQL.
The Proc will have 3 parameters:
@.Tablename
@.pStelleLevel
@.pStelleKey
And in the proc you will dynamically create the select statement
Grtz,
Nico
"Dieter Felix" wrote:
> Hi,
> Maybe a simple Question
> I have a Dataset with following SQL Command:
> SELECT a.*, b.stelleText
> FROM verkauf_leas200510 a LEFT OUTER JOIN
> Vregion_stelle b ON a.stellelevel = b.stelleLevel AND
> a.stellekey = b.stelleKey
> WHERE (a.stellelevel = @.pStelleLevel) AND (a.stellekey = @.pStelleKey)
> ORDER BY a.stellelevel, a.stellekey, a.sort
> Is it possible to change theTablename (verkauf_leas200510 ) also
> dynamically, maybe also with a parameter like in the where-clause
> Have not found a solution yet, because i want to generate a report with the
> tablename as parameter.
> Thanks in advance
> Dieter|||Table name is not a problem. However, you need to have the field names
returned stay the same. To do this have the query tool in generic mode. Then
you put in an expression
="select * from " & parameters!TableName.value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dieter Felix" <Dieter Felix@.discussions.microsoft.com> wrote in message
news:6D6DE04F-8F74-418E-9EB2-E32E7437F0E1@.microsoft.com...
> Hi,
> Maybe a simple Question
> I have a Dataset with following SQL Command:
> SELECT a.*, b.stelleText
> FROM verkauf_leas200510 a LEFT OUTER JOIN
> Vregion_stelle b ON a.stellelevel = b.stelleLevel AND
> a.stellekey = b.stelleKey
> WHERE (a.stellelevel = @.pStelleLevel) AND (a.stellekey = @.pStelleKey)
> ORDER BY a.stellelevel, a.stellekey, a.sort
> Is it possible to change theTablename (verkauf_leas200510 ) also
> dynamically, maybe also with a parameter like in the where-clause
> Have not found a solution yet, because i want to generate a report with
> the
> tablename as parameter.
> Thanks in advance
> Dieter

Dynamic SQL Command

Dears,
I have a table with SQL Commands in it, and I want to create a result set
with the values powered by these commands.
I'm intending to create a stored procedure with a temporary table with that
values and use it as a result set.
Is it possible to make it using EXECUTE (@.SQLCOMMAND) ?
Does any body have another idea ?
Thanks in advantage,
Rodrigo.Rodrigo
DECLARE @.name as nvarchar(50),@.sql as nvarchar(200)
SET @.sql=N'select @.name = col_name FROM Table WHERE col=99'
EXEC sp_executesql @.sql, N'@.name nvarchar(50) OUTPUT',
@.name= @.name OUTPUT
SELECT @.name
"Rodrigo" <rborges11@.hotmail.com> wrote in message
news:OlLOwtE1DHA.1336@.TK2MSFTNGP12.phx.gbl...
> Dears,
> I have a table with SQL Commands in it, and I want to create a result set
> with the values powered by these commands.
> I'm intending to create a stored procedure with a temporary table with
that
> values and use it as a result set.
> Is it possible to make it using EXECUTE (@.SQLCOMMAND) ?
> Does any body have another idea ?
> Thanks in advantage,
>
> Rodrigo.
>|||"Rodrigo" <rborges11@.hotmail.com> wrote in message
news:OlLOwtE1DHA.1336@.TK2MSFTNGP12.phx.gbl...
> I'm intending to create a stored procedure with a temporary table with
that
> values and use it as a result set.
> Is it possible to make it using EXECUTE (@.SQLCOMMAND) ?
> Does any body have another idea ?
>
You could possibly use sp_executesql|||Hi
Say if your temp table which contains sqls like,
select * from table
insert into table select * from k
delete from table2
Then you can use a cursor and dynamic sql,
1. Declare @.sql nvarchar(8000)
2. Declare and open the cursor with @.sql = column from temptable (Which
contains the sql statements)
3. exec sp_executesql @.sql
4. Fetch next sql statement , the cursor repeats till status becomes -1
Thanks
Hari
MCDBA
"Rodrigo" <rborges11@.hotmail.com> wrote in message
news:OlLOwtE1DHA.1336@.TK2MSFTNGP12.phx.gbl...
> Dears,
> I have a table with SQL Commands in it, and I want to create a result set
> with the values powered by these commands.
> I'm intending to create a stored procedure with a temporary table with
that
> values and use it as a result set.
> Is it possible to make it using EXECUTE (@.SQLCOMMAND) ?
> Does any body have another idea ?
> Thanks in advantage,
>
> Rodrigo.
>|||Dear Uri, Dan and Hari,
Thanks for your help. The solution works very well.
Rodrigo.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eO4mN3E1DHA.3216@.TK2MSFTNGP11.phx.gbl...
> Hi
> Say if your temp table which contains sqls like,
> select * from table
> insert into table select * from k
> delete from table2
> Then you can use a cursor and dynamic sql,
> 1. Declare @.sql nvarchar(8000)
> 2. Declare and open the cursor with @.sql = column from temptable (Which
> contains the sql statements)
> 3. exec sp_executesql @.sql
> 4. Fetch next sql statement , the cursor repeats till status becomes -1
> Thanks
> Hari
> MCDBA
>
> "Rodrigo" <rborges11@.hotmail.com> wrote in message
> news:OlLOwtE1DHA.1336@.TK2MSFTNGP12.phx.gbl...
> >
> > Dears,
> >
> > I have a table with SQL Commands in it, and I want to create a result
set
> > with the values powered by these commands.
> >
> > I'm intending to create a stored procedure with a temporary table with
> that
> > values and use it as a result set.
> >
> > Is it possible to make it using EXECUTE (@.SQLCOMMAND) ?
> >
> > Does any body have another idea ?
> >
> > Thanks in advantage,
> >
> >
> > Rodrigo.
> >
> >
>