How To Convert Sp_who In A Table/query.

Sep 12, 2007

Hi

How to convert a sp_who in into a table/query

Please help



Raj.

View 2 Replies


ADVERTISEMENT

How To Convert Sp_who In A Table/query.

Sep 12, 2007

Hi

I would like to convert sp_who into a table/query. Please help

Thanks

View 3 Replies View Related

SQL Server 2005 - How To Convert A Query Into A Table?

Sep 11, 2007

I'm going through and restructuring a database scheme and was wondering if, after I make a query (view), how can I convert that into a table?

Thanks

View 4 Replies View Related

Convert XML Data With Select Query And Insert Into Table?

Jun 19, 2014

I want to convert xml data with select query and insert into table.

Xml sample like this :

<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>

[code]....

And data should be like this with select query :

NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL

I want simple query in form of above format.

View 8 Replies View Related

Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column

Jan 15, 2008

I have detail table like

date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8


Result View
=======

Item_id A B O T Total


001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18

where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.

Smartsys

View 4 Replies View Related

T-SQL (SS2K8) :: Pivot Query - Convert Data From Original Table To Reporting View

Apr 8, 2014

I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.

Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14

View For Reporting:

Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId

Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14

View 6 Replies View Related

Sp_who

Jun 4, 2002

Hi,
How can I save result of sp_who into temp table or variables?

Thank you,
Elena

View 1 Replies View Related

Sp_who

Jul 17, 2002

Hi!
I want schedule a job to run sp_who at 5 a.m.
How can I save the sp_who result to check it later.

Thank you,
Elena

View 2 Replies View Related

Sp_lock And Sp_who

Mar 16, 2004

Is there any way to write a line to a table showing who had a lock at a particular time?

We have a need to rollback transactions that persist for too long and cause application errors. what i want to do is to dump the details of the connection into a table for analysis later.

i am thinking along the lines of
SELECT loginname
FROM sp_who
WHERE spid in
(
SELECT spid
FROM sp_lock
)
or something!!!!

any ideas anyone?

View 3 Replies View Related

SP_Lock && SP_Who

Dec 13, 2005

Hi!

I need some examples of SP_Lock & SP_Who.

Best Regards,
Shabber Abbas Rizvi.

View 1 Replies View Related

Sp_who And Citrix

Jul 31, 2006

I have some users on Citrix, running an Access front end with ODBCattached tables.In sp_who. on a normal workstation they'd usually show once or maybemore but all with the same hostname so counting actual number of usersis easy, group on loginame,hostname.When someone connects from a Citrix server, I append the connectionnumber to the workstation name in the connect string so the hostnamelooks something like "CITRIXBOXICA-tcp#256" also for terminal servicesthe hostname will look like "GANDALFRDP-Tcp#8".The problem is that on Citrix the user appears twice with a differenttcp number, the hostname is made up of the name of the citrix/ts server+ the environment variable %SESSIONNAME%, which is surely static for asession?Has anyone seen this in Citrix? Does it ghost old connections? I loogedout and in again just now and saw the sessions look like:CITRIXBOXICA-tcp#199 & CITRIXBOXICA-tcp#254 for my user name thenafter re-logging on I got CITRIXBOXICA-tcp#199 & CITRIXBOXICA-tcp#256so that session 199 has stuck there but wasn't visible in sp_who whilenot logged into the Citrix box.

View 1 Replies View Related

Sp_who And Other Stored Procedure?

Jun 16, 2004

Hi,

Beside sp_who which shows the number of connections to databases,
is there any other sp that shows us the starting time for each connection because the
sp_who does not show the starting of the connection.

Thanks.

View 1 Replies View Related

&#39;sp_who&#39; Status Question...

May 11, 2000

Hi,

I'm using SQL SERVER 7.0. When I do sp_who from Query Analyser to see who has processes running, I get three statuses on the processes, 'runnable', 'background', 'sleeping'.
I can't find anywhere in books, BOL, anywhere that tells me a definition of what each of these statuses mean.
Can anyone help me out?

Thanks,
Darrin

View 1 Replies View Related

Difference Between Sp_who And Sp_who2

Apr 2, 2008

Dear All,
i've a doubt regarding the difference between sp_who and sp_who2.

i've read in one article stating that sp_who is documented and sp_who2 is un documented.what is meant by this?

and please provide me the articles related to locks(what are different types of locks)

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 4 Replies View Related

Fetch Result Of Sp_WHO

Feb 1, 2007

Hello All,
I need to find out which user are connected to a What database. For this I can Use SP_WHo. But How can i fetch the required fields only dat too in Query Analyzer

Thank you in advance

View 1 Replies View Related

Problem With Spid By Sp_who

Nov 30, 2005

