How Can L Rewrite This To Run Efficiently !!!!!!!!!!

Apr 18, 2002

How can l rewrite this and trim the code.

CREATE Procedure Disbursements_Cats
(@startdate datetime,
@enddate datetime)

AS
Begin

SELECT Loan.loan_No AS Loan_No,
Loan.customer_No AS Customer_No,
Customer.first_name AS First_name,
Customer.second_name AS Second_name,
Customer.surname AS Surname,
Customer.initials AS Initials,
Bank.Bank_name AS Bank_name,
Branch.Branch_name AS Branch_name,
Branch.branch_code AS Branch_code ,
Bank_detail.bank_acc_type AS Bank_acc_type,
Transaction_Record.transaction_Amount AS Transaction_Amount,
Transaction_Record.transaction_Date AS Transaction_Date,
Loan.product AS Product,
Product.product_Type AS Product_Type,
Product_Type.loan_Type AS Loan_Type

FROM Transaction_Record INNER JOIN
Loan ON Transaction_Record.loan_No = Loan.loan_No INNER JOIN
Product ON Loan.product = Product.product INNER JOIN
Customer ON Loan.customer_No = Customer.customer_no INNER JOIN
Bank_detail ON Customer.customer_no = Bank_detail.customer_no INNER JOIN
Branch ON Bank_detail.Branch = Branch.Branch INNER JOIN
Bank ON Branch.Bank = Bank.Bank INNER JOIN
Product_Type ON Product.product_Type = Product_Type.product_Type

END;
GO

View 1 Replies


ADVERTISEMENT

Rewrite A Query Efficiently

Mar 15, 2007

Is there a efficient way to write this query?

SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range,
COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries]
FROM Country
GROUP BY
CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END

View 1 Replies View Related

Using Wildcards Efficiently With Equals Or LIKE

Jul 6, 2006

Is it possible to use wildcards with an equals statement? Such asSELECT * FROM Table WHERE City = '%' AND State='Ca'Bascially just stating where city equals anything...I know you can do it with a LIKE statement such as...SELECT * FROM Table WHERE City LIKE '%' AND State='Ca'but is that very efficient?The reason I want to do this is because I want to programmitcally set the city, so just ommiting it won't work
Also, using City LIKE '%' seems to not include NULL...is there anywayto include NULL as well as anything else?
Thanks for your help!

View 2 Replies View Related

Ordering Records - Efficiently

Feb 8, 2008

I hope I explain this correctly...

I'm required to allow users to order items in a field to be displayed on a page in the order they specified.

For example: A user can drag and drop items in a list to specify the order it will be displayed in.

I have my drag and drop code ready to do this.
I have an idea on how to do this but I think it’s too inefficient.
I was going to create an orderby field and populate it with a number that corresponds to the position of the item. However, as one can deduce, if a user drags and drops a record between two others, I would have to change not only its orderby number but then change all the other items orderby number.

For instances if I dropped an item with an orderby number of 3 between 6 and 7 I would have to change the 3 to a 7 and then recursively change all the other records orderby numbers up to 3 and then change everything after 7.

Well, I hope I make sense. It’s easier to visualize it on paper.

Does anyone know how to tackle this issue of user dynamic ordering?

View 2 Replies View Related

Efficiently Joining Same Table Twice

Jul 23, 2005

My main table has the following structure:t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]I want to join this table with the following second table:t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),(NULL,1,"value2")]The join should first try to find a match on id_primary and only if thatfails it should find a match on id_secundary. Every row in t1 is matchedagainst a single row in t2.The following query works:selecta.name, isnull(b.value, c.value)fromt1 a left outer join t2 b on a.id_primary = b.id_primaryleft outer join t2 c on a.id_secundary = c.id_secundaryI'm wondering though if it would be possible to write a query that only usest2 once, since it actualy is quite a complex query that is calculated twicenow. Any ideas (besides using a temp table)?

View 3 Replies View Related

Efficiently Supplying Xml Data For Treeview

May 16, 2008

I have an interesting problem:

I have an ASP.NET web application that uses a Treeview control to display what can potentially be a very large data set. In the past, I would just run a recursive stored procedure in my database that would output the XML which I would save to a file. The Treeview used the XML file as its data source. I did this because it can take so long for the stored procedure to run (10 seconds or more) that, it isn't practical to have the treeview point directly to the stored procedure. This worked well enough because the data didn't change very often.

