script to install print queues

rundll32 printui.dll,PrintUIEntry /u /in /n \\ntsrva\LAW228_HP




http://blogs.technet.com/b/askperf/archive/2011/09/16/print-queue-scripting.aspx

automated Word index

The vb script below will create an index of every word from a document,  (directions for setting up macros)




Sub ConcordanceBuilder()
Application.ScreenUpdating = False
Dim StrIn As String, StrOut As String, StrTmp As String, StrExcl As String
Dim i As Long, j As Long, k As Long, l As Long, Rng As Range
'Define the exlusions list
StrExcl = "a,am,an,and,are,as,at,b,be,but,by,c,can,cm,d,did," & _
          "do,does,e,eg,en,eq,etc,f,for,g,get,go,got,h,has,have," & _
          "he,her,him,how,i,ie,if,in,into,is,it,its,j,k,l,m,me," & _
          "mi,mm,my,n,na,nb,no,not,o,of,off,ok,on,one,or,our,out," & _
          "p,q,r,re,s,she,so,t,the,their,them,they,this,t,to,u,v," & _
          "via,vs,w,was,we,were,who,will,with,would,x,y,yd,you,your,z"
With ActiveDocument
  'Get the document's text
  StrIn = .Content.Text
  'Strip out unwanted characters. Amongst others, hyphens and formatted single quotes are retained at this stage
  For i = 1 To 255
    Select Case i
      Case 1 To 35, 37 to 38, 40 To 43, 45, 47, 58 To 64, 91 To 96, 123 To 127, 129 To 144, 147 To 149, 152 To 162, 164, 166 To 171, 174 To 191, 247
      StrIn = Replace(StrIn, Chr(i), " ")
    End Select
  Next
  'Delete any periods or commas at the end of a word. Formatted numbers are thus retained.
  StrIn = Replace(Replace(Replace(Replace(StrIn, Chr(44) & Chr(32), " "), Chr(44) & vbCr, " "), Chr(46) & Chr(32), " "), Chr(46) & vbCr, " ")
  'Convert smart single quotes to plain single quotes & delete any at the start/end of a word
  StrIn = Replace(Replace(Replace(Replace(StrIn, Chr(145), "'"), Chr(146), "'"), "' ", " "), " '", " ")
  'Convert to lowercase
  StrIn = " " & LCase(Trim(StrIn)) & " "
  'Process the exclusions list
  For i = 0 To UBound(Split(StrExcl, ","))
    While InStr(StrIn, " " & Split(StrExcl, ",")(i) & " ") > 0
      StrIn = Replace(StrIn, " " & Split(StrExcl, ",")(i) & " ", " ")
    Wend
  Next
  'Clean up any duplicate spaces
  While InStr(StrIn, "  ") > 0
    StrIn = Replace(StrIn, "  ", " ")
  Wend
  StrIn = " " & Trim(StrIn) & " "
  j = UBound(Split(StrIn, " "))
  l = j
  For i = 1 To j
    'Find how many occurences of each word there are in the document
    StrTmp = Split(StrIn, " ")(1)
    While InStr(StrIn, " " & StrTmp & " ") > 0
      StrIn = Replace(StrIn, " " & StrTmp & " ", " ")
    Wend
    'Calculate the number of words replaced
    k = l - UBound(Split(StrIn, " "))
    'Update the output string
    StrOut = StrOut & StrTmp & vbTab & k & vbCr
    l = UBound(Split(StrIn, " "))
    If l = 1 Then Exit For
    DoEvents
  Next
  StrIn = StrOut
  StrOut = ""
  For i = 0 To UBound(Split(StrIn, vbCr)) - 1
    StrTmp = ""
    With .Range
      With .Find
        .ClearFormatting
        .Text = Split(Split(StrIn, vbCr)(i), vbTab)(0)
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .MatchWholeWord = True
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute
      End With
      Do While .Find.Found
        StrTmp = StrTmp & " " & .Information(wdActiveEndPageNumber)
        .Collapse (wdCollapseEnd)
        .Find.Execute
      Loop
    End With
    StrTmp = Replace(Trim(StrTmp), " ", ",")
    StrOut = StrOut & Split(StrIn, vbCr)(i) & vbTab & StrTmp & vbCr
  Next
  'Create the concordance table on a new last page
  Set Rng = .Range.Characters.Last
  With Rng
    .InsertAfter vbCr & Chr(12) & StrOut
    .Start = .Start + 2
    .ConvertToTable Separator:=vbTab, Numcolumns:=3
    .Tables(1).Sort Excludeheader:=False, FieldNumber:=1, _
      SortFieldType:=wdSortFieldAlphanumeric, _
      SortOrder:=wdSortOrderAscending, CaseSensitive:=False
  End With
