Posted by Dan | Posted in Databases, SQL Server | Posted on 04-24-2010
0
I can’t say how many various problems I’ve had connecting to SQL Server. I’ve lost count at this point. This was the last one I had, which I’ve encountered a number of times and always successful to fix it.
Cannot connect to x.x.x.x.
Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server….
Now usually when I encounter this error, I do something suggested here at Pinal Dave’s site. This time nothing mentioned there worked. What did work, was me explicitly selecting the Network protocol, and not leaving it as the :
Still wondering why I have to do this, as I’ve never had to explicitly select the protocol before, and nothing on the server has changed in years. Will continue to investigate.
I needed to delete 30,000 files that took about 60 GB. I know traditionally it takes forever to delete it in Windows. Even using the “del” command in the console, it’s always been slow for me. Enter Robocopy. I never gave it a chance until today. The way it can delete is by syncing directory against an empty one which was just what I needed. It was able to do it in about 50 seconds. Awesome.
You’ll have to download Robocopy as part of the Windows Server 2003 Resource Kit Tools.
What I used to delete this big directory:
robocopy /MIR c:\empty-directory c:\my-files-to-delete
Posted by Dan | Posted in Databases, SQL Server | Posted on 04-04-2010
0
Currently I use RedGate’s SQL Data Compare to see differences in data across databases. It’s a great product. One thing I love about RedGate is the usability of their products. All of them are intuitive and easy to use. What if you don’t have the cash to do this though?
You could use the EXCEPT (introduced in SQL Server 2005) clause to do your comparison. For example:
SELECT *
FROM #table2 -- returns 30 records
EXCEPT
SELECT *
FROM #table1 -- returns 10 records.
You get a result set of 20 records if those 10 records in the table1 statement are in table2. You can think of it as subtracting records from table2 that are in table1. You will get the end result of 20 records.
The following statement builds on that taking the end result and putting it back into table1. This is useful if you want to sync an outdated table (in this case table1) with another table that has additional records (table2).
INSERT INTO #table1
SELECT *
FROM #table2 -- latest data table (we'll be copying from here)
EXCEPT
SELECT *
FROM #table1 -- outdated table (has old data)
Other tools to compare data across tables: CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. The great thing about this tool is also that it will check results of two SQL statements / stored procs. Useful when unit testing.
Also you can use TableDiff.exe in your “C:\Program Files\Microsoft SQL Server\90\COM” directory. Learn more about it at the SQL team website.
Posted by Dan | Posted in JavaScript | Posted on 04-03-2010
0
Here’s a quick and simple way to do this. I often have to show and hide set of panels based on whether something is checked or not. jQuery code is pretty straightforward and it comes in handy.
$( function() {
function toggleCheckbox( trigger, hidethese )
{
if ( $( trigger ).attr("checked") )
{
$( hidethese ).hide();
}
else
{
$( hidethese ).show();
}
}
// jQuery object that has your checkbox
var triggerCheckbox = $( "#showPreonDescription" );
// jQuery object for collection of elements you want to hide
// For this example, I could've also just done #panel by itself
// to hide everything
var panelsToHide = $("#subpanel,#maincontent,#nav");
// Call the function on click.
triggerCheckbox.click(
function ()
{
toggleCheckbox( triggerCheckbox, panelsToHide );
}
);
});I have the demo if you want the source as well.
Posted by Dan | Posted in ColdFusion | Posted on 04-03-2010
0
Whipped up this UDF while working on a project. It checks to see if the list is valid to use in a TSQL WHERE IN Clause. I wanted to check for a list of valid positive integers (including 0), ignoring spaces.
<cffunction name="isNumericList" returntype="boolean" hint="Check for either single positive number or a set of positive numbers. Spaces ignored." >
<!--- Useful when inserting into an "IN" TSQL list in the WHERE clause. --->
<cfargument name="list" required="true">
<cfargument name="delimiter" required="false" default=",">
<cfset var isNumericList = false />
<cfif REFind( "^(\d+)$|^(([\d\s]+#Arguments.delimiter#)+\s*\d+)$", Trim(Arguments.list) ) >
<cfreturn true >
</cfif>
<cfreturn isNumericList >
</cffunction>Now let’s test it!
<!--- Dummy Data --->
<cfset dataList = ArrayNew(1) />
<cfset dataList[1] = "456c" />
<cfset dataList[2] = "456" />
<cfset dataList[3] = "45c," />
<cfset dataList[4] = "5,,," />
<cfset dataList[5] = "565,651,34,643232,45" />
<cfset dataList[6] = "454,c,45,5454,32" />
<cfset dataList[7] = "121.45,43,565,1,1,2" />
<cfset dataList[8] = "43,54,65,1," />
<cfset dataList[9] = "67,54,73,436," />
<cfset dataList[10] = ",6565,656,77,32,3" />
<cfset dataList[11] = "" />
<cfset dataList[12] = ",43656" />
<cfset dataList[13] = "4365, 55,31,24, 5, 5 ,1, 34" />
Now let’s use it!
<!--- Use it! --->
<cfoutput>
<cfloop array="#dataList#" index="i">
[#i#] => #isNumericList(i)#<br />
</cfloop>
</cfoutput>Results!
[456c] => false
[456] => true
[45c,] => false
[5,,,] => false
[565,651,34,643232,45] => true
[454,c,45,5454,32] => false
[121.45,43,565,1,1,2] => false
[43,54,65,1,] => false
[67,54,73,436,] => false
[,6565,656,77,32,3] => false
[] => false
[,43656] => false
[4365, 55,31,24, 5, 5 ,1, 34] => true
Winrar is a great tool used for compressing files. It also has a command line tool called “rar.exe” to compress files, in case you want to batch it up. Here’s an example of the most common switches I use.
"C:\Program Files\WinRAR\rar.exe" a -m5 -r -ep1 "C:\temp\Work\Upgrades\2010-02-06\111.rar" "C:\temp\Work\Upgrades\2010-02-06\Post-Upgrade\"
First path is the location of the rar file to create. The second path is either the location of the file(s) or directory to compress.
options used
-ep1 Exclude base directory from names
a Add files to archive
-r Recurse subdirectories (will compress entire directories)
-m5 Set compression level to maximum
You can even assign a password to it using the -p switch
Posted by Dan | Posted in Databases, SQL Server | Posted on 03-25-2010
0
One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:
SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.
Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :
SELECT OBJECT_DEFINITION( p.[OBJECT_ID] ) AS code
FROM sys.procedures p
WHERE OBJECT_DEFINITION( p.[OBJECT_ID] ) LIKE '%GetBillOf%'
Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.
Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out:
Here’s the requirements document template (the “Product Definition”) I created for use when I was a project manager. Feel free to use and change as needed. It serves as a basis for functional and non-functional requirements. To check out what other documents may be needed in a project, check out my Project Documents post.
Download the Word document.
Posted by Dan | Posted in Databases, MySQL | Posted on 03-23-2010
0
Why do this?
In case you don’t have shell access to your server from your hosting provider. Also if the provider doesn’t let you writing files locally from a script.
If you want to backup from a remote machine to your local machine:
(Don’t use the < or > symbols when you type it, except the last > that redirects to a file.)
mysqldump --opt -Q -h <www.narutorp.net> -u <username> --password=<chocolatepizza> <database_name> > <C:\temp\mysql\backupdb1.sql>
If you want to backup from a remote machine to that same remote machine.
mysqldump --opt -Q -h <127.0.0.1> -u <username> -p <password> > <filename.sql>
You can also pipe that to gzip to compress, and schedule it as needed.
I guess I didn’t know that if you disable Firefox to show images, images still show if you use a Data URI string, base-64 encoded. For example, let’s take a look at the following HTML:
<html>
<head>
<title>Test</title>
</head>
<body>
<img src="data:image/gif;base64,R0lGODlhuwAsAPcAAAAAAIAAAACAAICAAAAAgIAAgACAgMHAv8DcwKrK7wAAAAAA/wRAfQJHjQRR
owqRBgD/AAI2bRNnvw5j0BAQEBJWnBZoJhSGDhwdHBk6XhdLfiFUHhpcmB9xLxqmDidDXyVkHSMl
JidtqSty0it6JCyVFiyiFzEyMi6wGDk5OUFQazdowDttmjCAPzeXHkJCQUdITENyzUCIN0x9qEuE
tkiN3keTUk6Zwk2rNEq1J1BRUlVcUlJbblV1s1ehYFxcXV6HqFiAs2KRsljBLl1bWWVlZGefzl6a
4mtrinGX3WiycXC/D2jNNm9vbnaQyXaixYBhN3l8eXp6mYOOlXeq5oW9PX+41XjAgH7aEH3iSoQA
AIFxWISEAIaw54S8N4bMjI+EXYmIho+TqYulvIen2IytvZSx2pG764jsEZNoK5KQjZSYm5SUnJWU
1pDLPpvmOpbamppPM5mTfZqZkpycmZ2c15qylaHOY6abVqGfvKS7zaHkpKwIDa8OE66uqqqq566y
xq3Hg63G2a3ur7g1ObRKGbVWHLZ+MbR9UbS2uLTL37TafrkRFbopKbpmI726tLq9vbq5zcLB8L7R
m7r6vcIPGsUYIsk3IsY8QshKMcVNUcdqasZ7SsKAMryQXMiUPMa9zsrMzMDN1sulR86qpcy4pc7D
Ss3HwNDMxdIVJNdEJtZYPdhrStl0edSnfNHAttTQVtXVatPQyNbX2uMWC9saK9w0E+FMWNpZY91s
st1+LNx7VtyzcdqzydzXWeUBAeAnOOtiNudkxuhryOxzC+iBieaMzOmOHN+MWuamque0tuTl5fAP
CewuwOlFFe5IyeRWBPNY0Ox90PClheyqHvDGg+nQtO/v7/cAAPhSGPdSGP9sGviPavCVN+yjY/qz
IfWxWPK9bfXAofPELfLLSPTUMvPR0/neQfrcZPrmdPnqyfjsVvf3APv29v5iGv+vG/zphPvmpvv2
ev3znfv2tv742/7876KjpICAf/8AAAD/AP//AAAA//8A/wD///3+/SH5BAAAAAAALAAAAAC7ACwA
hwAAAIAAAACAAICAAAAAgIAAgACAgMHAv8DcwKrK7wAAAAAA/wRAfQJHjQRRowqRBgD/AAI2bRNn
vw5j0BAQEBJWnBZoJhSGDhwdHBk6XhdLfiFUHhpcmB9xLxqmDidDXyVkHSMlJidtqSty0it6JCyV
FiyiFzEyMi6wGDk5OUFQazdowDttmjCAPzeXHkJCQUdITENyzUCIN0x9qEuEtkiN3keTUk6Zwk2r
NEq1J1BRUlVcUlJbblV1s1ehYFxcXV6HqFiAs2KRsljBLl1bWWVlZGefzl6a4mtrinGX3WiycXC/
D2jNNm9vbnaQyXaixYBhN3l8eXp6mYOOlXeq5oW9PX+41XjAgH7aEH3iSoQAAIFxWISEAIaw54S8
N4bMjI+EXYmIho+TqYulvIen2IytvZSx2pG764jsEZNoK5KQjZSYm5SUnJWU1pDLPpvmOpbamppP
M5mTfZqZkpycmZ2c15qylaHOY6abVqGfvKS7zaHkpKwIDa8OE66uqqqq566yxq3Hg63G2a3ur7g1
ObRKGbVWHLZ+MbR9UbS2uLTL37TafrkRFbopKbpmI726tLq9vbq5zcLB8L7Rm7r6vcIPGsUYIsk3
IsY8QshKMcVNUcdqasZ7SsKAMryQXMiUPMa9zsrMzMDN1sulR86qpcy4pc7DSs3HwNDMxdIVJNdE
JtZYPdhrStl0edSnfNHAttTQVtXVatPQyNbX2uMWC9saK9w0E+FMWNpZY91sst1+LNx7Vtyzcdqz
ydzXWeUBAeAnOOtiNudkxuhryOxzC+iBieaMzOmOHN+MWuamque0tuTl5fAPCewuwOlFFe5IyeRW
BPNY0Ox90PClheyqHvDGg+nQtO/v7/cAAPhSGPdSGP9sGviPavCVN+yjY/qzIfWxWPK9bfXAofPE
LfLLSPTUMvPR0/neQfrcZPrmdPnqyfjsVvf3APv29v5iGv+vG/zphPvmpvv2ev3znfv2tv742/78
76KjpICAf/8AAAD/AP//AAAA//8A/wD///3+/Qj+APEJHEiwoMGDCBMqXMiwocOHECNKhCirosWL
GDNq3Mixo8ePIEOKHEmypMmTKFOqXMmypcuXMGPKnEmzps2bOHPq3Mmzp81TYV6cCBHiBIwwp1Bp
RIUqlFOlIQFIBYDv46l79x5B3WjDz6mKfg5sVUlpkNk9e+Co/cL2ipK3PlP6SRGiCB2B94qESOEH
Y6hZy5ZdGzwLUl+PVAdS7YgvxQsdO/CJ3Viiqx0ZD2xo1XgKV63PtVKJtkSatKavssoOQrv2y5XX
b+HGNekHgw5IoURNmSIqFCQdIQ5XnDV48LJ2/67JOtAx8cXFGMUggZECho4fRZqEeaTRTg4XMkz+
oPBwYbtGXMWQI//Hvv2/dq00VVyN9l/rK/9i+/Axu+SpEzo4JYoRQQRhhBF6hKLDCajJQlxxgzl1
zTJjYbSYVFVBZ9E9YoSSgg5NgIhPGHY1aJEPTAwxRA4ooHDBDsJdVMt6wLhnzi2p4HJNO5ZUhNYe
7Fng2mvsKbEff/2NhA8Gj+QmiAQVVCCBBAk+EkJVw0FonHGhcERVYl9iaZEU7Vj3TyhNFLGGGk1E
wR1GNmTBRIo5XLBBEfdUKEst/wDwD59S/dMKLuZY0so/jPioVpCvuVXkkSVJpdFUFU0lqUWWVgoA
pptulEIRApIhwQxjCKJIb7IUkcJFWmrZ5Ub+YcoS60VSRJICPqHQEaIf96QZY0U4ZCGnCxvAUAR3
XmHEJ3s1uvdPMpYkg6iicDDqlhLs7WeDDSR1mpG33lb6HKayghsuRiHM4ZQiRkhAgxBkCILqHCGw
qggZZjxY3KuT4nOhv2JWhIQUL2gXhRQB+tEEEb/KIsMQwoKgw5sD32Pisu1Zeg0mjSjzTx8VwcEW
o7Fluy23Ip0rq7gst9zypjCvrLJFGGS3hhgsNMAAAw1MQccaaWJgUShJdDGLIl2QASG/3wIsq9MX
3aOCDjAM/EMiByQSBsMahUFCDjhwpwISeUiBjx9bpeLs2q308XHIIq/d3sktjJQpy+COq7f+zHzP
XBEGdPgBiB4iOAAEEE/oAYlhcwhdUQxdJLDMLAkkYQaXXjo9q0WnqKFDETDAsEYoftDxQhF+1CGL
JBnZQUIUOujAgxhSAGJeRWq7FygmhGpCyD98VMTWF3KzR7fd5O6dt4WUruy837KEQEduijggwhil
9hbKKfRWBMgIVJyhiCBndBFDYMswbaHmUF90ABFR+OFUWGEcdUAds6iO0SxhpLCG7B3yQxQsVhFL
rMdZ5sBEHzBhjnaATBZDCpIFtGW8FlgQeS5zngZVdq5OeZAjn3KKIBqgM549wSmnUFVFnDCBGlBh
FqLoQg1GAJj0Ze5fGrqIGsJwDz/Qr2D+B8hDGyShP4voIBGQgMEPpnAAwyxMDajRRDGK5574VAQ2
QTLStip4wZQl74N4m5SFxrgRfISgSXqgAQ1YwIIgJCgUj8AAllg4giPAkAozrKGJ1ofDgHEuCmFo
wsJOdwBJtEEWf8iIIHNFB0gkIgowIMLZUHMKTViiEpVgBCOixR5M8KEPp7mio/4xwZOxx4It6EC3
LrVBVs6sg2TkDIAEJAqsOKVLC0INICYwghqIQhBHqMH5JrfH57AvhxZBxSPUUAQiFEEyefgDIAyJ
EVT4QQf4uEcoPAc/OjyimBchhSU24UBSYKRkpPSBKf9hwQ6oMkkjqY0ODuCU3TgliRj+iFEMeikK
PcyQDIGZBTgrJSZkVvMAPvTDI+ogTSJypgn4+N8OouDNgWKkFI0gRB9Kcc63+MACJ9uWBdrpTniS
xA9DsUsUKFoEDJzgVwdYwQjG8IQVBIGes5hFWDJHUJLMgpob8QM+sqOGb47kFITgwys6qq2QotKd
FrCASUkClBeEAAMYCMELkLKUMfQgCD18xAFOQVbEYIgkf/hDJDqiTIVatCOn2ERGjKROuqEylVGV
6lT3yte++vWvgA3sSAbwkZw+ZSal4OhHPPGJUZjisbCIhWQl64tYpMRSmM2sYD8ygH1A7x41NGwo
EOoSXsSjHvWoBnM4gghqjKMc6Ej+BzzmQY/a0mMe8liHZU8CgAD49rcAEIBwhwu9zcqiswPoU1BB
mwg/JOK5iaBDVFi5Px6gg7bxgMchOtKJ16ZDHai1hz3aUw/c+gIlvf2tb4M7XOEWV7DI/UdnZ+bD
ZdAlFEiExBpAAoAD+HdmTzjEOuBBj+x+Iwoc6e454jENdagiGxDOhjvEQQ95nNcivTCGNKRhjF7I
IhBVCHEgmJfZEhtXI/OV7z72MQCV+XAWEJ0F1g6wBj1Z6AD+OsDMbpCGc+iiGuk4xzt4kOBxwGMX
l1DHNCTsjm1sIxj1kAcsMByNKld5GF5Ygpa17IXnqHe97XXvTd5bks4eoE8rBkD+mjECXawtxylr
WK2XcFwulYlABONIBzN+MQ4oNMwihyBHNWyRjV3UIxjuSPQ23DGNeUxZFr2wspXxsOVK3wFTXw4A
e9tLZljdLXmgZomZ/3GAfSxgASsudbiQiOMw3DIUa3jTnKWS5nOtgQEVUMEcwpEG6criFcU8BDwy
AWFbWEMczkh0k92hjkdL4xnPEEYvepGLLd9hEnfYMqdKrFmTdDqDKRl1qU2N6lKr2iKQOMAJsJqC
v8A6EWZdcZpbjJF7ZCACDOCACGggh9OqIxxyloUndkFoCK+iHrtwRoSzwQ11VGQY0PawLEqBBSy4
wSJuqDimv7xp4vJ2jDGrswf+L7U8kAzgzONWc4v3cW6L0HgvL7BOTtfwZ03JW94AoHdGBJiBfF93
HvOAxzEu4opGLNwW4kBteMX7j4oII+IVobjFMa7xSmW642L2dqZIvkGQh9ojyU05i1ms5nOFAnZF
odrMay6rm+Nc5xthwBZ8AY94zAMd7/C1LBBB8Aivgh6scIbCIcwNazy8Gc2IxrRzUXEsLGISi2j8
trn96W7FMuTPax7mTS525JI9I6SDaAjuAYO1e8ntns15cTUABl3Q4hfoOMc3ZmARR4TjEsWuRjWS
rexsqGMUFekF4offDDw0/viXtjrHIcD85n/7W5fv+vI2z9k0jx25M4PELKL+gJsDFGFyNB+omt8+
gBavngXjQAc85IEOR5jB14boBjJs4QxWxOPBvZ8GOj5hEWMQH/FvgAYCKIBvsBXppV4A0HzO93HK
I32g9nwVMV/X93kaARizUASegz4050OyhinzVn4gWFz3oAEsAAa6BwVCoAhzUBGGQAzgkAmMUA3I
oHDKFgzx8A2dcBG98HTR5mGL8AZAuAgkRnnNEynRJ3Ka4koksXKeB3c7RwfHgT6BsQb34E2TknMg
WH7UtRE8wAAicANGYAVPAANQUQjEcAzegAzVwAi20IbOgHTjQA05yFv5UId2mIAKCAEQCBN7GIHd
1hEHEAZrEBgTMkA2pilgIfh8dKACGZABH3AsFhEH0EAM3RAOulcN8ZCJ/3YMx4AI6GWHd5iHejgb
fagSqHAAj2BUH1GELBEHhVAIhmAIjjCLtDiLnoheRFh5J7aLvNiLvviLwBiMwjiMxFiMHBEQADs=" />
<img src="TrayItems.gif" />
<img src="http://www.shinylight.com/wp-content/themes/Wiking/images/button_rss.png" />
</body>
</html>
Which outputs:
You can see for yourself here, which is the HTML page.
Now, let’s disable showing of images in Firefox – Uncheck “Load images automatically”:
Refresh the page and see that you get:
Strange that they haven’t blocked this as well. This happens in Firefox 3.6 for Windows. I tested it in Safari 4.0.4 on Windows and it none of them showed when images are disabled.
