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....
| 31175 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')
...
| 33618 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...
| 86663 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....
| 20617 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...
| 9762 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....
| 8078 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 :)...
| 8623 Views
| 10% / 0% Popularity