Can Kill Command undo changes outwith a transaction?

In SQL 2000 I have a problem whereby it appears that a process successfully
saved data (inserts followed by updates), hung, and when I killed the proces
s
some of the data disapeared. I had understood that only declared transaction
s
would be rolled back, but in this case it appears that untransacted changes
were undone.
When I say that the process successfully saved data, the data was read from
the database in a separate program (Crystal Reports) and was printed out.
By "the data disappeared", I mean that the inserted rows are no longer in
the database, and the rows that were updated (according to the hard copy) ar
e
in a previous state. The tables involved in this have insert/update/delete
triggers that record all changes; they do not record the changes that are
involved, and have been working without fault for several months.
The updates were grouped in transactions, whereas the inserts were not.
There was no over-all transaction.
Sure, at first glance you might think it's just a transaction that did not
complete and that I'm overlooking something, however I have the print out of
the missing data to show the data was in there.
Can someone confirm or refute that my interpretation of what happened is
possible, or suggest an alternative explanation?My guess is the report was run under the Read Uncommitted isolation level
(or used NOLOCK). As such it would see the changes if run while the
transaction was in progress and they would have been rolled back when you
killed it.
Andrew J. Kelly SQL MVP
"woodk" <woodk@.discussions.microsoft.com> wrote in message
news:F6E9C8E0-BD1C-49BF-BDB8-094BFC68C964@.microsoft.com...
> In SQL 2000 I have a problem whereby it appears that a process
> successfully
> saved data (inserts followed by updates), hung, and when I killed the
> process
> some of the data disapeared. I had understood that only declared
> transactions
> would be rolled back, but in this case it appears that untransacted
> changes
> were undone.
> When I say that the process successfully saved data, the data was read
> from
> the database in a separate program (Crystal Reports) and was printed out.
> By "the data disappeared", I mean that the inserted rows are no longer in
> the database, and the rows that were updated (according to the hard copy)
> are
> in a previous state. The tables involved in this have insert/update/delete
> triggers that record all changes; they do not record the changes that are
> involved, and have been working without fault for several months.
> The updates were grouped in transactions, whereas the inserts were not.
> There was no over-all transaction.
> Sure, at first glance you might think it's just a transaction that did not
> complete and that I'm overlooking something, however I have the print out
> of
> the missing data to show the data was in there.
> Can someone confirm or refute that my interpretation of what happened is
> possible, or suggest an alternative explanation?|||Thank you for your suggestion. The operations were not wrapped in a single
transaction, and some of them should have been atomic in their own right.
However as we've found an indication that Crystal does indeed read
uncommitted, we're investigating the possibility that an earlier transaction
by that user may not have been properly committed. If this is the case, it
may be that this was also what led to the situation where I had to issue the
Kill command in the first place.
"Andrew J. Kelly" wrote:

> My guess is the report was run under the Read Uncommitted isolation level
> (or used NOLOCK). As such it would see the changes if run while the
> transaction was in progress and they would have been rolled back when you
> killed it.
> --
> Andrew J. Kelly SQL MVP

Can I use IIF statement in the RS query?

