Get Sharepoint List Access from SQL Server

Well, here I go – how to natively (or naively at this point – I just started this) manipulate Sharepoint Lists from SQL Server.

I did it! It is not using a linked server (I will get to the bottom of that though) but using an OPENROWSET works. This means that the user needs to have insert and select privileges – but there is a way around that too – by making a role/user internal to the dB and running the stored proc run (EXECUTE AS) that role/user. To get that to work takes some tinkering – another article will come soon regarding that.

Basically – the onerous method would show that the two statements below work Continue reading

Adding jQuery to Report Services – (Make parameter field bigger is the reason for doing this in this article)

There are really 2 concepts here which is the reason for the long title. First, adding jQuery to Report Services means hacking 2 install files (.aspx files) of Report Services. If you don’t have someone who has access to the server that is hosting the web pages, it might be hard to get this done. Your IT department might say ‘no way’ but this problem is well known for RS 2008 up to the current – so it is really not a risk – if it breaks, simply fix it – most likely it will never need fixing especially if you are using an older version of RS which is by this point mature. Next to address the width – this article helped. Here is how I did it and who helped me (urls in the code and the links above)… Continue reading

Configure Access to SourceTree to Gitorious Source Control

Overview:

  • SourceTree is a visual client to a git-style source control repository for Windows (in this example) and Mac-OS-X.
  • Gitorious is a wiki/git-style web based file browser and repo setup system like github – except YOU run it.  It is delivered as standalone computer (Virtual Machine) that hosts a git server and wiki all in one. The wiki is integrated into the project. My good buddy at Geek Inc. delivered this all on a 4GB stick which had a VMWare AND a VirtualBox version of it and then he and pointed me in the right direction. Thanks Ben.
  • IMPORTANT – EVERYONE ELSE CAN VIEW PROJECTS PUBLICLY WITHOUT WRITE ACCESS (awesome!) – meaning they don’t have to know git – just send them links to the relevant directories or files AS A WEB LINK – they don’t have control over your files since the files are stored on the Virtual Machine’s internal directory structure. They can make their own accounts – but the admin determines who is on what team for write to the repository. THAT WAS THE GOAL
  • Ideally there Continue reading

Installing IIS and ASP.NET – installing things so you can get your website up and running

I have gotten a lot of issues of tinkering with IIS solved before – but never have I written anything down and I have never installed it from scratch. Now, I have to do these steps again so this time I will write them down for myself and others. I will not be using the default website.  I am doing so on Windows 7. Note that this is on the Intranet – so security is NOT my main focus although it is on my mind while setting it up but I am not going to be crazy on that in this discussion – just mention a few thing here or there that you might want to think about. This is where I started from a tutorial standpoint – but I am going back to this once I am done. There is a great MS tutorial in the 1st link in the install section below.

UPDATE: This little step of registering IIS (aspnet_regiis.exe -I) after install has gotten me SOOO many times. ALWAYS do this – see this link.

Install MS Visual Studio Express 2012 for Web

  • download from Microsoft and follow the instructions
  • http://www.asp.net/web-forms – great tutorial site too
  • register it, MS says its free but you have to register – ok – no issues
  • you will require a reboot

Install IIS

Windows Task Scheduler – a consistent way to execute exe files

A guaranteed way to make exe’s with parameters kick off is

aa) don’t run a scheduled task on a network share – if you get 0x1 as a result code .. copy everything to a local drive??? (latest update). Worked for me but is 2X work when source code changes.

aa1) UPDATE: If running bat files (tips from here)

  • run as highest privileges is checked
  • put the path of the bat file in the “start in” and a full path and bat file in the “Program/script” box
  • ensure there are no quotes in the Program/script and Start in boxes (not tested but I didn’t need them)
  • ensure all directories have “Ful Permissions” for the account you finally use to with’
  • test it with “r-mouse click” run. If it works there – it will worked with it logged on.

a) make a new Task Scheduler and make it it point to a bat file. You can try all sorts of other scenarios but  I have already spent hours – this works every time .

 

b) Now, lets take a look at that RunScriptNow.vbs.qualitysurfaces.bat file – I will copy the guts of it here:

[shell]
echo %date% %time% %username% qualitysurfaces >> c:\temp\test.txt
cd c:\Users\Administrator\Desktop\Moreaware
cscript //nologo //B RunScriptNow.vbs qualitysurfaces >>c:\temp\test.txt
echo %date% %time% >> c:\test.txt