Now, it looks as if the application will be used in a production environment, and I really need to find a way to supply up-to-date data to the treeview in a dynamic way. I have tried creating a view that would provide XML and that would be updated any time the target table is updated but, that has not worked. I have also tried creating a trigger that would output to an XML file any time an edit was made (using the xp_cmdshell functionality) but, that has proven difficult as well.

Is there a simpler solution that I am just missing? I just want an up-to-date XML representation of the data that is a result of a recursive function.

Thanks for any help you can provide.

View 7 Replies View Related

Way To Efficiently Make Test Data ?

Dec 26, 2006

We have a website that accesses our SQL databases. In the past, we used our internal employees to improve our SQL databases. However, we want to outsource the work.

There is a lot of information we would like to keep private from the outsourcing.


Is there a way to efficiently make test data throughout our database without changing our original database?
Is a way to easily update our database to the new changes?

I found this product through Google EMS Data Generator for SQL Server
http://www.sqlmanager.net/en/products/mssql/datagenerator
Would this program help us make test data?

Thanks in advance
-Devin

View 2 Replies View Related

Query Rewrite

Mar 28, 2008

Hi all,

Here's the code I'd like to update, and below it a set of sample data:

Declare @StartDate DateTime
Declare @EndDate DateTime
Set @StartDate = '20-mar-2008'
Set @EndDate = '25-mar-2008'

SELECT

COUNT (iqs.childid) as Cnt
,CASE

--Search Categories and Create Initial Groupings--
WHEN Category LIKE '%Jewel%' THEN 'Accessories'
WHEN Category LIKE '%Beauty%' THEN 'Accessories'
WHEN Category LIKE '%Accs%' THEN 'Accessories'
WHEN Category LIKE '%Gift%' THEN 'Accessories'
WHEN Category LIKE '%Grooming%' THEN 'Accessories'
WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand'
WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand'
WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand'
WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand'
WHEN Category LIKE '%Female%' THEN 'Womenswear'
WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand'
WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand'
WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand'
WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand'
WHEN Category LIKE '%Male%' THEN 'MensOwnBrand'
END AS CategoryGroup
,CONVERT(VARCHAR(10), iqs.StatusDate, 103) AS StatusDate

FROM InventoryQueryStatus iqs
JOIN InventoryStatus [is]
ON [is].StatusID = iqs.StatusID
JOIN Inventory i
ON i.InventoryID = iqs.InventoryID
JOIN InventoryCategory ic
ON ic.CategoryID = i.CategoryID
WHERE iqs.StatusID = 31000
and Category NOT LIKE 'Force%'
--AND iqs.StatusDate >=GETDATE()-1
AND iqs.StatusDate BETWEEN @StartDate AND @EndDate
GROUP BY
CASE

--Search Categories and Create Initial Groupings--
WHEN Category LIKE '%Jewel%' THEN 'Accessories'
WHEN Category LIKE '%Beauty%' THEN 'Accessories'
WHEN Category LIKE '%Accs%' THEN 'Accessories'
WHEN Category LIKE '%Gift%' THEN 'Accessories'
WHEN Category LIKE '%Grooming%' THEN 'Accessories'
WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand'
WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand'
WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand'
WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand'
WHEN Category LIKE '%Female%' THEN 'Womenswear'
WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand'
WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand'
WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand'
WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand'
WHEN Category LIKE '%Male%' THEN 'MensOwnBrand'
END
,CONVERT(VARCHAR(10), iqs.StatusDate, 103)

Order By StatusDate


Sample Data:

164WomensOutsideBrand 20/03/2008
5MensOutsideBrand 20/03/2008
78WomensOwnBrand 20/03/2008
92WomensPremiumOutsideBrand 20/03/2008
1Accessories 20/03/2008

However, I'd like to enable a total for the day (340)

Thanks,

JB

View 1 Replies View Related

Rewrite A WHERE Clause

Aug 29, 2005

I have a WHERE clause that could be an "=" or a "LIKE" depending uponif the passed variable is populated or not. I would like to know thebest way to write the WHERE clause to make it dynamically switchbetween the 2 and make best use of the indexes.CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))CREATE INDEX IDX_myTable_County ON myTable(COUNTY)DECLARE @COUNTY VARCHAR(50)SET @COUNTY = 'SANTA CLARA' -- Could also be SET @COUNTY = NULLSELECT ID FROM myTableWHERE COUNTY LIKE (CASE WHEN @COUNTY IS NOT NULL THEN @COUNTY ELSE '%'END)This does not seem like best practice to me because I am forced to use"LIKE" even when @COUNTY is populated with data. Ultimately I'd like:WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE'%' END)but that is incorrect syntax on "=".Also, I do not want to use a dynamically built statement. Is there away around this?Thanks,Josh

View 14 Replies View Related

How To Rewrite The View

Apr 7, 2008

Currently I just need to rechange a view which is writen by other staff member because of low performance.

This view comprises 4 query statement and link using Union statement.

Each query statement will refer more than 8 tables and other views.

Now I want to rewrite it but I can not know how to do. I change union to CTE, no performance improved.

I want to create indexed view, but because this view refer the other view, I cannot create index on it?

Any tips for me? Thank you for help.

View 6 Replies View Related

Efficiently Searching Multiple Words In A String

Feb 15, 2008

 Hi,I'd be interested in people's thoughts about the following.  A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for.  Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple LIKE '%' + @searchTerm + '%' however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue.  The benefit of this would be that you could do a:LIKE @term + '%'but then I have the cost of the join. (And the added complexity which is not a major issue)Any thoughts/tips?Thanks!   

View 1 Replies View Related

SQL 2012 :: How To Efficiently Downsize Some Unicode Fields

Oct 12, 2015

We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all Unicode and I have calculated a savings of 5G in space converting only 2 such fields averaging 206 characters each to non-Unicode, and almost 10G in space if we convert a few more of them from nchar and nvarchar to char and varchar types. These fields will never have a requirement to hold Unicode characters that cannot be in the SQL_Latin1_General_CP1_CI_AS collation as they come in as plain ASCII originally and will always do so per the protocol standard.

I’m the software architect and chief C# developer though only a DBA hack or I would not have designed our database to have Unicode fields for high volume tables that did not need Unicode for those fields when the database was created 3 years ago. I want to correct this mistake now before we finalize converting to an AlwaysOn environment to support with various performance and backup issues.

After downsizing these two or more fields, we would like to shrink the database one time to take advantage of the space savings for full backups, and for seeding an AlwaysOn environment.

1.What is the safest and most efficient conversion technique for downsizing columns from nchar/nvarchar to char/varchar types? Esp. when there are multiple fields in the same table to be converted. I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server (4 virtual core, 8G memory) before running out of disk space even though there was 8G left on the disk, and the db has unlimited size set (Could not allocate space for object 'dbo.abc'.'PK_xyz' in database 'xxx' because the 'PRIMARY' filegroup is full). I did delete an old database before it finished after getting a disk warning so maybe it did not count that new space. Regardless it was too slow. And this was on just the two largest of these columns (12.6M rows) and only ran 2 to 3% CPU busy so seemed not very efficient, and indicated unacceptable downtime if we were to convert even these two fields much less any additional fields. Average field size for these two fields was only 206 characters or 412 bytes each. Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table. I have a FK and indexes to contend with on the table.

2.If I figure out how to do #1 efficiently within an acceptable maint window, what is the safest practice for doing a one-time shrink and end up with organized/rebuilt indexes and updated Statistics? I understand the logic of not doing regular shrinks and that sometimes it can actually increase the size.

3.Is there any third party tool that could take a backup and restore it into a new database with the modified field definitions or otherwise convert certain field types?

View 9 Replies View Related

Need Opinions On Creating A Reporting Database More Efficiently

May 27, 2006

Situation:
SQL Server 2000.
At my new employer they have a production database on one server and a copy of it that is set to read only on another server which is used for reporting.

#1
They have an SQL Server Agent job on the production server that: (2 times a day)

Backs up the production database
Copies the backup file to a directory on the reporting server. (Its pretty big and can take time if there are problems with the LAN)

#2
They have an SQL Server Agent job on the Reporting server that: (scheduled to run 2 hours or so after the job on server 1 has runthey figured that it would be a safe bet that the backup and copy process of the first job would be done by then)

Breaks the user connections to the reporting database
Performs a restore on the reporting database using the backup file that was copied to the holding directory by the production job.
Sets some permissions for various users.
Sets the reporting database to READ ONLY.
What I would like to do is find a more efficient way to create this reporting database, I have started doing research into DTS methods but would like some opinions from more experienced users.

Thank You,
Wade

View 1 Replies View Related

Rewrite Index Scripts

May 29, 2007

Hello all

We are load testing SQL 2005 and I need to re-write the index scripts that we have from 2000. Is there an easier way to rewrite the scripts without having to go to each job, then each step and modify it?

Our current index script template is:

Create NonClustered Index [IdxABCDE]
On dbo.blahblah(blahID)
With FillFactor = 90, Statistics_NoRecompute
On [Index2]
Go

and I need to rewrite it as:

ALTER INDEX [IdxABCDE] ON [dbo].[blahblah]
REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)


I am thinking I would have to rewrite the scripts from scratch. We have hundreds of index scripts. So before I brace myself to lot of typing, just wanted to find out if there is any easier way..

Thanks..



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

View 4 Replies View Related

Rewrite Subquery Because Of Performance

May 1, 2008

I have a complex stored procedure that utilises inner joins, and in the WHERE clause there is defined a subquery. This subquery has now cause a performance hit to the ponit where the server is returning a timeout. Need to find an alternate fast solution.....


SELECT BE.BlogEntryID

FROM vw_BlogEntry BE

INNER JOIN @BlogView BC ON BC.CommonID = BE.BlogCommonID

INNER JOIN vw_Blog B ON B.BlogID = BC.BlogID

WHERE
(

...
)
AND
(

....
)

AND

(

-- GET ENTRIES WHERE COMMENT COUNT IS AT LEAST @CommentCount (..or @CommentCount = 0)

@CommentCount <= 0

OR BE.CommonID IN (SELECT bc.EntryCommonID
FROM vw_BlogComment_Current bc

INNER JOIN tblVersionDetails vd ON bc.VersionID = vd.VersionID

WHERE

IsNull(@CommentStatus,'') = ''

OR vd.Status IN (SELECT * FROM fn_CsvToString(@CommentStatus))

GROUP BY bc.EntryCommonID

HAVING COUNT(bc.EntryCommonID) >= @CommentCount)

)

View 10 Replies View Related

Rewrite This? Nested Subqueries...

Jan 17, 2008

This works, but it's highly unefficient and generates a lot of IO. Is there another way to do it without the use of temp tables?




Code Block
select eh.*
from Equipment_History eh
where Equipment_History_ID in
(select top 2 Equipment_History_ID
from Equipment_History eh1
where Equipment_ID in
(select Equipment_ID
from Equipment_History eh2
where Equipment_History_ID in
(select min(Equipment_History_ID)
from Equipment_History eh3
where eh2.Equipment_ID = eh3.Equipment_ID
and eh2.Equipment_Status_Type in (1,2,3)))
and eh1.Equipment_ID = eh.Equipment_ID
order by Equipment_History_ID)


Equipment_History_ID is PK

Let me know what other information would be useful, I can barely understand my logic from looking at the code but it does in fact work.

View 4 Replies View Related

Efficiently Creating Random Numbers In Very Large Table

Jan 19, 2007

Hello,

I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).

I'd like to take the top 5%, for instance, based upon a column containing random numbers.

Can anyone suggest a highly efficient method of populating a column with random numbers.

Thanks in advance.

Rod

View 10 Replies View Related

Better Way To Rewrite Stored Proc And Not Use Cursor At All

May 26, 2015

I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:

Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no

[code]...

The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words

I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:

SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'

View 8 Replies View Related

Query With A Lot Of Left Joins - Rewrite?

Sep 21, 2007

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?


Thanks.

View 5 Replies View Related

Rewrite Query, Alternatives To CASE WHEN

Oct 30, 2007

Hello.

I have a query which spends a lot of time calculating my CASE WHEN -statements.

My query looks like this

SELECT TOP 250

UserId,
CASE WHEN

(someCol*0.4+someOtherCol*0.3) > 99 THEN 99
ELSE