I tried to insert this line of code to the Column in the query design but it
doesn't work. Similar command works in MS-ACCESS.
IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE), 0)
Can someone help, thanks in advance.You can put this kind of formula in the textbox that displays the data. The
query design is limited to the back-end functionality, and Access has some
extra VB capabilities most databases don't support. But the reporting front
end does support this.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"LV" <LV@.discussions.microsoft.com> wrote in message
news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
>I tried to insert this line of code to the Column in the query design but
>it
> doesn't work. Similar command works in MS-ACCESS.
> IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> 0)
> Can someone help, thanks in advance.|||In addition to what Jeff says. What database are you going against? If it is
against Access MDB then you might be able to use the generic query window
(versus the graphical). This is basically passthrough window. It depends on
how the Access OLEDB provider handles it. If it is against SQL Server data
then this will definitely not work since it is not SQL Server SQL format. If
going against SQL Server then you can always test out your SQL using the
Query Analyzer.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> You can put this kind of formula in the textbox that displays the data.
The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting
front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>|||Thank you,
Yes I am using Access data, as you suggested I will try the textbox method.
"Jeff A. Stucker" wrote:
> You can put this kind of formula in the textbox that displays the data. The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>
>|||Thank you,
I did try the generic SQL but did not get the correct results somehow it
return just he true side of the IIF statement. Here is how I get around my
problem, I created the query with the IIF statemet in Access, on RS report I
connect to the query and it works. I don't know if this is the correct way
to do it but for now at least I can get it to work.
"Bruce L-C [MVP]" wrote:
> In addition to what Jeff says. What database are you going against? If it is
> against Access MDB then you might be able to use the generic query window
> (versus the graphical). This is basically passthrough window. It depends on
> how the Access OLEDB provider handles it. If it is against SQL Server data
> then this will definitely not work since it is not SQL Server SQL format. If
> going against SQL Server then you can always test out your SQL using the
> Query Analyzer.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > You can put this kind of formula in the textbox that displays the data.
> The
> > query design is limited to the back-end functionality, and Access has some
> > extra VB capabilities most databases don't support. But the reporting
> front
> > end does support this.
> >
> > Cheers,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "LV" <LV@.discussions.microsoft.com> wrote in message
> > news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> > >I tried to insert this line of code to the Column in the query design but
> > >it
> > > doesn't work. Similar command works in MS-ACCESS.
> > >
> > > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
> GETDATE),
> > > 0)
> > >
> > > Can someone help, thanks in advance.
> >
> >
>
>

Can I store the result set of sp_who command in a table

Hi,
I want to loop through the records of the result set of sp_who.
Can this be done? How it can be done?
Regards,
Sevugan.C

code:

create table #tmpSpWho
(
spid smallint
,ecid smallint
,status nchar(30)
,loginame nchar(128)
,hostname nchar(128)
,blk char(5)
,dbname nchar(128)
,cmd nchar(16)
)
insert into #tmpSpWho
exec sp_who

HTH,
Cristian Lefter, SQL Server MVP
"Sevugan" <
Sevugan@.discussions.microsoft.com>
wrote in message
news:41413280-9A1A-481D-9FC8-31642E546811@.microsoft.com...
>
Hi,
>
I want to loop through the records of the result set of sp_who.
>
Can this be done? How it can be done?
>
>
Regards,
>
>
Sevugan.C|||You can create a temporary table to grab the result from the sp sp_who2.
Example:
use northwind
go
create table #t1 (
spid int,
status varchar(255),
login sysname,
hostname sysname,
blkby sysname,
dbname varchar(128),
command varchar(255),
cputime int,
diskio int,
lastbatch varchar(35),
programname varchar(128),
spid_1 int
)
go
insert into #t1
exec sp_who2
go
select
*
from
#t1
order by
login
go
drop table #t1
go
AMB
"Sevugan" wrote:

> Hi,
> I want to loop through the records of the result set of sp_who.
> Can this be done? How it can be done?
> Regards,
> Sevugan.C|||If you are wanting to do this on the client side, you can call sp_who into
an ADO recordset the same as any stored procedure.
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:41413280-9A1A-481D-9FC8-31642E546811@.microsoft.com...
> Hi,
> I want to loop through the records of the result set of sp_who.
> Can this be done? How it can be done?
> Regards,
> Sevugan.C|||Create TABLE #WHO
(
spid int,
ecid int,
Status Nvarchar(20),
LoginName Nvarchar(30),
Hostname Nvarchar(30),
blk int,
dbname Nvarchar(30),
cmd Nvarchar(255)
)
Insert #who
EXEC sp_who
Select * from #who
Create cursor to loop through data with whatever you want to do with the dat
a.
Hope this helps.
"Sevugan" wrote:

> Hi,
> I want to loop through the records of the result set of sp_who.
> Can this be done? How it can be done?
> Regards,
> Sevugan.C

Can I pass a SqlParameterCollection object into a SQL Command

I currently have a connection class that handles all of my database connectivity. What I am trying to do is build a SqlParameterCollection object on one page, then pass that object to my connection class. Is it possible to do this? I am not getting any compilation errors, but I can not get the parameters to be recognized. Here is what I am trying to do:

Page 1:

string sql = null;
conn.strConn = connectionstring;
sql = "sqlstring";
SqlParameterCollection newcollect = null;
newcollect.Add("@.Switch",1);
conn.OpenReader(sql, newcollect);
while (conn.DR.Read())
{
read data onto page here...
}
conn.CloseReader();
Page 2 (connection class) :
public void OpenReader(string sql, SqlParameterCollection collect)
{
Conn = new SqlConnection(strConn);
Conn.Open();
Command = new SqlCommand(sql,Conn);
Command.Parameters.Add(collect); <--This is the root of my question
Command.CommandTimeout = 300;
// executes sql and fills data reader with data
DR = Command.ExecuteReader();
}

Can you do this? And if so, can anyone tell me why the statement will not return any data? The procedure works perfectly, and will work if I use the standard way of passing the parameters to the command statement.In case anyone runs into the same issue I did, I am posting my solution to this problem. From what I have read, you can not create(instantiate) a new SqlParameterCollection class on a page. If anyone needs to see the solution to return a dataset, just reply to this post, and I will post the dataset results as well. The workaround that I used to solve this problem involves using an Arraylist and passing the arraylist to the connection class:

clsConn conn = new clsConn();
string sql = null;

conn.strConn = connectionstring;

sql = "sqlstring";

SqlParameterCollection newcollect = null;

//newcollect.Add("@.Switch",1); <-- Different
param =new SqlParameter("@.Parameter", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "Switch", DataRowVersion.Current, ParameterValue); <-- Different
conn.SQLParamCollection.Add(param); <-- Different

conn.OpenReader(sql);

while (conn.DR.Read())

{

read data onto page here...

}

conn.CloseReader();

Page 2 (connection class) :

private SqlConnection Conn; // provides connection for server
private SqlCommand Command; // provides sql command object
private SqlDataAdapter da;
private DataSet ds;
public SqlDataReader DR; // provides data reader for sql
public System.Collections.ArrayList SQLParamCollection = new System.Collections.ArrayList();
public string strConn = null;

public void OpenReader(string ProcedueName)//, System.Collections.ArrayList param)
{
Conn = new SqlConnection(strConn);
Conn.Open();
Command = new SqlCommand(ProcedueName,Conn);
Command.CommandType = CommandType.StoredProcedure;

if(SQLParamCollection.Count > 0)
{
Different-->for(int i=0; i < SQLParamCollection.Count; i++)
{
Different-->Command.Parameters.Add(SQLParamCollection[i]);
}
}

Command.CommandTimeout = 300;

// executes sql and fills data reader with data
DR = Command.ExecuteReader();
}|||You may have to use an ArrayList in this context.

Also, have a look at SqlCommandBuilder.DeriveParameters.

Can I move some column position by using SQL command ?

I created SQL table follow by XSD file

And when any users added new column to XSD in ordinal position = 3

But after my program successfully created new column, its position is the last position

What can I do ??

I suspect why I can't set it (I've looked for solution on MSDN already)

even though

We can see ordinal position by

this query

SELECT *
FROM INFORMATION_SCHEMA.Columns

What can I do for solving ?? Help me please

You're adding columns dynamically? They will go to the end and I'd be surprised if there was any way to change the position.

But more importantly, why are you adding columns dynamically and why do you care what position the columns are in? It makes no difference what the physical col position is in a table