[/shell]

It writes to a log file of your choice, then it mimicks the “Start in” by cd’ing to the directory where the executable file is

In this case, I wanted to run a .vbs file with parameters so I use cscript but this is not the focus on this step b). We put any output to the dos prompt from this .vbs process to the log file.

Lastly – to know that we made it to the end of the cscript line – we write again to the log.

NOW WE KNOW FOR SURE THINGS WERE DONE! If gui items pop up from the command line – don’t expect to see them like with window 2003 – this is now all silent. Note I have a diff bat file for every parameter – substituting %1 in that bat file above for ‘quality surfaces’ in the script line DOES NOT WORK – neither does %%1.

c) For directly kicking off an exe – hmmm. I think I converted them all to .vbs but you simply put the exe file on the command line NOT fully qualified with a path since the cd line directly above it. Again – pipe the output (append or >> ) to the log file so you know what is going on.

d) here is the guts of the .vbs file which should speed things up for you. This is in leiu of me having a .exe example in c) 😉

[vbnet]
Dim myDate, myTimeS
Dim nowvar
Dim incust
Dim ArgsListExtra

‘ get the arguments from the command line – we only want the first one and put it incust
if WScript.Arguments.Count >= 1 Then
incust = WScript.Arguments.Item(0)

‘get the rest of the arguments – not sure why I left this in but it works with this in so lets leave it
ArgsListExtra = ""
if WScript.Arguments.Count >= 1 Then
For i = 1 to WScript.Arguments.Count
ArgsListExtra = ArgsListExtra & " " & WScript.Arguments.Item(i-1)

Next
End If

‘lets make the filename with a nice date and timestamp in the file name
nowvar =  Now()
myDate = FormatDateTime(nowvar, 2)
myTimeS = FormatDateTime(nowvar, 3)

myTimeS = replace(myTimeS, ":", "_", 1, -1, 1)
myTimeS = replace(myTimeS," ","",1,-1,1)

myDate = replace("0"&myDate,"/","_0",1,-1,1)
myDate = replace(myDate, "_0201", "201", 1, -1, 1)
myDate = replace(myDate, "_00", "0", 1, -1, 1)
myDate = replace(myDate, "_0", "0", 1, -1, 1)
myDate = replace(myDate,"_00","0",1,-1,1)
myDate = replace(myDate," ","",1,-1,1)

Dim finalstring
finalstring = myDate & "_" & myTimeS

‘ now assemble the command line we would normally type

Dim cmdstart
cmdstart = "cd ""C:\Users\Administrator\Desktop"" && "    ‘ look up what && does on the command line – it allows multi command line items on one line of text. Crazy

Dim command
command  = cmdstart & "JobMaterialCost.exe -c "&incust&" -u username -p password -fe c:\Users\Administrator\Desktop\"&incust&"_"&finalstring’& ArgsListExtra &" && pause"

‘ now execute it

if 0 Then
WScript.Echo(command) ‘ this is here in case you want to debug – this will NOT show up in the TaskScheduled version – but it might hang there until you press OK – except you cannot
‘  press ok …. cause you cannot see the windows although it will still wait for you  🙂
Else
Set oShell = CreateObject("WScript.Shell")
‘Launch a command shell …. "/K" &lt;- keeps it open   – we want —&gt; /C */ – read up a GREAT text at <a href="http://technet.microsoft.com/en-us/library/ee692837">http://technet.microsoft.com/en-us/library/ee692837</a>
oShell.Run "CMD /C " &amp;command, , True
End If

‘WScript.Echo("Done" &amp; incust)  ‘ again this might hang your Scheduled Tasks

Else

End If
[/vbnet]

Appendix:
If you are interested in what makes the command prompt wait or not for the next process etc. within vbs files – see these links and read them twice – it is REALLY IMPORTANT if you use .vbs to help you script things
– http://technet.microsoft.com/en-us/library/ee692837
– http://technet.microsoft.com/en-us/library/ee692836
– http://visualbasic.about.com/gi/dynamic/offsite.htm?site=http://msdn.microsoft.com/library/en-us/script56/html/wsoriObjects.asp
– http://visualbasic.about.com/gi/dynamic/offsite.htm?site=http://msdn.microsoft.com/library/en-us/script56/html/wsoriObjects.asp
– http://technet.microsoft.com/en-us/library/ee176904.aspx
– http://labmice.techtarget.com/scripting/WSH.htm