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')
...
Comments (0) |
Print |
| 7044 Views
| 14% / 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...
Comments (5) |
Print |
| 10644 Views
| 21% / 28% 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....
Comments (0) |
Print |
| 4988 Views
| 10% / 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...
Comments (2) |
Print |
| 5846 Views
| 11% / 11% 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....
Comments (1) |
Print |
| 4825 Views
| 9% / 6% 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 :)...
Comments (0) |
Print |
| 4885 Views
| 9% / 0% Popularity
Posted At : Nov 22, 2010 12:47 PM
| Posted By : Ed Tabara
Related Categories:
SQL
This weekend had a hair pulling moment when noted that on one of my sites the DB was down with a "SUSPECT" mark on it. Checking through logs told me that at restart replication didn't went well. Tried many different things to solve the problem but nothing worked till found a short list of commands that worked just FINE! maybe i lost a couple of broken records but that's fine taking in mind that nothing else worked and i was about to lose everything.
So here is the magic code:
EXEC sp_resetstatus ?DBname?;
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb(?DBname?)
ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (?DBname?, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER
Hope it saves someone else nerves. :)...
Comments (0) |
Print |
| 1649 Views
| 3% / 0% Popularity
