NET_ADDRESS In The Master.dbo.sysprocesses Table
Oct 24, 2006Does 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 ?
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 ?
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 ...
Hi,Is it possible to know workstation name from data in columnnet_address from master..sysprocesses?TIA
View 2 Replies View RelatedHi 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 RelatedHi,
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.
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.
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???
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 RelatedI'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?
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.
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
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 RelatedIn 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
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.
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!
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.
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
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,... ????
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 RelatedGuys,
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,
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.
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!!!!!!!!!!!!!!!!!!
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
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 RelatedHi 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.
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.
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
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 RelatedDear 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
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
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?
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 ?
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.