Can I just say I hate SSIS September 16, 2009

Ok.  So, I’m getting data from a website and putting it in a file.  I’m then uploading the file to a database.  I had changed the sizes of the strings in the file to match the size of the strings in the database.  But, then I start getting an error that it’s going to be truncated because I’m putting 150 into 50.  Where!!!???!!!  The database is 150, the file is 150, so what’s left???  Well, the output of the file is left.  Not only do you have to change the input to the file, you have to change the output of the file.  Ouch! 

To do this:

• Go to your source file box in your data flow
• Right-click and choose Advanced Editor
• Choose the tab, Input and Output Properties
• Choose Flat File Source Output
• Change External Columns AND Output Columns!

Ok, updated 5 minutes later.  You can’t really change the External Columns this way, even though it lets you.  Instead, you have to update the External Columns through the Connection Manager.

To do this:

• Go to the sub-tab Package Explorer
• Open the Connection Managers “folder”
• Double-click on the connection manager for your file
• Choose Advanced from the list on the left
• Update the OutputColumnWidth of each column you need to change
• Click OK
• Go back to the Data Flow
• If you have a little yellow alert and have already changed your Output Columns, open the Advanced Editor, verify the sizes are OK and click OK.  Now the little yellow alert should be gone.

Whew!  I hope that’s it for today!

Just one more fun thing, the Control Flow still seems to think there’s a problem in the Data Flow (a little yellow warning), but if you double-click on it it takes you to the Data Flow where there are no warnings.  Oiw!

Limit DB User to SPs August 26, 2009

 /* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Access is strange August 24, 2009

After hours trying to figure out how to get a sum from one table’s rows into another table (a fairly simple statement in SQL Server), I finally found this from peter57r

peter57r
awestrope,

Try this instead: it avoids the aggregate query.

UPDATE  tblOptions inner join tblOptionsPricing
on tbloptions.optionid = tbloptionspricing.optionid
SET BasePrice= nz(BasePrice) + ([LastCalculatedPrice]*[Qty])

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21826239.html

(this was the search I used in google:  updating table based on query operation must use an updateable query aggregate)

This is a completely different idea from the way it works in SQL Server, it adds up as it goes along.  So, just be sure to empty the appropriate cell before running if you need to start from scratch (which I do).  This is much better than using asp to update one row at a time which I was about to give up and do.  I can’t use VB.  This all used to be done in a module in Access called by a stored proc.  But, Access does not let you call that module if you’re coming from asp. 

However, you can’t use the nz function from asp either, so just change it to an iif, like this:

SET wins = IIf(wins Is Null,0,wins)+1;

Thank you Peter!  You are a life saver today!

Global Search and Replace in SQL Server 2005 July 9, 2009

Text Fields

If you have text fields you cannot use the stored procedure below in Non-Text Fields.  Instead, you will need to do the following for each text column. 

Find the table and column name and run the following command:

UPDATE table_name SET column_name = Replace(Cast(column_name AS NVARCHAR(Max)),'original string', 'new string')

For many columns, here’s an excel concatentation.  Column A is the table name and Column B is the column name.

="UPDATE " & A2 & " SET " & B2 & " = Replace(Cast(" & B2 & " AS NVARCHAR(Max)), 'original string', 'new string')"

Non-Text Fields

If you only have a column or two to replace you can use a command similar to the one above:

UPDATE table_name SET column_name = Replace(column_name,'original string', 'new string')

If you want to go through every single column in every table, use the stored procedure below.  Be careful that you really want to replace EVERY single instance of the old string.  This will only work on char, varchar, nchar, and nvarchar columns.  Be sure to remove the stored procedure when you are done with it.

This was found at:  http://vyaskn.tripod.com/sql_server_search_and_replace.htm

Run with this command:

EXEC SearchAndReplace 'original string', 'new string'

 Here is the stored procedure:

CREATE PROC SearchAndReplace

(

      @SearchStr nvarchar(100),

      @ReplaceStr nvarchar(100)

)

AS

BEGIN

      -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

      -- Purpose: To search all columns of all tables for a given search string and replace it with another string

      -- Written by: Narayana Vyas Kondreddi

      -- Site: http://vyaskn.tripod.com

      -- Tested on: SQL Server 7.0 and SQL Server 2000

      -- Date modified: 2nd November 2002 13:50 GMT

      SET NOCOUNT ON

      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int

      SET  @TableName = ''

      SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

      SET @RCTR = 0

      WHILE @TableName IS NOT NULL

      BEGIN

            SET @ColumnName = ''

            SET @TableName =

            (

                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

                  FROM INFORMATION_SCHEMA.TABLES

                  WHERE             TABLE_TYPE = 'BASE TABLE'

                        AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

                        AND   OBJECTPROPERTY(

                                    OBJECT_ID(

                                          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                                           ), 'IsMSShipped'

                                           ) = 0

            )

            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

            BEGIN

                  SET @ColumnName =

                  (

                        SELECT MIN(QUOTENAME(COLUMN_NAME))

                        FROM INFORMATION_SCHEMA.COLUMNS

                        WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)

                              AND   TABLE_NAME  = PARSENAME(@TableName, 1)

                              AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                              AND   QUOTENAME(COLUMN_NAME) > @ColumnName

                  )

                  IF @ColumnName IS NOT NULL

                  BEGIN

                        SET @SQL=   'UPDATE ' + @TableName +

                                    ' SET ' + @ColumnName

                                    + ' =  REPLACE(' + @ColumnName + ', '

                                    + QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +

                                    ') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

                        EXEC (@SQL)

                        SET @RCTR = @RCTR + @@ROWCOUNT

                  END

            END  

      END

      SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'

END

Grant Execute May 19, 2009

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Then, just assign that role to your user.  This should be standard in the setup for sql.  Not sure why they’re making you create it.  In using SQL Server from a website, you don’t want them doing anything but calling stored procs.

Print CSS April 17, 2009

I was having some problems because the site I was adding some pages too had a div with overflow & float.  When trying to print, I only got the first page in ff & ie7.  I ended up finding one solution for firefox on A List Apart (http://www.alistapart.com/articles/goingtoprint/) that had me basically add float: none !important;.  But, that did not fix it for IE.  Then I remembered my old work had the same problem, so I looked up the css and found:  position: static !important; .  That did it for IE.  I believe changing the overflow to visible was also a necessary action.

I also had some issues with divs within divs, so be sure you find them all!

In the end I added this css.  I’m pretty sure this is overkill, but I’ve spent too much time already!

#aa, #bb {
 float:none;
 display:none;
 width:auto;
 overflow:none;
}

#content, #content.list {
 display: block;
 overflow:visible;
 width: auto;
 height:auto
 padding: 0;
 border: 0;
 float: none !important;
 color: black;
 background: transparent;
 max-height:none;
 min-height:inherit;
 max-width:none;
 min-width:inherit;
 clear:both;
position: static !important; 
}

body {
 display: block; 
 background: white;
 font-size: 12pt;
}

.title, h5 {
 display: block; 
 width: auto;
 clear:both;
}

#logo, #banner {
 display: block; 
 float:none;
 padding-bottom:30px;
 overflow:visible;
}

Need an Access equivalent to DTS February 23, 2009

Found this:

 >Firstly, we want to upsize our Access db to a bigger environment and  a large database which is SQL db  and + automate the entire process

OK, I understand THAT.  At a minimum you migrate the backend tables; Then you can use DTS to feed those tables.  

>Secondly,Once we move Querys,Macro,modules(for modules we can use DTS ActiveX script)

Now I’m lost.  If you keep the Access front end, you don’t move the queries, macros, and modules.  Some of them may be replaced by DTS, but the rest remain in the front end.

>how can we trigger Access reports to generate?

You schedule a job (using NT scheduler is easiest way).  The job starts up Access which has an AutoExec Macro.  The Autoexec macro either runs the reports (simple) or runs some code that interprets the command line arguments to figure out what you want to do (more complex)

>how SQL server can link to Access db inorder to generate reports ?

More confusion. SQL Server Reporting Services can reference an Access datasource.   But, according to you, you upsized to SQL Server, so you don’t have an Access DB any more.  Also, SQL Server can’t run Access reports, those have to be run by Access.    

>is there any way in DTS where we can create a task to go to Access db and generate report?  

I don’t know.  But it’s the long way around the block.  Let’s say you upsize to SQL Server.  So, now your tables are in the SQL Server backend.   Your Access Front End can still link to those tables and still run Access reports.  (At least for the most part.  As I explained in the previous thread, you may find that some things don’t work perfectly and need to make some adjustments.  So, do lots of testing.  But the good news–Reports should not be affected too much by upsizing).

If you want to automate your reports.  Build an Access Macro to run the reports. Name it AUTOEXEC or call if from the AUTOEXEC macro.  Create an event in the NT scheduler and start the .mdb file.  That’s all there is to it.

 

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx
http://sqlserverpedia.com/blog/?p=69
http://technet.microsoft.com/en-us/library/aa902657(SQL.80).aspx#sqlbac_topic2

LDAP in ASP February 5, 2009

I had a hard time finding everything I needed for this.  This is a conglomeration of several sources and what worked for me.  I’m not using a MS AD, which most seemed to be.

This only does the query to see if a user exists.  I really need one to authenticate, but that’ll be next.

Dim conLDAP 'As ADODB.Connection
 Dim strSQL 'As String
 Dim strLDAPConn 'As String
 Dim rsUser 'As ADODB.Recordset
 
 strSQL = "Select uid,sn,givenname,cn,telephoneNumber,mail,manager,objectClass,dc,ou From 'LDAP://xxx.xxx.xxx' where uid='username'"
 
 Set conLDAP = Server.CreateObject("ADODB.Connection")
 conLDAP.Provider = "ADSDSOOBject"
 conLDAP.Open
 Set rsUser = conLDAP.Execute(strSQL)
 
 if not rsUser.EOF and not rsUser.bof then
  do while not rsuser.eof
   'Iterate through available user attributes
   For count = 0 to rsUser.Fields.Count - 1
    sAttribName = rsUser.Fields(CInt(count)).Name
    sAttribVal = rsUser.Fields(CInt(count))
   
    If IsArray(sAttribVal) Then
     For i = lbound(sAttribVal) to ubound(sAttribVal)
      sAttribList = sAttribList & sAttribName & ":: " & sAttribVal(i) & "<BR>"
     Next
    Else
     sAttribList = sAttribName & ": " & sAttribVal & "<BR>"
    End If
    response.Write(sAttribList & "<BR>")
    sAttribList = ""
   Next
   rsuser.movenext
  loop
 end if
 rsUser.Close
 set rsUser = Nothing 

ASP & LDAP January 30, 2009

This forum had some good info & links to MS:

http://www.tek-tips.com/viewthread.cfm?qid=969032&page=1

userstring = WSHNetwork.UserName

On error Resume Next

Dim cont’ As IADsContainer
Dim usr’ As IADsUser

szUsername = “domainname\Administrator”
szPassword = “Password”

const ADS_SECURE_AUTHENTICATION=&h0001
const ADS_SERVER_BIND=&h0200
set ons=getobject(“LDAP:”)
set cont=ons.OpenDSObject( _
    ”LDAP://OU=SBSUsers,OU=Users,OU=MyBusiness,DC=companName,DC=local”, _
    szUsername, _
    szPassword, _
    ADS_SECURE_AUTHENTICATION or ADS_SERVER_BIND)

‘ Filter users.
cont.Filter = Array(“user”)

For Each usr In cont
    If lcase(usr.sAMAccountName) = lcase(userstring) Then
      shortName = usr.sAMAccountName
      ldpath = usr.distinguishedName
    End If
Next

SQL Server Express & CF January 28, 2009

1) Installed SQL Server Express — no worries

2) Installed ColdFusion 8 — no worries
http://www.adobe.com/devnet/dreamweaver/articles/setup_coldfusion_02.html

3) Installed Dreamweaver CS3 — no worries

4) Hooking up CF8 to MS SQ Server Express

–See http://www.fusioncube.net/index.php/coldfusion-sql-server-express to open up TCP/IP, setup Security, and setup Login (skip the part about the .jar file): 

a) Registered Servers/Configuration Manager/Protocols for SQLExpress/Enabel TCP/IP Protocol

b) Double-click TCP/IP Protocol/IP Addresses/go to final “All” and set to 1433 (I also set each individual IP to 1433, but not sure if necessary)

c) Go to Properties for the Server Instance / Security / Set to SQL Server and Windows Authentication mode

d) Restart Server (go to Admin Tools / Services)

e) Go to SQL Server / Security /Logins & create login

f) (not in fusioncube, but I had to do it) Go to database / Security / Users / the login / Properties / choose what database role membership this login should have ( I chose reader & writer for now).  I also restarted CF8 (all services) and SQL Server Express a couple of times, so that might be a good thing to do here, but not sure if necessary.

Then login to the CF Admin, Go to Data & Services / Data Sources / Name it and choose SQL Server

Add a new DataSource:

• CF Data Source Name: Anything
• Database: Name of Database
• Server: Empty (since it’s local)
• Port: 1433
• Username:  username created above
• Password: password created above

There was some mention of finding the port instead of setting it to 1433.  http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/5d3621d9-b17d-4f4f-9f7e-fba3124771ed/