[T-SQL] Add login, user, roles in mult db's dynamically

---

• Add login, user, roles in mult db's dynamically
• To find perfect expense?
• ERD from existing database
• How to-Combine 3 Storedprocedures O/P to get one output report without UNION clause
• substring issue
• Last business day of the month; Partial holiday calendar and tally table

Add login, user, roles in mult db's dynamically Posted: 05 Mar 2013 11:43 PM PST I am attempting to create T-SQL that I can re-use on different db's, users, domains, roles, etc…The trouble I'm having is when trying to change database names dynamically. I need the ability to hit multiple db's in one shot. My goal for starters is to only change the username parameter for each user/executio and execute the rest.I have attached the code. Any suggestions would be helpful - there must be a better way.This is what's failing after adding the +(@DB) variable:set @USER = + QUOTENAME(@DB) + 'CREATE USER ' + QUOTENAME(@DN + @UN) + ' FOR LOGIN ' + quotename(@DN + @UN) + ' WITH DEFAULT_SCHEMA=[' + @DN + @UN + ']' --db1

To find perfect expense? Posted: 05 Mar 2013 01:18 PM PST Hai friends, create table user( user_id varchar(100), name varchar(100), designation_id varchar(100), grade_id varchar(100))insert into user values('0012','abc',13,8)insert into user values('0010','bc',5,3)insert into user values('0011','bjc',2,3)insert into user values('0013','bct',6,3)insert into user values('0016','bci',59,35)insert into user values('0019','bcp',9,11) create table designation(desigantaion_id varchar(100),name varchar(100),grade_id varchar(100))insert into designation values('13','progrmmer',8)insert into designation values('5','GM','3')insert into designation values('2','regional manager','3')insert into designation values('6',' accounts manager','3')insert into designation values('59','worker','35')insert into designation values('9','trainee','11')create table sal(effective_date date, sala varchar(100), designation_id varchar(100)grade varchar(100))insert into sal values('2010-01-01','5000','13','b2')insert into sal values('2010-01-01','10000','5','a1')insert into sal values('2010-01-01','10000','2','a1')insert into sal values('2010-01-01','10000','6','a1')insert into sal values('2010-01-01','2000','59','e')insert into sal values('2010-01-01','3000','9','c')insert into sal values('2011-01-01','5500','13','b2')insert into sal values('2011-01-01','11000','5','a1')insert into sal values('2011-01-01','11000','2','a1')insert into sal values('2011-01-01','11000','6','a1')insert into sal values('2012-03-01','2500','59','e')insert into sal values('2012-02-01','3600','9','c')insert into sal values('2012-01-01','15000','6','a1')now i wanna make a join all the table,if i ' m pass the effective_date depends on the salry ll display all the employes.... such designations are missed on the '2012-01-01' in that... if i m pass the all effective _dates the outputs of sal shows '2010' and '2012','2011' also that each employess sal display three times i waana display the occurate sal of all employees depends on effective_dates.

ERD from existing database Posted: 05 Mar 2013 09:40 AM PST Hello,I need little help. I have an existing database and i need to have an ERD of this DB. Is there any tool which i can use (free tool) to draw ERD from this existing database?Please suggest,Thanks

How to-Combine 3 Storedprocedures O/P to get one output report without UNION clause Posted: 05 Mar 2013 10:25 AM PST Hi Friends,I have very complex functionality-I have developed 3 Stored Procedures- 1) Jobposting,2) WorkOrder and 3) Workernow the final step I want to do is-Combine the 3 Stored Procedures and get one single output the problem with UNION clause is that each of the 3 STored procedures have 2 sections of code - the 1st section has fields in different order than the 2nd second section for each stored procedure, and there are 300 fields so I wanted to know without the manual effort maintaing the same fields order in both sections of the code for each pass, instead is there anyother way this can be done using TEMP table in the start if yes , would be much obliged if I could get a sample code to get my above functionality accomplished please.ThanksDhananjay

substring issue Posted: 05 Mar 2013 06:55 AM PST Not sure what I am missing here possible brain freeze declare @loginname varchar(35)set @loginname = '<rxno>001c</rxno>\test1'select SUBSTRING(@loginname,CHARINDEX('<rxno>',@loginname)+1,CHARINDEX('</rxno>',@loginname)-CHARINDEX('rxno>',@loginname)-1) I want to return only 001c but I am getting rxno>001

Last business day of the month; Partial holiday calendar and tally table Posted: 05 Mar 2013 12:30 AM PST Hi Folks,I am trying to perform some if/else logic on the last business day of the month. I have a third- party provided partial holiday calendar, just the holidays.When I hard code the date, SET @StartDate = '2013-03-28', I am not getting my expected result Any suggestions? --Create the tally table if you don't have one-Courtesy Jeff Moden--===== Create and populate the Tally table on the fly/* SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC*/CREATE TABLE #Holidays (HolidayDate datetime, IsHoliday char(1))INSERT INTO #Holidays (HolidayDate,IsHoliday) VALUES('2013-02-18 00:00:00.000', 'Y')INSERT INTO #Holidays (HolidayDate,IsHoliday) VALUES('2013-03-29 00:00:00.000', 'Y');DECLARE @StartDate DATEDECLARE @LastBusinessDayMonth DATESET @StartDate = GETDATE()--@LastBusinessDayMonth seems to be set properly hereSET @StartDate = '2013-03-28'--Doesn't work hereSET @LastBusinessDayMonth = ( SELECT TOP 1 MAX((DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n)) FROM TALLY T LEFT JOIN #Holidays H ON H.HolidayDate = (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n) WHERE DATEPART ( mm , @StartDate ) = DATEPART ( mm , (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n) ) AND --Days in the current month DATEPART ( yy , @StartDate ) = DATEPART ( yy , (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n) ) AND --Days in the current year HolidayDate IS NULL AND --Exclude holidays DATEPART(WEEKDAY, (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n)) NOT IN (7,1)--Exclude weekends)PRINT @StartDatePRINT @LastBusinessDayMonthIF @LastBusinessDayMonth = @StartDateBEGINPRINT 'LastBusinessDay'ENDELSEBEGIN PRINT 'NotLastBusinessDay'ENDDROP TABLE #Holidays

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610