NET_ADDRESS In The Master.dbo.sysprocesses Table

Oct 24, 2006

Does anyone knows how the field NET_ADDRESS in the master.dbo.sysprocesses table is encoded ?
May I extract the IP address or MAC address from that field ?

View 9 Replies


ADVERTISEMENT

SQL SERVER 2000 - Table SYSPROCESSES

Mar 8, 2004

Hello friends,

Despiste what it is mentioned in B.O.L., in the Table SYSPROCESSES, the column KPID has almost always the value 0 !!!

If anyone knows how I can find the right link between a SPID and the corresponding KPID, I shoud be very happy to know to.

It concerns SQL SERVER 2000 under WINDOWS 2000 SP3.

Thanks in advance for any help about that ...

View 1 Replies View Related

How To Find Out Workstation Name From Net_address

Jul 20, 2005

Hi,Is it possible to know workstation name from data in columnnet_address from master..sysprocesses?TIA

View 2 Replies View Related

Sysprocesses

Jul 20, 2005

Hi everyone,I'm a bit new til ms sql server and hope that anyone here can answer aquestion I have. I'm running a ms sql server 7.0. The server isaccessed by a application written in Access (help..) and sometimes Iexperience that the server performance is slowing down. When I do asp_who I find alot of the processes (or sids) with the statussleeping? Can anyone please tell med what this means, and is it ok forme to kill these processes? Since this is happening quite often Iwould like to make a script that automatically kills all sleepingprocesses, is this ok?Looking forward to hearing from anyone!B'regSql server newbee

View 2 Replies View Related

Sysprocesses.waittype

May 24, 2004

Hi,

The column waittype of master..sysprocesses table contains binary numbers.
I am looking for their codes. I know that Microsoft has published these codes for SQL Server 7 (here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;244455 ), but I need the codes of SQL Server 2000.

And yes, I've checked spt_values - they are not there.


Any help will be appreciated,
Thanks,
kukuk.

View 4 Replies View Related

How To See All Sysprocesses In SQL 2005?

Sep 11, 2007



My application was written in SQL 2000 where a non-dbo account could see all logged in users. Is there some way I can do this in SQL 2005?
Here is the query I run in SQL 2000:
SELECT spid
FROM master.dbo.sysprocesses, master.dbo.syslogins
WHERE master.dbo.sysprocesses.sid = master.dbo.syslogins.sid
AND master.dbo.sysprocesses.dbid = db_id()
AND LOWER(master.dbo.syslogins.name) IN ('grnl_security','log_security')


Right now, my application (where many users log in with gnrl_security) can only see thier own SPID; but I need to see all logged on SPIDS.

I have tried adding a SELECT permission to the new sysprocesses view for user grnl_security.

View 5 Replies View Related

HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE

Dec 7, 2007

I have the following data

MASTER
id
name


DETAIL
id
master_id
name

I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View 1 Replies View Related

Incorrect Hostname In Sysprocesses

Jun 27, 2000

On SQL Server 6.5, we tried to track down the owner of a process,which was hogging the CPU. Using Currently Activity Detail, we went to the hostname that seemed to be the culprit, but the users had not accessed SQL Server in a few days. Where is SQL Server get the hostname from? We are on an NT4.0 network, using DHCP.

View 1 Replies View Related

Sysprocesses Says: Wait Forever

Sep 30, 2004

I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.

Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

What should I do?

View 6 Replies View Related

Joining Sysprocesses With Fn_get_sql

May 16, 2006

ok, I have a requirement where I need to get a list of sql commands currently being blocked.
This is very easy to do via stored procedure, and I have it working well using a vb.net console app to fire it off.

Trouble is, I need to ship it to different offices on an ad hoc basis. I don't want to install a stored procedure on each site because it'll be a one off job and there is not likely to be anyone available who would know how to even install a new sp. So, I thought I'd try and pull back the sql commands via a select statement, joining together sysprocesses and the fn_get_sql udf. This returns a table, so I presumed I could join the two together using a subquery via the sql_handle with something like this :

