Tuesday, January 3, 2023

Remove invalid user id from Sharepoint Fundation 2010 with powershell

 Whenever user from AD removed, The user id remained at sharepoint without automatically remove.

Here is the steps to remove invalid user using powershell:

1. get a list of user from sharepoint and save to a file

cmdlet: get-spuser -web http://portal select userlogin | sort userlogin | foreach{ $_.userlogin.remove(0,5) out-file spuser.txt

2 Open spuser.txt manually scan through and remove non user id such as itsupport, ifca, boss, etc

3. copy/move spuser.txt to AD server.

4. Open AD Powershell from AD server.

5. create a script file and with code below:

$content = get-content "$(get-location)\spuser.txt" # load the list of user id from spuser.txt file.


out-file remove-list.txt   #generate a user list that need to be removed at sharepoint server

foreach ($line in $content)
{
    try {
        
get-aduser $line -ErrorAction stop | selectsamaccountname -expandproperty samaccountname 
    }  # try
catch 
{
                "absb\" + $line | out-file -append remove-list.txt

           }  # catch
 } # foreach


6. Once remove-list generated, copy to sharepoint server 

7. create a script file with code below:

set-variable -name portal -value "http://portal" -option constant

$content = get-content "$(get-location)\remove-list.txt"

out-file deleted.txt # to record deleted id

foreach ($line in $content) {

    remove-spuser $identity $line -web $portal $confirm:$false

    try{
            get-spuser $line -web $portal -ErrorAction stop
        } # try
catch {
            $line + " -deleted." | out-file -append deleted.txt

           } # catch

} # foreach

    



        






Friday, December 30, 2022

Powershell 2.0 ... All that i know

General Command

- To display powershell version
$PSVersionTable

Server 2008 AD
- Allow remote access to powershell
cmdlet: enable-remoting -force

- To access AD powershell from member computer of an AD use(can be remote or locally):
cmdlet: import-module ActiveDirectory

- To list all AD users
cmdlet: get-aduser -server <computername> -filter * 

-To sort by specific property. Sort must be define after -filter command otherwise not working.
cmdlet: get-aduser -filter * | sort -property <properties> 
e.g: get-aduser -filter * | sort -property name / samaccountname / DisplayName

-To display without header
cmdlet: get-aduser -filter * | select samaccountname -ExpandProperty samaccountname

-To display without header and with custom string “absb\"
cmdlet: get-aduser -filter * | -expandproperty samaccountname | foreach{ "absb\" + $_.samaccountname }

-To select only specific property
cmdlet: get-user -filter * | select samaccountname / DislayName / Name

-To call a specific user 
cmdlet: get-aduser -identity <userid> 
shortcut: get-aduser <userid>





Saturday, June 14, 2014

install php for apache win7 64x

Be sure that you have installed Visual C++ Redistributable for Visual Studio 2012 : VC11 vcredist_x64/86.exe

install/unzip apache.

make sure apache is not using the same port as IIS, port 80.

install apache as windows service with "httpd -k install" at command prompt(wuth administrator right).

edit httpd.conf.

pre php configuration
-------------------------
1. set ServerRoot "d:/www/Apache" where apache folder located.

2. change the port number if desire port is not 80, Listen 80

3. change document root if you have alternative location for web document
DocumentRoot "D:/www/htdocs"

D:/www/htdocs">

4. configure ServerName with parameter (make sure port 80 not conflict with IIS if IIS is installed).

ServerName localhost


php configuration
--------------------
1. add  at the last line of load module 
LoadModule php5_module "d:/www/php/php5apache2_4.dll"


2. add php extension to mime module

     AddType application/x-httpd-php .php


3. add PHPIniDir "d:/www/php" at the last of the line to tell where php located.


4. change default page as index.php at:
DirectoryIndex index.php index.html


5. restart apache

Tuesday, October 29, 2013

MSSQL select specific range of row


SQL2012
SELECT field
FROM table
ORDER BY field ASC
OFFSET 26 ROWS
FETCH NEXT 15 ROWS ONLY


SQL 2005  and above
SELECT field
FROM (SELECT ROW_NUMBER() OVER (ORDER BY table.field ASC) AS field_alias,
field FROM table) table_alias
WHERE table_alias.field_alias >= 10 and table_alias.field_alias <=30;


SQL 2000

SELECT TOP 25 *
FROM (
  SELECT TOP 75 *
  FROM   table
  ORDER BY field ASC
) table_alias
ORDER BY table_alias.field DESC;

Monday, September 9, 2013

Connect to MSSQL SERVER(Express) by using TCP/IP remotely

to allow MSSQL Express edition to connect by using TCP/IP remotely,
the TCP/IP protocol must be first enabled.

1. go to SQL Server Configuration Manager,
2. go to SQL Server Network Configuration
3. go to Protocols for SQLEXPRESS
4. Right click TCP/IP and select Enable.

















5. After enabled, double click TCP/IP
6. go to IP Addresses tab,
7. Scroll until the end and look for IP All
8. Add 1433 to TCP Port
























9. Go back to SQL Server Service,
10 right click SQL Server(SQLExpress) and Restart.

Monday, August 13, 2012

Method 'Post' of object 'IOWSPostData' Failed in Sharepoint 2010

original source:http://makingspwork.blogspot.com/2010/04/method-post-of-object-iowspostdata.html

I'm running Sharepoint 2010 and Office 2007. I confirmed this error message does not occur if you're running Sharepoint 2010 and Office 2010, only when the environments are mixed.


All of the posts I'd read about this problem suggested editing the Excel Add-in file EXPTOOWS.XLA located in the \Program Files\Microsoft Office\Office 12\1033 folder. But editing that file didn't help me a bit. Turned out there's another version of that same file in \Program Files\Microsoft Office\Office14\1033, and that was the one that needed editing in my case.
*note: I personally modify both files from 2 folder location.
1. \office12\1033
2. \office14\1033

Before you begin, a few notes for the uninitiated:

  • EXPTOOWS.XLA is a hidden file. In order to find it, you'll have to show all hidden files on the system. Do this through the Control Panel, Appearance and Personalization, Folder Options, Show Hidden Files and Folders.
  • The 1033 folder is read-only, so you 1) must be an administrator to write to the folder and 2) can't just edit the file and save it back to the same directory.
To modify the file:1. Once you've found the file, rename it to OldEXPTOOWS.XLA (this will give you a backup in case something happens). You may be prompted to confirm the change because this is a read-only folder.

2. Double-click on OldEXPTOOWS.XLA. This will open Excel but it will look like nothing else is happening. On your keyboard, press ALT-F11 to open the built-in Visual Basic editor. It will look something like this:

*Note: If you are using 64-bit Windows, the XLA file is actually under: C:\Program Files (x86)\Microsoft Office\Office12\1033\. If you are running Windows Vista, you will need to run Excel as administrator in order to save the modified file back.

3. Open the code window and scroll until you find this section:

Sub Initialize(List, Title, URL, QuickLaunch)
strQuickLaunch = QuickLaunch
aTarget(iPublishURL) = URL
aTarget(iPublishListName) = List
aTarget(iPublishListDesc) = Title
lVer = -1 ' can't tell STS server version
If Val(Application.Version) >= 12 Then
lVer = Application.SharePointVersion(URL)
End If
End Sub


4. Comment out this line lVer = Application.SharePointVersion(URL) by adding a single apostrophe ( ' )to the beginning of the line.

5. Add a line immediately beneath the commented line that reads lVer = 2 (that's an L, as in lollipop). The section will now look like this (I have highlighted the changes):

Sub Initialize(List, Title, URL, QuickLaunch)
strQuickLaunch = QuickLaunch
aTarget(iPublishURL) = URL
aTarget(iPublishListName) = List
aTarget(iPublishListDesc) = Title
lVer = -1 ' can't tell STS server version
If Val(Application.Version) >= 12 Then
' lVer = Application.SharePointVersion(URL)
lVer = 2
End If
End Sub

image


6. Click File, Save. You will get an error message that the file is Read Only. Click OK to continue. A Save As dialog will appear. Change the file type to Excel Add-in (.xla). When you do this, the file location will change to the Excel addin directory - you will want to save the file to somewhere you can remember (like the Desktop). Make sure you've named it EXPTOOWS.XLA.

7. Close the VB editor and exit Excel.

8. Using Windows Explorer, navigate to \Program Files\Microsoft Office\Office14\1033.

9. Copy the new file (EXPTOOWS.XLA) from your Desktop (or wherever you saved it) into \Office14\1033. You will be prompted to confirm this action.

When the new file is in the \Office14\1033 folder, you should be able to import the spreadsheet into Sharepoint 2010 (although you may have to restart IE or the Sharepoint session).
Some of the blog posts say that you can change the version in the line from lVer = 2 to lVer = 3 and try that too. In my tests, it didn't matter which version you used as long as it was in the version in the \Office14\1033 directory. A modified EXPTOOWS.XLA in the \Office12\1033 folder didn't seem to cause this any ill-effects either.

Sunday, August 5, 2012

Create a customize auto numbering from SharePoint.

Original post from Autonumber Custom List Field
here are the flow:
1. Create a list name "Running Serial" with 2 columns named LastNo and NextNo.
2. Add the list column which hold the auto running number. CIN or RefNo or CustomerNo.
1. Create a workflow variable named "mNumber" or any name preferred.
2. Create a Set workflow variable action with set NextNo from "Running Serial" as a data source. Set Title from "Running Serial" as desire column name (CIN, RefNo or CustomerNo).
3. Create another action with Set field in current item. Choose the auto running number field (CIN or RefNo or CustomerNo). Set value to workflow variable, mNumber.
4. Create a last aciton with Update list item. 
5. Update "Running Serial" LastNo field with mNumber variable.
6. Set Find the list item with "Title" field, set value as "CIN" or "RefNo" or "CustomerNo".
7. Save and publish it.