Hello 2 all,Maybe my question can be very stupid, but I'm a little confused.When I run sp_who on my database, I see one process (accessing remotelymy database from another database on another SQL server) many manytimes.Well, I assume that this is one process, because I cannot imagin thatthe data aquiring can be done by almost 1000 (thousand) openedconnections.This is what I have. Almost 1000 spids running (sleeping) with cmd =awaiting command.The best part is, that when I run sp_lock, I see, that the currentsession of the client, (lets say spid 53) is locking tempdb, withobjects id below 10 (system).As far as i know, and as far as I'm using this in my software, there isno necessity to open-close-open-close connection.Normaly, you can just open a connection to db, with assigned spid, andoperate within this just one. After the disconect ist done, the sessionshoould be removed.I think, there is something wrong in the way, the remote system isconnecting to my database, but i'm not sure is it normal what I'mexpieriencing or not.below, a piece of that what sp_who returned:SPIDECIDstatusloginnamehostnameblkdbnamecmd2660sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2670sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2680sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2690sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2700sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMAND2710sleeping remote_user_namehost_ERP 0PRIMARY_PROCESS_DBAWAITING COMMANDand these are locks from sp_lock 53 which is the current runningprocess:53700DB SGRANT53260TAB IXGRANT53210TAB IXGRANT53230TAB IXGRANT53220TAB IXGRANT532120TAB IXGRANT53290TAB IXGRANT532110TAB IXGRANT53232KEY(1902141b21c7) XGRANT53213KEY(e60041ad2c6f) XGRANT53232KEY(5b0233670fb9) XGRANT53232KEY(6e0212a7298c) XGRANT53232KEY(97013ece81c8) XGRANT53231KEY(e7006f987fb0) XGRANT53213KEY(ad00a33f46b9) XGRANT53210643560700TAB Sch-MGRANTany idee?ThanksMateusz

View 1 Replies View Related

Change Login-Info For Activity-Monitor And Sp_who

Sep 26, 2007

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas
DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.
Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.
Thomas

View 4 Replies View Related

User Defined Function: Convert String Value Of Table To Table Object

Jul 20, 2005

Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik

View 2 Replies View Related

MS Access Query That I Would Like To Convert To MS SQL Server Query

Jun 28, 2005

I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.


Code:

SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate

View 3 Replies View Related

Convert Access Query To Sql 2000 Query

Jan 30, 2008

I'm having trouble converting this access query into a sql 2000 query.
Your help would be appreciated:

IIf(nz([PTicketNum],"M999Z")="0","M999Z",IIf(Trim(nz([PTicketNum],"M999Z"))="","M999Z",nz([PTicketNum],"M999Z")))

Here is what I have, but I'm not confident it is correct:
CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket

View 1 Replies View Related

How To Convert Foxpro Table In To MSSQL Table

Dec 7, 2005

Hi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
 
 

View 4 Replies View Related

How To Convert Verical Table To Horizontal Table?

Mar 4, 2008

I’ve a vertical table as follows: CMDATA

IDFormIDRecordCountFieldNameValue
15251204 Name John
25881204 OrganizationGE
35251257 Name Peter
45881257 OrganizationIntel
55251272 Name Rita

And a horizontal table as follows: CMFORM

IDFormIDRecordCount
15251204
25251257
35251272
45881204
55881257

Where
CMDATA.FormID = CMFORM.FormID and
CMDATA.RecordCount = CMFORM.RecordCount

Now I want select records as follows:

RecordCountNameOrganization
1204 JohnGE
1257 Peter Intel
1272 RitaNULL

I’ve written following query for that but it works properly only if all the record count has similar fieldnames.

SELECTCMCF.RecordCount,
CMD1.VALUE AS Name,
CMD2.VALUE AS Organization
FROMCMDATA CMD1
JOIN CMFORMS CMCF ON CMD1.FORMID = CMCF.FORMID AND CMD1.RECORDCOUNT = CMCF.RECORDCOUNT
JOIN CMDATA CMD2 ON CMD2.FORMID = CMCF.FORMID AND CMD2.RECORDCOUNT = CMCF.RECORDCOUNT
WHERECMD1.FIELDNAME = 'Name'
AND CMD2.FIELDNAME = 'Organization'
AND CMCF.RecordCount in(1204,1257,1272)

Is there any other way to handle this?

View 3 Replies View Related

How To Convert Verical Table Into Horizontal Table

Mar 4, 2008

I’ve a vertical table as follows: CMDATA

IDFormIDRecordCountFieldNameValue
15251204 Name John
25881204 Organization GE
35251257 Name Peter
45881257 OrganizationIntel
55251272 Name Rita

And a horizontal table as follows: CMFORM

IDFormIDRecordCount
15251204
25251257
35251272
45881204
55881257

Where
CMDATA.FormID = CMFORM.FormID and
CMDATA.RecordCount = CMFORM.Record Count

