http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/
Ran accross this example of using the SQL-Server parser to tokenize a T-SQL script, statement, what-have-you.
At my company, we have some very specific SQL Formatting Standards. And we have not found a code formatter that can do the job. We've just come up with a VS2012 Add-on that bridges the gap between the formatting that SQL Prompt Pro does and what our standard requires. But, there are problems. I got to thinking, if there were a way to parse the T-SQL and then use the resulting Tokens as the basis a T-SQL formatter may not actually be that tough to create.
This is the best article I found. It turns out that you've got a parser available through .NET. Now, if I don't lame out on the idea, I might just try some C# code to format some T-SQL. Who knows. Might be the beginning of a codeplex project. Or a successor to our SQL-Prompt-Pro-plus-homebrew-plugin solution.
September 27, 2013
September 09, 2013
Data-Tier Application Framework (DACFx): What Version is installed
I've identified two ways to identify the version of DACFx installed on a windows machine. I've tested this under Windows 7. If you try it on other platforms, please let me know so I can note success or failure here.
Method One: Uninstall Programs
Method One: Uninstall Programs
- Go to Uninstall Programs on the control panel.
- Search for Data-Tier
- See the version number.
Method Two: Windows Registry Value
- Run regedit
- Find this registry key HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\DACFramework\CurrentVersion
- From there, look at the Version value.
I found this method by watching what the August 2013 DACFx installer did. I watched installer activity with Sysinternals Process Monitor
May 30, 2013
Creating a Windows Installer on USB Drive
I have an ISO file for windows 8. Just realized I don't have DVD. Turns out. I don't need one. Microsoft provides a tool that takes the contents of the iso file and creates and copies then to a USB device that can be used to install Windows. Be warned, it formats the USB device. That means anything on that device is lost.
http://www.microsoftstore.com/store/msus/html/pbPage.Help_Win7_usbdvd_dwnTool/
Don't worry that the tool has Windows 7 in the name. It works for Windows 8 too.
http://www.microsoftstore.com/store/msus/html/pbPage.Help_Win7_usbdvd_dwnTool/
Don't worry that the tool has Windows 7 in the name. It works for Windows 8 too.
October 08, 2012
Team Foundation -- Oops, I shouldn't have checked that in!
Working in Visual Studio 2010 today, I was in the middle of creating a shelveset when I goofed and pressed Check In. Agh! Oh no!
Then, I found the setting in Visual Studio to propmt on check-in.
Menu:
The cool thing is that the change type in history shows 'edit, rollback'. I also updated the initial changeset's comment to include a note about the rollback and the rollback changeset id.
I discovered that Team Foundation (tf) has a rollback command. So I used
> tf rollback ...from the command line.
Then, I found the setting in Visual Studio to propmt on check-in.
Menu:
Tools > OptionsOptions dialog:
Source Control > Visual Studio Team Foundation Server > Prompt before check-in from Pending Changes windowI checked the option. And now I get a confirmation before checking in.
The cool thing is that the change type in history shows 'edit, rollback'. I also updated the initial changeset's comment to include a note about the rollback and the rollback changeset id.
VS Studio 2010 Database Projects
Initial Thought on VS Database Projects
NOTE: I wrote this up as notes on my first experience with database projects in Visual Studio 2010. It contains my notes and thoughts. As 'Noob-notes', nothing here is guaranteed to be correct, insightful, or interesting :)
Meta-model
It looks to me like VS 2010 creates a meta model of the a database from the source files and compares on that. It maps tables, columns, constraints, procedures(body, parameters...), to the source files that define them.
We ran into a situation where somebody defined a pk in the table.sql file instead of the normal key.sql file. well. because the dif is on a against a meta model, it didn't matter that i changed the location of the pk def to correspond to the standard way in vs db projects. it still recognized that there wasn't a meaningful change and didn't insist on any update to the db. Now that i've thought how you would do that it doesn't seem amazing to me so much. but i was very impressed with that level of intelligence in the being to handle the situation well.
Best Dev practices
you can develop in SSMS still, but first take your development database and sync it with the db project. when you are 'done', sync the project with your dev db.
only review the dev project for checkin, never the objects in the dev db.
don't have db source files and change scripts. but do archive the change scripts that are generated and put into prod... don't hand create them.
Some interesting possibilities
with db projects you would be able to move between versions of the database much more freely.
normally db changes are managed as change scripts in tfs and hopefully equivalent changes to source files for the creation of dbs from scratch. you could think of this as two possibly inconsistent representations of your DB. for example, say a well-meaning developer creates a change script and doesn't update the 'source' for the db. and, impossible though this would be, it passes code review and gets into tfs and production. so you could create a fresh db that is wrong. maybe this would be detected or maybe not. But with one definition of the database, you don't have that problem. You need disipline to generate (and archive) change scripts, not write them yourself. and the tool will let you easily create a new one and compare it to an existing...or just compare the project to the existing db.
References
See http://vsdatabaseguide.codeplex.com for some really good (i hope, will read before posting) reference whitepapers.
March 07, 2012
Providing Metadata to SSIS dataflows
Recently I've begun to work with SQL Server Integration Services (SSIS) to extract data and load it into a data warehouse. I discovered a seemingly absurd statement in an existing package.
Oh, this must be nonsense, I thought. Let me ask if I can take this out. There is no reason to have an if that tests a contradiction (the opposite of tautology is, which is what we have here).
Well there is a good reason why the contradiction had to be there and I couldn't remove it.
SSIS must determine what columns are coming into it. It does this by looking at the first select in the SQL script and assuming that the columns and datatypes it sees there are what will flow into it. So, in this very complicated script, the IF 1=2 statement must be there.
If the sql script were simply a single select statement, the fake first select wouldn't be necessary since SSIS could get its result set metadata from the one-and-only select statement.
IF 1=2 SELECT CAST(NULL AS...), CAST(NULL AS ...) ...This was the very first statement in a long complicated SQL script that eventually...way down at the end of the file...after hundreds of lines...selected a result set that would be used in a SSIS dataflow.
Oh, this must be nonsense, I thought. Let me ask if I can take this out. There is no reason to have an if that tests a contradiction (the opposite of tautology is, which is what we have here).
Well there is a good reason why the contradiction had to be there and I couldn't remove it.
SSIS must determine what columns are coming into it. It does this by looking at the first select in the SQL script and assuming that the columns and datatypes it sees there are what will flow into it. So, in this very complicated script, the IF 1=2 statement must be there.
If the sql script were simply a single select statement, the fake first select wouldn't be necessary since SSIS could get its result set metadata from the one-and-only select statement.
Clearing Up My SSL Noob Questions
In a conversation with co-workers recently I realized that I didn't have a clear, correct view of how HTTP over SSL (HTTPS) works. So, having done some reading today, I want to record some things about HTTPS.
My main question was, where on the computer is the encryption/decryption happening? Does it happen somewhere in the windows networking stack (maybe somewhere in its TCP/IP implementation)? Seemed that handling the SSL work outside of the browser would be dangerous. But, I wondered if that might be the case since you can use a tool like Fiddler to view the clear text content of secure traffic.
As a starting point, I set about learning about SSL. There's some excellent documentation on Mozilla. I started reading about Network Security Services(NSS). And that lead me to a good intro to SSL.
So, does SSL encrypt or decrypt in the Operating System's Network Stack? Thankfully, no. The client application (i.e. web browser) actually does this using an SSL-capable security mechanism of its own. In Firefox, this is NSS.
Then, how does Fiddler allow you to see the traffic? Fiddler decrypts SSL traffic by acting as a "Man-in-the-middle". It sits between the browser and the web server and establishes an SSL connection to each, decrypting the traffic that flows through it for inspection.
January 05, 2012
Find out what is causing CPU use in SQL Server
Here is a snippet shared by a co-worker for finding out what processes are eating cpu resources on SQL Server.
exec admin.dbo.usp_serverblocks @orderby = 'secprocessortime'
exec admin.dbo.usp_serverblocks @orderby = 'secprocessortime'
December 21, 2011
In Javascript, || and && don't evaluate to a boolean
Developers familiar with c-languages may assume that Javascript's || and && operators produce boolean results. But that isn't necessarily true. What they really do is return one of the two operands.
Truthy-ness (and falsy-ness)
An expression is "truthy" if it isn't "falsy". The "falsy" values are
And because of this interesting absurdity, such a snippet may just destroy the world.
Enough with the truthy-falsy, get on to the operators already!
So, again, <expression1> || <expression2> will return ....
the result of <expression1> if it evaluates to true, else, the result of <expression2>.
So, false || undefined evaluates to ... undefined.
undefined || "Hi!" evaluates to "Hi!"
You can use this to default the value returned from an expression.
someValue || 5.0 ... will evaluate to 5 if someValue is undefined. But if someValue is boolean false, it will also do that. As it will with other defined values. so maybe be careful. You would probably only intend for 5 to be defaulted if someValue===undefined.
Inverse to the behavior of the || operator is that of the && operator. && returns the first operand (left one) if it is falsy, else it returns the second.
Confused Yet?
If you are...well so was I. I would refer you to a lecture by Douglas Crockford, it will clarify and teach you some amazing things about javascript.
Finally:
Here is an example I setup on jsfiddle.net.
Truthy-ness (and falsy-ness)
An expression is "truthy" if it isn't "falsy". The "falsy" values are
- false (the boolean literal value)
- "" (the empty string)
- undefined
- null
- NaN (The special number value, representing not a number)
- 0 (that's the number, not the string)
The above values are evaluated as false, and all other values are true.
So, false (boolean literal false) is of course, false. But, perhaps unexpectedly "false" (string literal false), is considered truthy.
So you can say, "In Javascript 'false' is true". Absurd but true.
In your code you could right:
if ("false"===true) {
launchEntireGlobalNuclearArsenal();
}
And because of this interesting absurdity, such a snippet may just destroy the world.
Enough with the truthy-falsy, get on to the operators already!
So, again, <expression1> || <expression2> will return ....
the result of <expression1> if it evaluates to true, else, the result of <expression2>.
So, false || undefined evaluates to ... undefined.
undefined || "Hi!" evaluates to "Hi!"
You can use this to default the value returned from an expression.
someValue || 5.0 ... will evaluate to 5 if someValue is undefined. But if someValue is boolean false, it will also do that. As it will with other defined values. so maybe be careful. You would probably only intend for 5 to be defaulted if someValue===undefined.
Inverse to the behavior of the || operator is that of the && operator. && returns the first operand (left one) if it is falsy, else it returns the second.
Confused Yet?
If you are...well so was I. I would refer you to a lecture by Douglas Crockford, it will clarify and teach you some amazing things about javascript.
Finally:
Here is an example I setup on jsfiddle.net.
December 08, 2011
Javascript with operator is bad
In short you don't know if you are changing a property of an object in the with or an implied global variable. See Crockford's full post.
VirtualBox/USB - Community Ubuntu Documentation
VirtualBox/USB - Community Ubuntu Documentation
I had to add myself to the vboxusers group and then vbox could connect to usb devices. I was able to let other programs access usb without starting the program as root too. So I still need to understand how the group and the bus permissions are linked. But I'm glad to be able to share USB devices with my Virtual Machine.
I had to add myself to the vboxusers group and then vbox could connect to usb devices. I was able to let other programs access usb without starting the program as root too. So I still need to understand how the group and the bus permissions are linked. But I'm glad to be able to share USB devices with my Virtual Machine.
March 24, 2011
My Code Fixes: Runtime Class and Method information
Turns out you can get current thread using Thread.currentThread()
With that thread you can call getStackTrace(). This allows you to get the current class and method name from stackTrace[0] (stackTrace being the result of getStackTrace()).
You, of course, have to be in a context where this code executes on the same thread.
Thanks to .
My Code Fixes: Runtime Class and Method information
With that thread you can call getStackTrace(). This allows you to get the current class and method name from stackTrace[0] (stackTrace being the result of getStackTrace()).
You, of course, have to be in a context where this code executes on the same thread.
Thanks to .
My Code Fixes: Runtime Class and Method information
April 06, 2010
Ubuntu 9.04 PDF Printer (You have to create the PDF directory)
I've been using Ubuntu 9.04 for 9 months. I installed the Virtual PDF printer months ago. Apparently this morning I just used it for the first time. It failed to print.
I looked in the ubuntu documentation. I'll quote the part of the article that referenced the problem.
I looked in the ubuntu documentation. I'll quote the part of the article that referenced the problem.
however, on the 9.04 machine I had to create the /home/your_user_name/PDF folder manually for each user on that machine to allow the PDF "printer" to work. Before I did so, when opening the "Printer Properties" window for the PDF "printer" I saw the following:After executingPrinter State: Idle - /usr/libs/cups/backend/cups-pdf failed
So if you can't find your output and see the above status, make sure that the PDF folder exists in the home directory of the current user.
mkdir PDFfrom my home directory, printing started to succeed. PDF documents began to appear in my new PDF directory.
December 18, 2009
Enable Classic Eclipse Update Capability
In eclipse galileo, go to window-->preferences.
In the preferences dialog, search for Capabilities.
In /General/Capabilities, check 'Classic Update'.
Restart and the classic update and plug-in management is available.
In the preferences dialog, search for Capabilities.
In /General/Capabilities, check 'Classic Update'.
Restart and the classic update and plug-in management is available.
December 01, 2009
Hibernate's "User SQL Comments" feature causes a problem with DB2
I've been having a problem where a query sent through hibernate fails with the message: executeQuery method cannot be used for update
Here is an example of the log output. The hibernate actual hql and sql queries have been replaced with [...] in order not to expose my employer's database schema information.
Hibernate: /* select [...] */ select [...]JDBCExceptionReporter:100 - SQL Error: -99999, SQLState: nullJDBCExceptionReporter:101 - executeQuery method cannot be used for update.
I googled: db2 "executeQuery method cannot be used for update"
and the second result lead me to this.
It turns out that the problem was that I had the hibernate property "hibernate.use_sql_comments" set to "true". This caused the error because the hql was prepended to the sql query. Once I commented out the setting of that property the problem went away. I suppose that the default is false for this property.
I copy and paste the material here just in case it disappears from the referenced page.
Hibernate problems and solutions
- Hibernate not retrieving entity and throwing (what seems) a jdbc driver exception.
I have a simple entity mapping and attempt to save and then retrieve the object by the generated id. Hibernate throws the following exception:
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not load an entity:
[nz.co.client.blitztecapp.model.MessageArchiveImpl#41]; uncategorized SQLException for SQL [/* load nz.co.client.blitztecapp.model.MessageArchiveImpl */ select messagearc0_.MESSAGE_ARCHIVE_ID as MESSAGE1_0_0_, messagearc0_.MESSAGE_TYPE as MESSAGE2_0_0_, messagearc0_.CONTENTS as CONTENTS0_0_, messagearc0_.MESSAGE_ID as MESSAGE4_0_0_, messagearc0_.PACKAGE_ID as PACKAGE5_0_0_, messagearc0_.CREATED_BY as CREATED6_0_0_, messagearc0_.CREATED_DATE as CREATED7_0_0_ from MESSAGE_ARCHIVE messagearc0_ where messagearc0_.MESSAGE_ARCHIVE_ID=?]; SQL state [null]; error code [-99999]; executeQuery method cannot be used for update.; nested exception is com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
Caused by: com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
at com.ibm.db2.jcc.a.co.a(co.java:2079)
at com.ibm.db2.jcc.a.cp.d(cp.java:1528)
at com.ibm.db2.jcc.a.cp.K(cp.java:316)
at com.ibm.db2.jcc.a.cp.executeQuery(cp.java:299)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1860)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3042)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:395)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:815)
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:808)
at org.springframework.orm.hibernate3.HibernateTemplate$1.doInHibernate(HibernateTemplate.java:467)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:369)
at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:461)
at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:455)
.
.Problem:
The problem is a hibernate property that I had set : hibernate.use_sql_comments=true causes the failure. This seems to be a bug in hibernate - Unset the property and things should work fine.
November 25, 2009
DB2 9.7 JDBC Driver causes a problem for hibernate native id strategy
I've been struggling with a problem where an application I'm upgrading had problems inserting new records into a database using hibernate.
App Server: IBM Websphere Application Server 6.1
DB: IBM DB2 8.?
JDBC Driver: Those packaged with IBM DB2Express v9.7
I would get a message stating:
org.hibernate.HibernateException: The database returned no natively generated identity value
When I changed my Websphere server configuration to point to an older db2 jdbc driver, the insert succeeded.
Information and workaround options are available here.
App Server: IBM Websphere Application Server 6.1
DB: IBM DB2 8.?
JDBC Driver: Those packaged with IBM DB2Express v9.7
I would get a message stating:
org.hibernate.HibernateException: The database returned no natively generated identity value
When I changed my Websphere server configuration to point to an older db2 jdbc driver, the insert succeeded.
Information and workaround options are available here.
October 12, 2009
DB2 install and config tutorial
Thanks to bits.of.info for this tutorial on db2 install and config. Way better than trying to follow IBM's docs.
October 08, 2009
In Ubuntu, sh is dash, not bash
I've been trying to install IBM Websphere Application Server 6.1 on Ubuntu 9.04 AMD64. The installer kept failing. I found this post that clued me in that in Ubuntu /bin/sh is a symbolic link to /bin/dash (a faster, smaller shell apparently). The association of sh can be changed from dash to bash as follows:
Should you desire, you can switch back to dash. Just run the above commands substituting 'dash' for 'bash' in the second one.
Here is more information on the sh-as-dash 'feature' of Ubuntu.
unlink /bin/sh
ln -s /bin/sh bashafter doing this the installer worked just fine.
Should you desire, you can switch back to dash. Just run the above commands substituting 'dash' for 'bash' in the second one.
Here is more information on the sh-as-dash 'feature' of Ubuntu.
September 24, 2009
Aptitude (Command-line package manager)
Here are some commands to use (usually with sudo):
aptitude update -- update the package repo
aptitude safe-upgrade
aptitude full-upgrade
aptitude search 'part of package name'
aptitude show package-name
aptitude install package-name
aptitude update -- update the package repo
aptitude safe-upgrade
aptitude full-upgrade
aptitude search 'part of package name'
aptitude show package-name
aptitude install package-name
Switching Linux Consoles
Turns out linux has several consoles going at once. From within my Gnome session I can press Ctrl-Alt-F1 and bring up console one. Gnome is running on console 7 so Ctrl-Alt-F7 brings me back into Gnome. Ctrl-Alt-F1 through Crtl-Alt-F7 work. Notice that Crtl-Alt-F1 takes you to TTY1 and Ctrl-Alt-F6 to TTY6. Having a full terminal to run from is handy when the session on TTY7 (Gnome) gets messed up some how.
Ctrl-Alt-F8 brings up some text, maybe system messages or something but it doesn't look like a command line I can do something with.
A little research suggest nothing runs on Terminals 8-12 initially.
Ctrl-Alt-F8 brings up some text, maybe system messages or something but it doesn't look like a command line I can do something with.
A little research suggest nothing runs on Terminals 8-12 initially.
Adding key to apt so ppa packages can be authenticated
I use a program called KeePass on Windows to manage passwords. There is a project called KeePassX for Linux that will use the same password database file I use on windows. To install it on Ubuntu 9.04 AMD-64, I did the following:
Add the source to Ubuntu (System > Administration > Software Sources)deb http://ppa.launchpad.net/keepassx/ppa/ubuntu jaunty mainAdd the project signing key to apt:
deb-src http://ppa.launchpad.net/keepassx/ppa/ubuntu jaunty main
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 095F1873
The signing key from launchpad was 1024R/095F1873 (note the part after the slash is used in the apt-key command.
The ppa page from which I got this information:
https://launchpad.net/~keepassx/+archive/ppa?field.series_filter=jaunty
Adding key to apt so ppa packages can be authenticated
When you add Third-Party Software Sources to Ubuntu, you will likely want to add those sources' shared keys to the apt so that when you update your packages a warning stating that some packages cannot be authenticated can be avoided. I get tired of it coming up. So, In my case I added
Then I added the corresponding key to apt with this command:
deb http://ppa.launchpad.net/keepassx/ppa/ubuntu jaunty mainas sources.
deb-src http://ppa.launchpad.net/keepassx/ppa/ubuntu jaunty main
Then I added the corresponding key to apt with this command:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 095F1873095F1873 is part of the PPA's PGP signing key (1024R/095F1873)
September 04, 2009
Trapped in Terminal Server Client Full Screen
The Terminal Server Client (tsClient) that I use with Ubuntu (9.04) works quite well. But if you don't know how to escape full screen mode, it can be frustrating.
The key sequence Ctrl-Alt-Enter toggles full screen.
The key sequence Ctrl-Alt-Enter toggles full screen.
September 02, 2009
Killing processes in linux
I have a laptop running Ubuntu 9.04. Recently Lotus Notes was installed on it. Notes seems to become unstable some times. The instability is so bad that I can't close the program in the normal way (mouse-click the 'x' button in the upper right corner. I would have to run
ps ax | grep notes
to get the process id and then run
sudo kill -9*procId*
to kill it.
Tyler, a coworker, pointed out to me that I could add 'Force Quit' to the panel at the top. When you press that you can just click on a misbehaving window to kill it.
ps ax | grep notes
to get the process id and then run
sudo kill -9
to kill it.
Tyler, a coworker, pointed out to me that I could add 'Force Quit' to the panel at the top. When you press that you can just click on a misbehaving window to kill it.
Subscribe to:
Posts (Atom)
Posts