SELECT sql_handle,
(
SELECT top 1 [text] FROM ::fn_get_sql(sysprocesses.sql_handle)
) as sqlcommand

FROM master..sysprocesses

The error back is incorrect syntax near 'sysprocesses'. I can't see if I'm doing anything obvious wrong.

Anyone any ideas? I'm using SQL Server 2000 sp3.

View 6 Replies View Related

SysProcesses Login Time

Apr 1, 2008

Hi,

We have written various applications that have a connection table for managing users logins, we have a connect stored proc that inserts the SIPD and the login time from sysprocesses.

From time to time we clean up our connections table by queryng against the SPID and login_time in sysprocesses and clear it if these don't match. This has been fine for a few years, howver we have found recently with a client that with some SPID's in sysprocesses that SQL server is changing the login time, this is unexpected behaviour.

The client is using SQL server 2005 and this has caused us problems, we have worked around it for now but wondered if anyone new why on this particular setup why the login time would change even though they have not disconnected.

We have other clients using SQL 2005 but have never seen this issue before, I wondered if there was a setting we have not seen that can cause this.

Thanks

View 4 Replies View Related

Sysprocesses Memusage Sleeping

Nov 3, 2005

I was trying to find the bottle neck on our server while we wererunning a couple very large updates.I ran the following query and noticed there were a couple sleepingspid's that were really eating up the memory. One had a value of16000.I guess my question is was this query still holding on to that memoryeven though the query had finished? Why didn't SQL Server clean it up?select spid,substring(Loginame,1,10) Login,memusage,substring(hostname,1,15) Host,program_name,cmd,statusfrom master..sysprocessesorder by memusage desc

View 1 Replies View Related

What Happened To Sql_handle In Sysprocesses?

Sep 13, 2007

In SQL Server 2000 you can monitor active processes by looking at the sql_handle column in sysprocesses. In SQL Server 2005 BOL says sql_handle ...


Represents the currently executing batch or object.


In SQL 2000 a non-zero value in sql_handle indicated an active process. You could not simply rely on the status column to check for an actively running process. This is not the case in 2005. I ran a profiler trace to confirm that just because sql_handle reports a non-zero value does not mean the corresponding process is active. Does anyone know why this has changed and how we are now to interpret sql_handle in 2005? Why are non-zero entries reported for processes that are not currently running?


Thanks, Dave

View 1 Replies View Related

Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table

May 12, 2015

In Master tabel i have these date datas

2015-05-10

2015-05-11

2015-05-12

SO when i try to load from  Master table to parent and child table i am using using expresssion like 

select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON 
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND 
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE())  and convert(date,B.Ftpdate) = convert(date,getdate()) ;

If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.

My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.

If i run this expression to remote server  i cannot change the system date in server.

while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.

What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.

View 10 Replies View Related

How To Alter Column Length Of The Master Table Along With The Slave Table?

Aug 13, 2006

In SQL Server 2005,here are two tables, created by the following SQL Statements:

CREATE TABLE student(
ID CHAR(6) PRIMARY KEY,
NAME VARCHAR(10),
AGE INT
);

CREATE TABLE score(
ID CHAR(6) PRIMARY KEY,
SCORE INT,
FOREIGN KEY(ID) REFERENCES student(ID)
);

For the length of Column ID is not enough, So I want to alter its length.The alter statement is:

ALTER TABLE student ALTER COLUMN ID CHAR(20)

For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.

But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?

Thanks!

View 2 Replies View Related

Merging Master And Staging Table (was: Update Table Using SSIS)

Nov 20, 2007

Hi,
Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task.
Can find the new records using merge join task.

Is there better way to merge master table using staging table?

Thanks in advance.

DF.

View 1 Replies View Related

Transact SQL :: Querying Data From Master Table Into Transaction Table?

Oct 13, 2015

I am stuck in the following scenario.

Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01

[code]....

View 5 Replies View Related