Now I want select records as follows:

RecordCountNameOrganization
1204 JohnGE
1257 PeterIntel
1272 RitaNULL

I’ve written following query for that but it works properly only if all the record count has similar fieldnames.

SELECTCMCF.RecordCount,
CMD1.VALUE AS Name,
CMD2.VALUE AS Organization
FROMCMDATA CMD1
JOIN CMFORMS CMCF ON CMD1.FORMID = CMCF.FORMID AND CMD1.RECORDCount = CMCF.RECORDCount
JOIN CMDATA CMD2 ON CMD2.FORMID = CMCF.FORMID AND CMD2.RECORDCount = CMCF.RECORDCount
WHERECMD1.FIELDNAME = 'Name'
AND CMD2.FIELDNAME = 'Organization'
AND CMCF.RecordCount in(1204,1257,1272)

Is there any other way to handle this?

View 3 Replies View Related

Convert - Query Help

Mar 19, 2008

how can i convert

4332 3423 5423 5428

And convert it to

XXXX XXXX XXXX 5428

View 2 Replies View Related

Convert Columns-Row. Please Help Query!

Jul 11, 2001

My table:

Table1
T1T2T3T4T5
---------------
abcde

How to query to have following result:

Table2
abcde
---------------
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5

Thanks in advance.
Janasha H

View 1 Replies View Related

Datetime Convert Query

May 1, 2008

declare @dt varchar(20)
select count(s.sopnumbe) as orders from sop30300 s
left outer join sop30200 ss
on s.sopnumbe = ss.sopnumbe
where s.itemnmbr=ss.sku and s.soptype = 2 and ss.docdate=@dt

well docdate is datetime and dt is varchar
how can i compare - both?

View 6 Replies View Related

Convert Access Query To Sql

Aug 17, 2007

I am being asked to convert this access query into sql server 2000.
Access query
SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum
FROM tri_offnotes;

I'm new to SQL and am not familiar with which function replaces the InStr access function.

View 2 Replies View Related

Trying To Convert Query From T-SQL To Access

May 9, 2008

First off, I apologise if this is classed as off topic as it's concerning access but I couldn't see another forum that was better suited for this question.

I'm trying to run this query in Access. I designed it in SQL Management Studio with a test database that I set up and it runs fine. However, when I tried to run it in access I get a syntax error on the subquery bit. Can anyone tell me what I'm doing wrong here?

UPDATE tblInvoice
SET tblInvoice.PeriodID =
(SELECT p.PeriodID FROM tblPeriod p
INNER JOIN
tblInvoice i
ON i.TransDate BETWEEN p.PeriodStartDate AND p.PeriodEndDate
WHERE i.InvoiceID = tblInvoice.InvoiceID)

Thanks in advance,

Andy.

View 3 Replies View Related

Query To Convert String To Int

Oct 4, 2007



CREATE TABLE USERTABLE(
USERID int,
USERNAME varchar(20));

INSERT INTO USERTABLE VALUES (1,'x');
INSERT INTO USERTABLE VALUES (2,'y');
INSERT INTO USERTABLE VALUES (3,'z');


CREATE TABLE ACCESSTABLE(
ACCESSTYPE varchar(50),
USERIDS varchar(20));

INSERT INTO ACCESSTABLE VALUES('Enabled Users','1,2');
INSERT INTO ACCESSTABLE VALUES('Disabled Users','3');

Select USERNAME from USERTABLE where USERID IN (select USERIDS from ACCESSTABLE where ACCESSTYPE='Enabled Users');


I get the following error for the above query.

Syntax error converting the varchar value '1,2' to a column of data type int.


Please advice me.

View 1 Replies View Related

Convert Access Query To Sql

Apr 10, 2007

how to convert this access query to sql query



IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2

SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1

IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name



please......

View 3 Replies View Related

Convert A Record From Table A Into Table B

Apr 12, 2008

Pls advise how to "convert" a record from table A into table B (to perform similar "transpose" function in excel)

e.g a record in Table A with a key column & a number of data columns.
key: 1
col_1:A
col_2:B
col_3:C
...
col_26:Z

become in Table B with a key column & table A column name & data value.
key col value
1 col_1 A
1 col_2 B
...
1 col_26 Z

thx

View 8 Replies View Related

Convert Table Using DTS

Jul 20, 2005

Hi,how to:using dts convert row in table to column.i have table:col1,col2b1 , 1b2 , 2b1 , 4b3 , 3b2 , 5and i want using dts convert/rewrite this table to another table:b1,b2,b31,null,nullnull,2,null4,null,nullnull,null,3null,5,null

View 1 Replies View Related

Convert Table

May 9, 2008

I have the following table:


RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8

etc....



I need to transform it to this:


1 2 3

Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8



Can this be done in SQL?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved