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
I need to install the SSIS components on the referenced database server. I believe the SSIS components are cluster aware, so I was planning on just doing add/remove and selecting the components. Rather than breaking the cluster apart to patch the components back to SP1, I was thinking of just running the SP2 install right over the top of the running cluster to bring everything up to SP2.
I'm looking for any suggestions/comments on whether this is the proper way to proceed or if I'm going to have a problem.
I have a 2005 active/active cluster and want to apply SQL server 2005 SP2 to both node. I know that for active/passive , the sp can simple be installed on the active node(instance) and everything will be replicated to the other node. bear in mind what I have SSIS and SSAS and SSRS running on the active/active cluster.
what is the rigth method for applying the SP to activeactive cluster?
I have an exesting SQL 2005 Active/Active 2 node production cluster running on server 2003 x64 enterprise and was wondering is it possible to add an additonal node to this cluster as a passive node/ failover node.
If so could you please point me to documentation on how to do it..
if not, what do i need to do to have a 2 active 1 passive cluster? and do i have to break the existing cluster and rebuild it. if so any documentation related to this as well please
I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers. Per information I've found via this site I did the following: Task: from SS2005, select data elements from Active Directory and populate in ODS (using an SS2005 Package in SSIS)
I created a linked server on the MS2005
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' I then created the following View (in the Master DB): CREATE VIEW viewADContacts AS SELECT [NAME],MAIL FROM OPENQUERY( ADSI, 'SELECT NAME, MAIL FROM ''LDAP://#######/ DC=####,DC=###'' ') The View created w/ no errors
When I execute select * from viewADContacts I get this error: Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.
In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:
Retrieve a list of account numbers through Recordset1 While Not Recordset1.eof Build a sql statement based on fields from current row of Recordset1 Open Recordset2 based on the sql statement built Run an update query based on results from recordset 2 Loop
I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.
Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?
I hope this topic is appropriate for this forum. If not, I pre-apologize.
I have an SSIS package that takes data from active directory and dumps it to a table.
Here is the query I am using:
SELECT displayName, Mail, Title, physicalDeliveryOfficeName, description, telephoneNumber, department From 'LDAP://ourserver' WHERE objectClass='user' AND objectCategory='Person'
I am dumping it into a table on SQL Server where the columns are all of data type: NTEXT.
All columns come across GREAT except the column DESCRIPTION. We keep a piece of information in that column that is important and without it the SSIS is worthless. What is coming across in that column is the following:
System.Object[]
No values are coming across. It should be character data up to 10 in length.
Anyone see this or know of something that I can do to force the "real" data to come through?
Thank you in advance for any consideration you give.
Hi I have migrated a DTS that had some activeX transformation tasks within data pump flow tasks.
Those parts were migrated as "DTS 2000 tasks" .. so activeX transformation tasks aren't possible in SSIS ? I know ActiveX script tasks are but for transformations ?
1. IF i leave these Encapsulated DTS 2000 tasks in the migrated SSIS package, will it run independently of the original DTS or does it need the old DTS running to "call" that part from ? (I hope im making sense here) is it possible to load this functionality internally into the new SSIS ?
2. How could I (if i can't do ActiveX transformation tasks) achieve this is SSIS ? can I achive this using the script tasks in SSIS ?
2. Created a connection in SSIS 3. In My SSIS Package I have created a dataflow select the AD connection 'ADSI'. 4. Tried to run the following query:
SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company, department, title, sn, givenName, name, employeeNumber FROM ''LDAP://OU=Main_Users, DC=DemoAD,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''
And it fails - I can get this working in SQL 2000 DTS by using an Open Query statement!
The Error message I get when trying to test the query is:
Error at Export From AD [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
I have event tried the working OPEN QUERY in SSMS and I get the following:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
'LDAP://OU=DEMO_Users, DC=DEMO,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
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
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)
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
I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime. OpDatesessionStartTimesessionCloseTime 2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166 2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442 2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853 2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour, Count(*) AS Total FROM #empSessionLog Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults: opDateopHourTotal 2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
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
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/
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))
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.
I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:
My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.
Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.
Is it possible to configure two quorum disks, one for each group? What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?
We are going thru the process of scoping an active/active cluster at one site. I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.
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:
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?
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.
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
Currently I am trying to connect to SQL Server 2005 via Excel VBA. I would like to create a connection to the server as I have previously done with my 2000 server. However, the diffence this time is that I am using Active Directory so there are no specific log-in's for SQL per se just Windows Users that are part of groups, any of which could use this spreadsheet. So where-as previously I included the username and password for SQL Server in the connection string I would now like to use the credentials currently logged onto the particular machine.
My previous code was this
Public Function getConnection() As ADODB.Connection
' Create a connection object. Dim Conn As ADODB.Connection Set Conn = New ADODB.Connection
' Provide the connection string. Dim strConn As String
'Use the SQL Server OLE DB Provider. strConn = "Network Library=DBMSSOCN;PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server. strConn = strConn & "DATA SOURCE=SQL02,1433;INITIAL CATALOG=dbDataWareHouse;"
'Use a login. strConn = strConn & " Uid=*******;Pwd=******;"
'Now open the connection. Conn.ConnectionString = strConn
Conn.Open
Set getConnection = Conn
End Function
Would anyone be able to point me in the eight direction ? Your help would be much appreciated
I have an active x script written in vb that just counts the records in a table and fails the step if there are not enough records. It is simple and effective. But when I transfered this task to my SSIS package I get a function not found error.
I am not familiar with active x or vb script so I have no idea what is wrong. I can do without while I build the package but I need it in the end to prevent possible problems....
We are trying to set up an active/active configuration of a SQL Server cluster, and we had a few questions.
Initially, we want to have 2 Database Servers that would share the same Database (both reading/writing to the same tables). However, from reading the MS docs, we find out that we can have what they call an "active/active" configuration using a Cluster but they need to have 2 different disk sets, i.e. having 2 separate databases. If this assumption is correct, how does the data get synchronised between the 2 databases (that are on the 2 different disks sets)?