Selection Of Table Based On Value Selected For The Particular Attribute In Master Table

May 29, 2008



Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected .
So how can I do this ???
How to form a realtion between the tables,... ????

View 1 Replies View Related

Finding Active Users From Sysprocesses

Jul 20, 2005

I am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1

View 2 Replies View Related

Linking Sysprocesses To Results Of DBCC Inputbuffer

May 23, 2008

Guys,
I need to send a group of people a list of specific processes running on the server, one of the requirements is to send them what's actually being ran on the machine. I have the information I was on the sysprocess tabke and the results of the DBCC Inputbuffer. Is there a way to link both result sets?

This is the criteria of the processes that neeed to be sent out to my users:

SELECT *
FROM master.dbo.sysprocesses p
WHERE last_batch < DATEADD(mi, -5, GETDATE())
AND dbo.fncGetNumLocks(p.spid, DB_ID('EngDataMart')) > 1
GROUP BY p.spid, p.loginame, p.hostname, dbo.fncGetNumLocks(p.spid, db_id('DBName'))
ORDER BY p.spid

Thanks,

View 1 Replies View Related

SQL 2005 Does Not Work With ADO Query On SYS.sysprocesses After Update Installed From Microsoft

Jul 11, 2007



str = "select nt_username, hostname, nt_domain, loginame, login_time, program_name " &
"from master.sys.sysprocesses where spid=@@SPID"



-This is the Query to display the username in the application.

-The client is MS-Access 2003, MDAC 2.8

-It was and is Ok both on SQL 2005 and SQL 2000. The qury runs under Query Analyzer 2000 or Management Studio 2005 without problem



-After Downloading the updates from Microsot Website. The embedded code returns no result set:

Data provider or other service returned an E_FAIL status



If anybody has any idea, I will be thankful.

View 8 Replies View Related

Delete A Row From Master Table

Mar 13, 2008



Hi,

I want to delete an entry from master table. The master table has three fiels.
1)Empid--primary key
2)Empname
3)Date of birth
I have another table which stores the employee salary. There is a relationship with the master table and employee salary table. The employee salary table fields are
1)Empid--Foreign key
2)Salary.

How can I delete an entry from the master table?

Please help!!!!!!!!!!!!!!!!!!

View 8 Replies View Related

Lookup From One Table To Other Master Table

Apr 21, 2015

I imported an Excel file in the form of a table(11,000 rows) in SQL Server 2012 database called Sanbox, I am trying to link this table to MS-CRM leads table so that I can link all the street addresses from table in Sandbox DB to the respective tables in MS-CRM leads table (200,000 rows), my end goal is to link the owner names from MS-CRM table to Sandbox DB's table with the matching criteria of 'street address'

My simple query looks like this, but it returns 200,000 rows, but I only want 11,000 rows -

SELECT *
FROM Sandbox.dbo.Sheet1$ A
LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B
ON A.[Street 1] = B.address1_line1

View 4 Replies View Related

How To Insert New Rows To Master Table

Jul 23, 2005

I use Ftp to import a comma delim file.Problem:Day 1 file has 5 records. During the course of Day 1 ,users makechanges to a field in the five records.Day 2 has same 5 records as they were at the beginning of Day 1(withoutchanges made during Day 1) plus 1 new record.I need a statement that will only add the 1 new record to the mastertable and leave the other 5 fields alone.I have a composite key that prevents duplicate addition.My original idea was to have a master table(Day 1) and a dailytable(Day 2) and do some kind of join that would give we only therecords in Day 2 that do not appear in Day 1 using the Composite key.This has not worked.Any ideas?

View 2 Replies View Related

Master/Detail Table Insertion.

Sep 20, 2007

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

View 6 Replies View Related

Master And Child Table Inserts At A Time.

May 15, 2007

I have a situation here. Please
advice me on this.



I have a master table and a child
table. They have a PK and FK relationship. The master table has an identity
column with auto increment set to true. This map as a FK in the child table. My
questions are:

