Mashed Articles

Ok, I’m talking about temporary tables and yes,it could happen to you even you think that the temporary table is no longer exists. Consider the below script where stored procedure inserts the data with wrong type which caused the error(245conversion error). As you propably know we would never name the constraints for local temporary tables (as opposite to permanent tables), because if we run the following statement from two connections we get something like that.
/*
Msg 2714, Level 16, State 4, Line 1
There is already an object named ‘PK_tmp1′ in the database
*/

USE master
GO
ALTER PROCEDURE spSample
AS
CREATE TABLE #tmp(
col int –PRIMARY KEY NONCLUSTERED–,
CONSTRAINT PK_tmp1 PRIMARY KEY NONCLUSTERED
(
col
)
)
BEGIN TRY
INSERT INTO #tmp SELECT ‘A’
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
END CATCH

–Usage
USE master
EXEC spSample

USE tempdb
SELECT * FROM sys.objects
WHERE OBJECT_NAME(OBJECT_ID)like ‘%#tmp%’

Try add DROP TABLE #tmp in BEGIN CATCH ..block and then SQL Server deletes the constraint as well.

If you want to know who alters/drops/creates tables/views/stored procedures… I would like to share with you the following script. As you know DDL Triggers (introduced in SQL Server 2005) work very like the DML triggers but details of the event that fired the a trigger are available only in XML format.

Fist of all I create a table that will hold events.

CREATE TABLE [dbo].[DDL_ChangeEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Session_ID] [int] NOT NULL CONSTRAINT [DF_ddl_change_Session_ID] DEFAULT (@@spid),
[Session_IPAddress] [nvarchar](50) NULL,
[Insert_Date] [datetime] NOT NULL CONSTRAINT [DF_ddl_change_Insert_Date] DEFAULT (GETDATE()),
[Username] [nvarchar](100) NOT NULL CONSTRAINT [DF_DDL_change_Username] DEFAULT (CONVERT([nvarchar](100),ORIGINAL_LOGIN(),(0))),
[EventType] [nvarchar](200) NULL,
[objectName] [nvarchar](200) NULL,
[objectType] [nvarchar](200) NULL,
[sql] [nvarchar](max) NULL
) ON [PRIMARY]

It wont help if I get only SPID of the session as in many cases users get logged with only one defined login or even with ‘sa’.So I need IP address of those workstations thus I added Session_IPAddress column.
Now, let’s create a database trigger to capture the info.

CREATE TRIGGER [trgDataDDLChangeEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @eventdata XML
SET @eventdata = EVENTDATA()
IF @eventdata.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(200)’)
‘CREATE_STATISTICS’
INSERT INTO DDL_ChangeEvents
(
EventType,
ObjectName,
ObjectType,
[sql] ,
Session_IPAddress
)
SELECT @eventdata.value(‘(/EVENT_INSTANCE/EventType)[1]‘,
‘nvarchar(200)’),
@eventdata.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘,
‘nvarchar(200)’),
@eventdata.value(‘(/EVENT_INSTANCE/ObjectType)[1]‘,
‘nvarchar(200)’),
@eventdata.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘,
‘nvarchar(max)’), client_net_address
FROM sys.dm_exec_connections WHERE session_id=@@SPID
;

Well I won’t bother to record CREATE STATISTIC events hence there is an IF block to skip this event. I get the IP Address from sys.dm_exec_connections DMV which has client_net_address column.
Now create/drop/alter table (also via SSMS) for example and query the DDL_ChangeEvents table to see what happened.

Writen by Elizabeth Morgan

The information age has been revolutionized not just by the power of computers to process data, but also by the power of communication amongst computers. This communication is responsible for the proliferation of computer networks. Sharing of information, sharing of resources (both software and hardware) and sharing of processing load are some of the major objectives of a computer network. One of the recent trends in the computer and communication industries is wireless communication. A wireless network does not use cables, but instead uses radio waves. Such networks enhance the functionality of computing devices by freeing them from the location constraints of a wired network. Although wireless networking is of great use to the laptop users who move from one location to the other throughout the day, there are benefits for fixed users as well. Many schools and businesses have an architecture that is unsuited for a wired network. Wired networking is very cost effective in such environments.

Wireless networks can be broadly classified as fixed wireless systems and mobile wireless systems. A fixed wireless system supports a little or no mobility of the equipment related to the wireless network. For example, a local area network can be set up using wireless network to get rid of the hassles of laying cables. The mobile wireless systems support mobility of the equipment, enabling the user to access information from anywhere and at any time. The equipments used in the mobile wireless systems include personal digital assistants (PDAs), smart phones and pager with Internet access.

Wireless networks communicate by modulating radio waves or pulsing infrared light. A wireless networking router is linked to the wired network infrastructure by stationary transceivers. Wireless communication technologies used in wireless networks include analog cellular, digital cellular, Cellular Digital Packet Data (CDPD), mobile radio networks, personal communication services (PCS), satellite, microwave systems and local multipoint distribution systems (LMDS). Although the data rates supported by analog cellular technology are low, it is the most popular methods for wireless data communications due to the wide availability of modems and analog cellular services. The commonly used wireless technologies include 2G and 3G technologies, wireless LANs, Wireless Local Loops (WLLs), radio-router technology, Multihop wireless network and Wireless Application Protocol (WAP).

Wireless Networks provides detailed information on Wireless Networks, Home Wireless Networks, Configuring Wireless Networks, Wireless Network Cards and more. Wireless Networks is affiliated with Wireless Internet Services.

Hospital in Tennessee Thailand Hotels