Dear everyone,
I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.
I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.
Could anyone know how to do that?? Thanks!!
From,
Roy
Hi all,
Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!
Hi,I'm a newbie to sql server and this may be a really dumb question forsome you. I'm trying to find some examples of sql server triggers thatwill set columns (e.g. the created and modified date columns) if the rowis being inserted and set a column (e.g. just the modified date column)if the row is being updated.I know how to do this in oracle plsql. I would define it as a beforeinsert or update trigger and reference old and new instances of therecord. Does sql server have an equivalent? Is there a better way to dothis in sql server?Thanksericthis is what i do in oracle that i'm trying to do in sqlserver...CREATE OR REPLACE TRIGGER tr_temp_biubefore insert or updateon tempreferencing old as old new as newfor each rowbeginif inserting then:new.created_date := sysdate;end if;:new.modified_date := sysdate;end tr_temp_biu;
View 1 Replies View Related
I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.
Here is what I did but It needs to be corrected:
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
ALTER TRIGGER CreateTopicsTableTrigger
ON dbo.Forums
AFTER INSERT
AS
SET NOCOUNT OFF
DECLARE @myNewForum varchar
CAST(@@ROWCOUNT as varchar) /*Is it OK???*/
SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/
GO
CREATE Table @myNewForum /*Will this work some how???*/
( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,
DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
I created stored procedure to create trigger file in a particular directory using xp_cmdshell.
I am calling the procedure from windows batch script as follows
@@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
@%osq200% >>%3rec.txt 2>%3rec_err.txt
@set dberr=%errorlevel%
@if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt
If the directory doesn't exist, its throwing error "The system cannot find the path specified" , but the %errorlevel% still showing as 0..
I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :
Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1
Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.
Here is the code for the stored procedure :
CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END
Hi,My requirement is i want to auto generate ID'S in sqlserver that meansin database one column is there recordid for that coloumn i want to add numbers with no duplication like JDS 10001,JDS 10002,JDS 10003............like that if any record added in database means automatically add that type of id'sif suddenly we deleted that record means that id will not add again how to do dat??
View 5 Replies View RelatedI have a table called dbo.test. This table is built with two columns, namely; col_id and col_name.. Now, I want col_id to be auto-populated when a new col_name is added. But the col_id - should be of formulae xyyyy
where x = 2
and yyyy = will be incremental
Therefore, the first 3 records will be like ;
col_id Col_name
20001 A
20002 B
20003 C
......
......
and so on
Now, is there a way SQL can auto-populate teh col_id whenever a col_name is added?
Hello,I m creating forms in ASP.Net 2005 using C# language. I'm
using Microsoft SQL Server 2005 and my IDs are in A001, A002, A003...and so on. How
can I auto generate this IDs? Like A001 +1=A002? Please help...In SQL server 2005 which datatype i should select and how can i code in ASP.NET with C#??On button click event the data is inserted and been shown onthe grid..Thanks
Hello, I have a table (publication) that has a primary key pubID which is an identity specification. This ID already has relationships to other tables.
I am having problems trying to make one of the other columns in the same table to auto generate. This column (bibNumber) is not related to any other table. All i want to do is when i create a new record in the table, auto generate a number for this column bibNumber.
How can i do this?
I am using sql server express 2005
Regards
Is there an easy way to generate the parameters code for calling stored procedures?
Is there an easy way to generate stored procedure code without having to manually type it in? I've seen 3rd party tools, but I was wondering if there's anything within Sql Server to do it.
I'm using c#, visual studio 2005, sql server 2005.
Thanks,
John
Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express?
In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?
Thanks a lot.
Hi,
Can we able to auto generate columns in sql server 2000?
I have a tabe with two fields No and Name. I need to auto generate no for each name i am going to enter. Can any give me a solution for this? If so i will be thankful to u.
This is my mail id suresh@tracy.in
Do reply me as soon as possible.
I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq
View 6 Replies View RelatedI have an app that I created and I am trying to upload the MS SQL DB to my web host. I downloaded MS SQL Server Database Publishing Wizard and then open up Visual Studio 2005. I right click my database, and then try publishing it. I convert to a MS 2000 DB (was originally 2005). I save the .sql statement and try copying into the host's (Godaddy) query analyzer. (It's only about 400k).
Well, I tried cutting and pasting the doc so I could see exactly where I get the error. I get a good part of it successful, but then I try the pasting the code below (not modified at all) and get the error about "unclosed quotation..." I skip this and go to the next process, and is successful, but often, I am getting this error.... WHY?
Please help.
Code copied directly from generated script from MS Publishing wizard:
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode int
DECLARE @RowCount int
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER(@UserName)
AND u.ApplicationId = a.ApplicationId
AND LOWER(@ApplicationName) = a.LoweredApplicationName
IF (@UserId IS NULL)
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF ((@TablesToDeleteFrom & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF ((@TablesToDeleteFrom & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF ((@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
SET @NumTablesDeletedFrom = 0
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END'
END
GO
***************************ERROR:****************************
Unclosed quotation mark before the character string 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranS...
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int ASBEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSESET @TranStarted = 0 DECLARE @ErrorCode int DECLARE @RowCount int SET @ErrorCode = 0 SET @RowCount = 0 SELECT @UserId = u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE u.LoweredUserName = LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName IF (@UserId IS NULL) BEGIN GOTO Cleanup END -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set IF ((@TablesToDeleteFrom & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V'')))) BEGIN DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set IF ((@TablesToDeleteFrom & 2) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set IF ((@TablesToDeleteFrom & 4) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set IF ((@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set IF ((@TablesToDeleteFrom & 1) <> 0 AND (@TablesToDeleteFrom & 2) <> 0 AND (@TablesToDeleteFrom & 4) <> 0 AND (@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId))) BEGIN DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0Cleanup: SET @NumTablesDeletedFrom = 0 IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCodeEND' ENDGO
Thanks in advance,
Rob
We have the following requirement :
1. In daily basis auto loop through each item in the invoice table.
2. passing invoice number into a Summary SSRS report as parameter.
3. Auto download ALL generated PDF reports into a window folder with a special file name format i.e. <INVOICE_NO>_<DATE>.pdf
how to achieve this via SSRS, Store Procedure or Power Shell?
Hey all,
1) I have a report with many parameters that I want users to be able to pick from. Allow them to pick 1, many or all to build their report dynamically. I'm all set on the TSQL side, but on the Reporting Services side I have to allow each parameter to be null with a default of NULL. In by doing this, the report will auto run, which I do not want to happen. The only resolution I've found thus far was by adding a parameter that does nothing, with a NULL default value. Yet It sticks out like a sore thumb on the report and I want to get rid of it. If I check in "Hidden" in the parameter options, my report errors out stating that the parameter requires a value.
2) Is it possible to have a parameter that has available values from a dataset have a NULL checkbox like those of parameters that do not have available values?
3) Is it possible to add back/forward buttons inside of a report instead of just at the report header by default?
Thanks!
The table in the database has a field called LabID. That field is aninteger and consists of the year plus a counter. For example, thefirst record of 2006 would be "20060001," the second record of 2006would be "20060002" and so on. I'm trying to create an Insert triggerthat can generate the ID value when a new record is inserted, but I'mnot quite sure how to implement that trigger. Can anyone help?
View 11 Replies View RelatedHi all,
My requirement is to get the autoincrement column once a new row is inserted, we need the autoincrement value to update other tables, at present I am using an insert trigger in which I am extracting the autoincrement column from the 'inserted' table, but how far this work perfectly when multiple users insert simultaneously. Can any of you suggest me the best way to extract the actual value inserted.
Now the scenario is :
sp which insert a row
Begin tran
insert ...
select @returnKey = (select retkey from #temptab)
drop #temptab
Commit Tran
Trigger on insert
insert idcolumn into #temptab select autokey from inserted
If user A & B inserts row exactly at same time, will this method return the exact auto value what A and B have inserted to them respectively.
Thanks in Anticipation
Raj
I have create a recursive triggers in tblIncident. The PKID is the primary key in tblIncident, in this trigger, i'm trying to generate an auto increament primary key from the stored procedure GetMaxId and update to tblINcident, but I face a problem where PKID does not refresh the latest PKID, it always show the default value 0, until I requery the table. How to get the latest PKID?
/* Trigger in tblIncident */
CREATE TRIGGER GetPKID
ON tblIncident FOR
INSERT AS
DECLARE @PKID int
DECLARE @NEWVALUE int
DECLARE PKID_Cursor CURSOR FOR SELECT tblIncident.PKID FROM tblIncident, inserted
where tblIncident.PKID = Inserted.PKID
OPEN PKID_Cursor
BEGIN
FETCH NEXT FROM PKID_Cursor INTO @PKID
WHILE (@@fetch_status = 0)
BEGIN
SET @NEWVALUE = 0
/*Call stored procedure - getmaxid to get the latest PKID */
EXECUTE GetMaxId "IN", @NEWVALUE OUTPUT
Update tblIncident SET PKID = @NEWVALUE WHERE PKID = @PKID
FETCH NEXT FROM PKID_Cursor INTO @PKID
END
END
CLOSE PKID_Cursor
DEALLOCATE PKID_Cursor
Hi,
I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!
Thank you!
My trigger:
CREATE TRIGGER tr_SendPassword ON PIPEmail
FOR INSERT
AS
DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @message varchar(100)
IF (select count(*) from inserted) = 1
BEGIN
IF exists (SELECT * FROM inserted
WHERE Subject = 'Forgot my password')
BEGIN
select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join inserted on UserRole.WindowsUser = inserted.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser
exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@Password
END
END
We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular value get inserted in a tag.
For ex:
<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">
In the above xml data if we have the value 21545 in Stab the trigger needs to be executed and mail needs to be sent to a distribution list.
The Trigger should not be initiated if value other than 21545 is updated...
We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular kind of data get inserted in a tag.
For ex:
<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">
In the above xml data if we have the value 21545 in Stab tag the trigger needs to be executed and mail needs to be sent to a distribution list.
I created a table with a field called myID. I set the data type as "uniqueidentifier", but it won't auto generate. How do I create a field to auto generate a unique number? I'm working with SQL Server 2000 and I'm creating the table from Enterprise Manager.
View 5 Replies View RelatedI need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.
User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD
13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD
more the records of each user, unique should be the ID as displayed above.. Optimized way to generate the same...
Hi there,
I have made a stored procedure to create a script with a create statement for a table. it looks like this:
Create Procedure GenerateScript ( @tableName varchar(100)) as If exists (Select * from Information_Schema.COLUMNS where Table_Name = @tableNameBegin declare @sql varchar(8000) declare @table varchar(100) declare @cols table (datatype varchar(50)) insert into @cols values('bit') insert into @cols values('binary') insert into @cols values('bigint') insert into @cols values('int') insert into @cols values('float') insert into @cols values('datetime') insert into @cols values('text') insert into @cols values('image') insert into @cols values('uniqueidentifier') insert into @cols values('smalldatetime') insert into @cols values('tinyint') insert into @cols values('smallint') insert into @cols values('sql_variant')
set @sql='' Select @sql=@sql +case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type + case when Data_Type in (Select datatype from @cols) then '' else '(' end +case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+ ','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end +case when Data_Type in (Select datatype from @cols)then '' else ')' end +case when Is_Nullable='No' then ' Null,' else ' Not null,' end from Information_Schema.COLUMNS where Table_Name = @tableNameselect @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where Table_Name = @tableNameselect @sql=@table + substring(@sql,1,len(@sql)-1) +' )' select @sql as DDL End Else Select 'The table ' + @tableName+ ' does not exist'GO
Now my problem. I want to install this SP in the master db and i want call other databases. With "SP_RENAME" that's possible when i say "USE otherdatabase". With my own stored procedure it's not possible. What do i have to alter?
Thanks
Hello everybody
I am doing update on Database using Erwin
Is any diffrence in this code and what is is right way to create table in my case ?
-- --- this code generated by Erwin 3.5.2
CREATE TABLE obooGrpAllotRmBooking (
BookingDate ASmallDateTime NOT NULL,
ID_RoomType ID NOT NULL,
ID_GrpAllot ID NOT NULL,
ContractQty hAvailQty DEFAULT 0
)
go
ALTER TABLE obooGrpAllotRmBooking
ADD PRIMARY KEY (ID_GrpAllot, ID_RoomType, BookingDate)
go
------------- This code generated by SQl server 7 generate script ---
CREATE TABLE [dbo].[obooGrpAllotRmBooking] (
[ID_GrpAllot] [ID] NOT NULL ,
[ID_RoomType] [ID] NOT NULL ,
[BookingDate] [ASmallDateTime] NOT NULL ,
[ContractQty] [hAvailQty] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ID_GrpAllot],
[ID_RoomType],
[BookingDate],
[ContractQty]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD
CONSTRAINT [DF__obooGrpAl__Contr__78DED853_05] DEFAULT (0) FOR [ContractQty]
GO
Thanks
Hi all
I just got SQL server 2k5 installed and working....im just trying to figure out how to make an auto incremement column wiht an integer..
or has it been replaced with the "unique identifyer"
Abyss
how can i create an auto number field in sql server 2000?
thanks kris
Daily, I have to map thoursands of items into its correct category. What I have to do is Filter item by the ClSID and SCLISD, then look into the Label for where the item should be. For example from the attachment,
CLSID = 1 and SCLSID = 23 - if in the label has the keyword "notebook", its NEW_CAT should be 14 and its NEW_CATNAME is LAPTOPS;
CLSID = 1 and SCLSID = 23 - if in the Label contains Tablet PC, its NEW_CAT name should be 2474 and its NEW_CATNAME is PC LAPTOPS.
CLSID = 3 and SCLSID = 54 - if in the Label contains Watch, its NEW_CAT name should be A6052 and its New_CATNAME is SMART WATCH.
Basically, the work flow is looking into the CLSID, then SCLSID,then, Label and then assign a correct NEW_CAT and NEW_CATNAME of the item.
I have written a code but I can't get it look into specific CLSID and SCLSID.
For example, i have when upper(Label) like upper ('%Tablet%PC%') then '2474 PC Laptops'
I couldn't attach either the file or image.
Hi does anyone know how to create a sql table and then import a list by just clicking on a button to call a procedure?CREATE TABLE clients(ClientID VARCHAR(5), ClientName VARCHAR(30), PRIMARY KEY (ClientID));LOAD DATA LOCAL INFILE 'C:/client.csv' INTO TABLE clientsLINES TERMINATED BY '
';
Hi everyone,
I know that statistics called _WA_... are created on tables when auto create statistics is set on a database. Is this an indication that queries against the table would perform better if indexes were created on the columns in question? (The tables I'm interested in optimising are used equally for transactional querying and reporting)
Thanks for any replies!
Les
For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.
We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.
The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
/************************************************************************************************************
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y"
AS
DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)
SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''
DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10), @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)
DECLARE CurHistoryTable CURSOR FOR
-- query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription,
CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.XScale) AS FieldScale,
CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID
LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0
WHERE SO.xtype = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0
BEGIN
-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', ' + @FieldName
END
IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF
END
SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
BEGIN
SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END
IF @AllowNulls = 'Y'
BEGIN
SET @SQLTable = @SQLTable + ' NULL'
END
ELSE
BEGIN
SET @SQLTable = @SQLTable + ' NOT NULL'
END
SET @SQLTable = @SQLTable + ',' + @CRLF
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END
CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable
-- finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF
SET @SQLTable = @SQLTable + ' )'
PRINT @SQLTable
-- execute sql script to create history table
EXEC(@SQLTable)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
--PRINT @SQLTrigger
-- execute sql script to create update/delete trigger
EXEC(@SQLTrigger)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
END