Can I have a single form to insert a new record into
master and child table at the same time?This has to be accomplished without stored
procedures. Can it be done?Is it possible to do this with a single insert query?
If yes, can it be done with sql data source or
dataset or tableadapters’?Please point me towards appropriate link for doing
so.





Thank you.

I am currently using SQL Server
2005 and VS 2005.

View 1 Replies View Related

CREATE TABLE In Wrong Database (Master)

Apr 19, 2006

I am using ASP.NET 1.1 and  MS SQL 2005 the folowing ODBC stringconnection
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBase

I am using windows Authentication

what can be wrong ?

thank you

View 6 Replies View Related

CREATE TABLE In Wrong Database (Master)

Apr 19, 2006

I am using ASP.NET and a normal ODBC stringconnectionDriver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBasei am using windows authenticationwhat can be wrong ?thank you

View 13 Replies View Related

In Master And Detail Table Fetch Record.

Jun 24, 2008

Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements

In Master table have 5 columns; In Master Table stored all records.

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20



In second Table store those records who are selected and stored in 2nd table.
NameAppidFunctionCodeFunNameSubFunCode
Data810Summary0
Data820View0
Data830&View0
Ad630Mbl20


Our requirements we use one query,
In query fetch total 5 rows. and output show like this

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y


Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha

View 3 Replies View Related

Lookup Primary Key Of A Master Table Which Is Does Not Contains Unique Key

Mar 21, 2008



Hi
I have a strange requirement in ETL operation. My Source contains only the details table data. Out of it, I have to load the master table and refer the Master table primary key ID to load Details table. I can easily load the Master Table with aggregate transformation. But problem is how to look up the Master Table Primary Key ID to load the Details table, as the master does not contains any unique key to lookup. This may seems to be strange but this is my requirement. You can refer the Source and Destination data model as below. They may give you clear picture. Can you guys help me out on this?

My Source Table
--------------------------








Citye Type
City Name

Source
New York

Via City1
Pittsburg

Destination City
Chicago

Source
New York

Via City1
Philadelphia

Destination City
Chicago


My Destination
------------------------






Master Table

ID

1

2









Detail Table



Master Table ID
Citye Type
City Name

1
Source
New York

1
Via City1
Pittsburg

1
Destination City
Chicago

2
Source
New York

2
Via City1
Philadelphia

2
Destination City
Chicago


Thanks

View 11 Replies View Related

How To Recover Master Database Table Data

Aug 14, 2006

Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.

Here is the script:

declare @table_name sysname
declare @alter_table_statement varchar(256)
declare @delete_statement varchar(256)

-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

-- desligando os vínculos...
open table_name_cursor
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all'
exec(@alter_table_statement)
while @@Fetch_Status = 0
begin
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all'
exec(@alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @table_name
select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name))
exec(@delete_statement)
while @@Fetch_Status = 0
begin
fetch next from table_name_cursor into @table_name
select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name))
exec(@delete_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all'
exec(@alter_table_statement)
while @@Fetch_Status = 0
begin
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all'
exec(@alter_table_statement)
end
close table_name_cursor


deallocate table_name_cursor


I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.

Is there anyway to recover the data that I have lost?

View 5 Replies View Related

Master Data Services :: Table Partitions In MDS?

Apr 13, 2015

Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....

We created table managers_plan in MDS :

year
month
id_manager (domain attr)
POS (domain attr)
plan_sum_USD
plan_unit
----------------------------
Entities:
Managers ~ 800 records
POS  ~ 100 000 records

managers_plan Total records for
1 year = 100K x 12 = 1 200 000

managers_plan  - table partitions  
- Will bemade ?

View 3 Replies View Related

Master Table Differential Backup Not Working.

Oct 17, 2006

Newbie to backups here. I'm trying to setup a Maintenence Plan for backing up the system tables. For some reason the Differential backup on the Master table is failing.

Can you NOT run differential backups on the Master table?

Thanks.

View 1 Replies View Related







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