(someCol*0.4+someOtherCol*0.3)
END
FROM

(

SELECT

UserId,

CASE WHEN @myparam > 50 THEN

CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END
ELSE

CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol,
CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol
FROM

SomeTable
) t1


This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.

I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.

Any suggestions?

View 3 Replies View Related

A Rewrite Of The Sp_help_revlogin Procedure (use At Own Risk)

Mar 21, 2006

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @logintype char(1)

DECLARE @logindisabled int

DECLARE @binpwd varbinary (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@logintype = 'G' OR @logintype = 'U')

BEGIN -- NT authenticated account/group

IF @logindisabled = 1

BEGIN -- NT login is denied access

SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- NT login has access

SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

PRINT @tmpstr

END

END

ELSE IF (@logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @SID_varbinary

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

SET @tmpstr = 'SET @pwd = CONVERT (nvarchar(128), ' + @txtpwd + ')'

PRINT @tmpstr

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = @pwd, @sid = ' + @SID_string + ', @encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = NULL, @sid = ' + @SID_string

END

PRINT @tmpstr

END

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

View 4 Replies View Related

Rewrite Active X Script From DTS In SSIS 2005

Aug 14, 2007

I have a activeX script like below in DTS and I am trying to rewrite it in SSIS.
What is the best way to do this?
Can I do this using a look up table? or other transformers in SSIS
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then
DTSDestination("RACE_NATIVE_AM_IND") = "Y"
else
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "2" then
DTSDestination("RACE_NATIVE_AM_IND") = "N"
end if
end if

if DTSSource("RACE_ASIAN_YN") = "1" then
DTSDestination("RACE_ASIAN_IND") = "Y"
else
if DTSSource("RACE_ASIAN_YN") = "2" then
DTSDestination("RACE_ASIAN_IND") = "N"
end if
end if

if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "1" then
DTSDestination("RACE_BLACK_IND") = "Y"
else
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "2" then
DTSDestination("RACE_BLACK_IND") = "N"
end if
end if

if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then
DTSDestination("RACE_HAWAIIAN_IND") = "Y"
else
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "2" then
DTSDestination("RACE_HAWAIIAN_IND") = "N"
end if
end if

if DTSSource("RACE_CAUCASIAN_YN") = "1" then
DTSDestination("RACE_WHITE_IND") = "Y"
else
if DTSSource("RACE_CAUCASIAN_YN") = "2" then
DTSDestination("RACE_WHITE_IND") = "N"
end if
end if


if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 9 then
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 40
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource ("RACE_ASIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 16
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource ("RACE_AFRICAN_AMERICAN_YN") = "1" then
DTSDestination ("RACE_CD") = 32
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 51
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource("RACE_CAUCASIAN_YN") = "1" then
DTSDestination("RACE_CD") = 31
DTSDestination("RACE_MULT_IND") = "N"
DTSDestination("RACE_OTH_IND") = "N"
end if
end if
end if
end if
end if
else
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 10 then
DTSDestination("RACE_CD") = 99
DTSDestination("RACE_MULT_IND") = "N"
DTSDestination("RACE_OTH_IND") = "N"
else
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) < 9 then
DTSDestination("RACE_CD") = 52
DTSDestination("RACE_MULT_IND") = "Y"
DTSDestination("RACE_OTH_IND") = "N"
end if
end if
end if

Main = DTSTransformStat_OK
End Function

View 3 Replies View Related

Modifying A Report Model Without Rewrite ALL The Ad-hocs Reports Developed By Users...it Is Possible?

Mar 13, 2007

Community:

Suposse that some models are deployed in Report Server for a while, and users have developed some ad-hoc reports on them using Report Builder, (some of the models are SSAS Cubes).

Modifications are required for a Model, what is the procedure to deploy this modifications? What happens with ad-hoc reports of this Model? Rewrite all the reports is a VERY BAD option, I agree that some reports must be rewrote, but only if they reference objects no longer valids in new model.

I suposse that the procedure for SSAS Cube Models will be different for a Relational Database Source because metods of generating models are so different. (I am particularly curious about Cubes, I can't figure out how I can do it)

Suggestions and links will be very appreciate.

Regards

Julio Díaz C.



View 6 Replies View Related







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