Posted At : Jul 18, 2013 15:46 PM | Posted By : Ed Tabara
Related Categories: Other, SQL

Few days ago i was playing with MyConnect Server and had to install the JDBC driver for MSSQL in order to save speed tests data to a database. And still no data got inserted into the DB. Checking the logs i found that "Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library" which was weird as JDBC 4 is exactly what i downloaded and installed. The answer came after checking driver's installation folder. Apparently, because there was both the old and the new jdbc JAR files in the folder, MyConnect Server was picking the old one (sqljdbc.jar) and not the new one (sqljdbc4.jar). So, right after i stoped MyConnect service, deleted sqljdbc.jar and started MyConnect service again, things started to work as expected. It happened to me for MyConnect Sever software, but it easily can happen with any other software that uses jdbc driver. Hope it will help someone....

| 31009 Views | 36% / 0% Popularity


Posted At : Sep 19, 2012 20:34 PM | Posted By : Ed Tabara
Related Categories: SQL

Some time ago had a nightmare when trying to create Full-Text Index for a table with a "weird" name for the Primary Key. And because i did not have access to DB structure i had to find a way to get PKs info programmatically. So, for my later reference and for anyone that will have similar problems, here is the code to find the Primary Key name in MS SQL: select * from sysobjects where xtype='pk' and parent_obj in (select id from sysobjects where name='tablename') ...

| 33429 Views | 39% / 0% Popularity


Posted At : Nov 02, 2011 11:38 AM | Posted By : Ed Tabara
Related Categories: SQL, Security

A new report by iMPERVA shows that from 2005 through today, SQL injection has been responsible for 83% of successful hacking-related data breaches. Based on the sites they were monitoring, over half of attacks originate in United States. Country Number of Originating Attacks % United States 48176 58 Sweden 8850 11 China 6709 8 Great Britain 4970 6 Vietnam 2412 3 Netherlands 1963 2 Bulgaria 1359 2 Ecuador 1356 2 European Union 1093 1 Germany 971 1 Other 4748 6 The vectors of attacks are: Direct Query Manipulation This is often done by appending a logical expression with a known value to the parameter that the application expects, like:? OR 1=1 to get a true value or 1?/**/ aND/**/?8?=?3 to get a false value. This type of vectors is most often used to establish the existence of a SQL in...

| 86454 Views | 100% / 1% Popularity


Posted At : Apr 18, 2011 11:18 AM | Posted By : Ed Tabara
Related Categories: ColdFusion, SQL

Got today an interesting error message: [Macromedia][SQLServer JDBC Driver][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. When checking the code, i found out that in the lie where the error happened i have a query with an INSERT in loop. Why make say 10 queries with INSERTs while you can have 1 query with 10 INSERTs? But it seems like i got in a situation when there was 684 INSERTs in that query and each having 4 parameters, that making 2736 parameters being inserted. It seems like there is a particular limit of 2100 parameters that can be passed in. Good to know....

| 20456 Views | 24% / 0% Popularity


Posted At : Jan 12, 2011 13:42 PM | Posted By : Ed Tabara
Related Categories: ColdFusion, SQL

Had an interesting task last days. To make a script that would access a couple of links, get from each one zip file, extract CSVs from them and then import the recors to a MSSQL Database. So with just a few ColdFusion tags like CFSET, CFHTTP, CFZIP, CFLOOP, CFDIRECTORY and CFQUERY it's a piece of cake to make it work. BUT, (yeah yeah again a BUT hehe) because the CSVs had a lot of records and it had to be done repeatedly for many of them, it came to be a serious issue from performance perspective. In other words it took about 28 minutes to run that task for 1 day data. So, the smart thing to do in such situation is: let each tool do what it can do best. In other words i had to make MSSQL Server do the import instead of ColdFusion. The steps was as follow: Created DTSXes for each of the import format Created a Stored Procedure that would execute the right DTSX for the right CSV with data In the ColdFusion script change the code that read and insert te records with just a call...

| 9584 Views | 11% / 0% Popularity


Posted At : Dec 16, 2010 14:45 PM | Posted By : Ed Tabara
Related Categories: SQL

Equals: = This one is the most efficient SQL query, especially if the WHERE clause is covered by an index. Greater than, less than: >, >=, Note that Microsoft SQL Server rewrites a BETWEEN to >= and . Like: LIKE Note that if you start with a wildcard, then SQL Server will do a table scan, whether there is an index or not, such as: LIKE '%E'. Different from: , NOT Note that Microsoft SQL Server rewrites a NOT to . Note that SQL Server will do a table scan, whether there is an index covering the WHERE clause or not. So, use = as much as possible and use as least as possible....

| 7940 Views | 9% / 0% Popularity


Posted At : Dec 04, 2010 21:41 PM | Posted By : Ed Tabara
Related Categories: SQL

Sometimes may happen in a database there to be fields with different Collations and it will make you problems. Previously i wrote about a way to get over this issue directly in your queries. Other option is to change the collation on the sql server for that "problem" column. Here is an example of how to do it: Alter Table yourTable Alter Column yourColumn varchar(1000) Collate Latin1_General_CI_AS Easy :)...

| 8462 Views | 10% / 0% Popularity