End With
Application.ScreenUpdating = True
End Sub





From   microsoft.com Answers

Exporting Outlook contacts

You can export contacts to a file that can then be imported into other applications, such as Web mail clients, Excel spreadsheets, or database applications.
The most common export file format is a comma separated value (CSV) file. If you are exporting contacts for use in another copy of Outlook, we recommend that you choose an Outlook Data File (.pst) in step 6 below.

1. Click the File tab.
   
2. Click Options.
   
3. Click Advanced.
   

1. Under Export, click Export.
   

Validating user input from batch file

To prevent blank input:

:askQuestion
set /p UserName=   What is your CUA user ID? || Set UserName=NothingChosen

If "%UserName%"=="NothingChosen" goto :sub_error

:sub_error
echo Error:   Please type in CUA user ID
echo(
echo(

goto:askQuestion

To use regular expression:

:loop
    SET /P "UserName=What is your CUA user ID?: "
    echo("%UserName:"= %"|findstr /r /C:"""[0-9][0-9][a-zA-Z]*""" >nul || ( echo Error:   CUA user ID starts with 2 digits & ECHO( & goto loop )

Installing windows print queue on laptop

To install printers on student laptops (not on domain) the START command works best:


echo Starting printer install....
start \\printers.cua.edu\LAW150_HP4000


This will just like manual install, and will generate Windows Security popup, student must put in user name preceded by domain, and then password:

DOMAIN\userName


This will save name/password in Credentials Manager.



To install via Visual basic, computer must be on the domain:



Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.AddWindowsPrinterConnection "\\printers.cua.edu\LAW400_HP9000"

Open URL with CMD file

What to open web page, and submit form via CMD script:



set /p UserName=   What is your CUA user ID? 
echo(
start ""  http://test.law.cua.edu/form/printer-install.asp?name=%UserName%^&explanation=Library_Prnt_v3b^&OS=Windows^&Submit=Submit"


The URL has variable, enclosed by %  %,  and &'s which must be escaped via ^

http://test.law.cua.edu/form/printer-install.asp.asp?name=%UserName%&explanation=Library_Prnt_v3b&OS=Windows&Submit=Submit"

nslookup command

From Microsoft.com

Commands:   (identifiers are shown in uppercase, [] means optional)

 NAME            - print info about the host/domain NAME using default 
                   server
 NAME1 NAME2     - as above, but use NAME2 as server
 help or ?       - print info on common commands
 set OPTION      - set an option

    all                 - print options, current server and host
    [no]debug           - print debugging information
    [no]d2              - print exhaustive debugging information
    [no]defname         - append domain name to each query
    [no]recurse         - ask for recursive answer to query
    [no]search          - use domain search list
    [no]vc              - always use a virtual circuit
    domain=NAME         - set default domain name to NAME
    srchlist=N1[/N2/.../N6] - set domain to N1 and search list to N1, N2, 
                          and so on
    root=NAME           - set root server to NAME
    retry=X             - set number of retries to X
    timeout=X           - set initial time-out interval to X seconds
    type=X              - set query type (for example, A, ANY, CNAME, MX, 
                          NS, PTR, SOA, SRV)
    querytype=X         - same as type
    class=X             - set query class (for example, IN (Internet), ANY)
    [no]msxfr           - use MS fast zone transfer
    ixfrver=X           - current version to use in IXFR transfer request

 server NAME     - set default server to NAME, using current default server
 lserver NAME    - set default server to NAME, using initial server
 finger [USER]   - finger the optional NAME at the current default host
 root            - set current default server to the root
 ls [opt] DOMAIN [> FILE] - list addresses in DOMAIN (optional: output to 
                  FILE)

    -a          -  list canonical names and aliases
    -d          -  list all records
    -t TYPE     -  list records of the given type (for example, A, CNAME, 
                   MX, NS, PTR, and so on)

 view FILE       - sort an 'ls' output file and view it with pg
 exit            - exit the program

SQL for patron fines

Looking to run SQL query to get patron fine info (from Sierra Listerv)

As David Noe reported, the descriptions you seek are based on fine.charge_code and are described in Sierra DNA.

This is one of those occasions when code descriptions are not stored in sibling tables that can be easily joined to. In this case, you must use a CASE statement to manually describe all possible descriptions for the codes – and hope that Innovative doesn’t add extra codes in the future. To test for this possibility, I use the ELSE clause to write out ‘unexpected code’ followed by the new code.

select

p.id,

p.home_library_code,

p.record_num,

p.mblock_code,

n.last_name,

n.first_name,

p.owed_amt,

f.assessed_gmt,

  CASE

    WHEN f.charge_code = '1' THEN 'Manual Charge'

    WHEN f.charge_code = '2' THEN 'Overdue'

    WHEN f.charge_code = '3' THEN 'Replacement'

    WHEN f.charge_code = '4' THEN 'Adjustment (OVERDUEX)'

    WHEN f.charge_code = '5' THEN 'Lost'

    WHEN f.charge_code = '6' THEN 'Overdue Renewed'

    WHEN f.charge_code = '7' THEN 'Rental'

    WHEN f.charge_code = '8' THEN 'Rental Adjustment (RENTALX)'

    WHEN f.charge_code = '9' THEN 'Debit'

    WHEN f.charge_code = 'a' THEN 'Notice'

    WHEN f.charge_code = 'b' THEN 'Credit Card'

    WHEN f.charge_code = 'p' THEN 'Program Registration'

    ELSE 'unexpected code '||f.charge_code

  END                                           AS "Charge Type",

f.description,

f.item_charge_amt

from sierra_view.fine as f

join sierra_view.patron_view as p

on p.id = f.patron_record_id

join sierra_view.patron_record_fullname as n

on n.patron_record_id = p.id

;

As an example of a code which has a properly configured sibling table that describes the codes, we can use your fourth column, p.mblock_code. To see descriptions instead of codes in that column you could add a join to sierra_view.mblock_property_myuser and then display the ‘name’ column from that table. No ugly CASE statement required. Unfortunately, we have no alternative for charge_code

select

p.id,

p.home_library_code,

p.record_num,

m.name,

n.last_name,

n.first_name,

p.owed_amt,

f.assessed_gmt,

  CASE

    WHEN f.charge_code = '1' THEN 'Manual Charge'

    WHEN f.charge_code = '2' THEN 'Overdue'

    WHEN f.charge_code = '3' THEN 'Replacement'

    WHEN f.charge_code = '4' THEN 'Adjustment (OVERDUEX)'

    WHEN f.charge_code = '5' THEN 'Lost'

    WHEN f.charge_code = '6' THEN 'Overdue Renewed'

    WHEN f.charge_code = '7' THEN 'Rental'

    WHEN f.charge_code = '8' THEN 'Rental Adjustment (RENTALX)'

    WHEN f.charge_code = '9' THEN 'Debit'

    WHEN f.charge_code = 'a' THEN 'Notice'

    WHEN f.charge_code = 'b' THEN 'Credit Card'

    WHEN f.charge_code = 'p' THEN 'Program Registration'

    ELSE 'unexpected code '||f.charge_code

  END                                           AS "Charge Type",

f.description,

f.item_charge_amt

from sierra_view.fine as f

join sierra_view.patron_view as p

on p.id = f.patron_record_id

join sierra_view.patron_record_fullname as n

on n.patron_record_id = p.id

JOIN sierra_view.mblock_property_myuser AS m

ON m.code = p.mblock_code

;

--

Brent Searle

Library Systems Manager

Langara College

Windows 8 shortcuts

Want to create desktop shortcut to Windows 8 search function.

How to create/edit Start screen short cuts


Link to shutdown shortcuts

DNS tools for ezproxy

After moving OPAC server to new IP address, needed to check DNS for wildcard entries  (eg *.columbo.law.cua.edu)

This tools is perfect:
http://www.usefulutilities.com/cgi-bin/checkdns

Type in domain name, it does all the checking.  Very good for troubleshooting ez proxy.

Documentation for DNS settings from OCLC

Sierra Direct SQL access error

From Sierra Listserv:

If you create a Sierra user ID with "Sierra SQL Access" under assigned applications, it must be in lower case text.  Otherwise you get the error message below: 

Dear Collective Wisdom,

Getting further, but since can’t connect to our sierra-db for PostgreSQL access.  Judging from my latest error message, a file called pg_hba.conf seems to require some kind of configuration:

In the pgAdmin Edit menu is “Open_pg_hba.conf”,  which brings up an editor – so I assume I can modify the file. 

I’ve roamed through my pg_Admin directory on my workstation, but don’t have (or can’t locate) any default config file, so not sure what to do at this point.

Can someone using pgAdmin III check to see what their default pg_hba.conf file looks like and share it here?

Task manager permissions

Trying to set up task in Windows server with local user, and I get this error message:

This task requires that the user account specified has Log on as batch job rights

I needed to give the user rights to login to run batch jobs:

1. Run    secpol.msc /s
2. Select "Local Policies" in MSC snap in
3. Select "User Rights Assignment"
4. Right click on "Log on as batch job" and select Properties
5. Click "Add User or Group", and include the relevant user.

ISBN field from OPAC marc records

Trying to get ISBN numbers from Innovative OPAC via SQL (from Sierra Listserv)

Hi Liza:

Ray Voelker had some good suggestions for you. I have a very small database compared to many of you so I rarely run into the timeout issues you are experiencing. Ray's suggestions for use of LIMIT and OFFSET along with ORDER may be what you need to do.

I have a few other suggestions that may also help.

1) Get rid of unnecessary JOINs

In your original query, you join bib_view to subfield_view unnecessarily, I think.  subfield_view contains the column "record_type_code" with which you can limit your output to bib records without the need for the join.

On my small database, your original query takes approximately 14 seconds to run whereas the following alternative takes approximately 5.5 seconds. That's quite a saving.>

-- ============================================================================
-- Option 1 - no joins to other views
-- ============================================================================
SELECT
  isbn.content                     AS "ISBN"
FROM
  sierra_view.subfield_view        AS isbn
WHERE
  isbn.record_type_code = 'b'
  AND
  isbn.marc_tag IN ('020','024')
  AND
  isbn.tag = 'a'
; 

Note that I have also used the IN ('n1','n2','n3',etc) format in place of the ORs in your original query. I don't think it saves anything on processing time but it sure saves typing time.


2) Removing extraneous data from ISBNs

You have already limited your output to only subfield 'a' of 020 or 024 so you shouldn't be seeing any subfield delimiters. But, you may have extraneous information within subfield 'a' following the number. Assuming that there is always a space between the number and extraneous data (which may be an incorrect assumption), you could extract the portion of the string that appears before the space like this:

-- ============================================================================
-- Option 2 - with CASE statement to remove data that follows ISBN number
--   Assumes that there is a space following the number
-- ============================================================================
SELECT
  CASE
    WHEN POSITION(' ' IN isbn.content) != 0
    THEN SUBSTR(isbn.content,1,POSITION(' ' IN isbn.content)-1)
    ELSE
    isbn.content
  END                                              AS "ISBN (Edited)"
FROM
  sierra_view.subfield_view                        AS isbn
WHERE
  isbn.record_type_code = 'b'
  AND
  isbn.marc_tag IN ('020','024')
  AND
  isbn.tag = 'a'

On my small database, I didn't see a noticeable increase in processing time with the addition of the CASE statement but you might see one with a large database. You'll have to weigh the pros and cons.


3) Limiting to first ISBN per record

Each bib record can have multiple ISBNs. Depending on your project, you may not need to export all of them. If your project could get by with only one ISBN per record, you can trim off some more time by limiting to the first ISBN per record. The following query run over my small database takes a bit less than 4 seconds:

-- ============================================================================
-- Option 3 - Exclude all but the first ISBN found for each record
-- ============================================================================
SELECT
  CASE
    WHEN POSITION(' ' IN isbn.content) != 0
    THEN SUBSTR(isbn.content,1,POSITION(' ' IN isbn.content)-1)
    ELSE
    isbn.content
  END                                              AS "ISBN (Edited)"
FROM
  sierra_view.subfield_view                        AS isbn
WHERE
  isbn.record_type_code = 'b'
  AND
  isbn.marc_tag IN ('020','024')
  AND
  isbn.tag = 'a'
  AND
  isbn.occ_num = 0
;
 

--
Brent Searle
Library Systems Manager

On 2015-05-01 12:00 PM, Liza wrote:

Hi all,

I am trying to pull a list of ISBNs to give to vendors. The SQL query times out with this error:

----------------------------------------------------

ERROR: canceling statement due to statement timeout

SQL state: 57014

----------------------------------------------------

I have tried adding a LIMIT and it will run if the limit is under 100000. If I run the query with a limit, how do I run it again where the first query left off? 

I would also like to get just the ISBNs without the delimiters. I can put the results into Excel and get rid of the delimiters there, but is there a way to get just the numbers in the SQL results? 

Here's the query:

SELECT subfield_view.content as ISBN

FROM sierra_view.bib_view, sierra_view.subfield_view

  WHERE (subfield_view.record_id = bib_view.id) AND ((subfield_view.marc_tag = '020') OR (subfield_view.marc_tag = '024')) AND (subfield_view.tag = 'a');

Query with the limit:

SELECT subfield_view.content as ISBN

FROM sierra_view.bib_view, sierra_view.subfield_view

WHERE (subfield_view.record_id = bib_view.id) AND ((subfield_view.marc_tag = '020') OR (subfield_view.marc_tag = '024')) AND (subfield_view.tag = 'a')

LIMIT 100000;

Thanks for your help,

Liza






 



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Liza Miller Arend 

Systems Administrator 

Minuteman Library Network 

Dynamic new books list Innovative OPAC

From Sierra Listserv, here is SQL to get new books with in a specific date range, and LC call number
Page is http://library2.udayton.edu/newbooks -- Source code 

SELECT  *

FROM
  s_v.bib_record b JOIN S_V.record_metadata r  ON (b.record_id = r.id)
  LEFT OUTER JOIN S_V.bib_record_call_number_prefix n ON (b.record_id=n.bib_record_id)
  LEFT OUTER JOIN S_V.bib_record_property p  ON (b.record_id = p.bib_record_id)
  --    now link the bib, to the record(s)
  LEFT OUTER JOIN S_V.bib_record_item_record_link l  ON (b.record_id = l.bib_record_id)
  LEFT OUTER JOIN S_V.item_record_property I ON (l.item_record_id = i.item_record_id)

WHERE    -- limit to one call number prefix (eg, law)
  n.call_number_prefix like \'' . $call_prefix . '%\' AND
  b.cataloging_date_gmt >= date(\'' . $date_from . '\') AND
  b.cataloging_date_gmt <  date(\'' . $date_to   . '\') AND b.is_suppressed = FALSE

ORDER BY
            i.call_number_norm