SQL Server Technologies

File storage - blobs / file system / document storage solution?

Heat_Rash — Tue, 03 Aug 2010 05:51:30 GMT

Hi,

I'm working on a very interesting project. Part of the project requires the storage of files (mostly PDF's) within the application. Now I'm not sure what's the best way to go about doing this. Should I save the files as blobs in the database, or should I rather save the files in the file system with their locations saved in the database?

I need to consider the following:

1. The files will need to be viewed / downloaded frequently.

2. Space is a limitation, and I need to store as much data as possible using as little space as possible.

3. The file size will range up to 10 mb per file.

4. It is a web based application.

5. The application is quite big, expected total data will be about 3 TB.

Any ideas on which the best route to take would be appreciated. Should I rather look into some sort of Document Storage system like FileNet or Centera? SharePoint is not an option.

Breaking the Relational Model: What is the recomendation in terms of Best Design Principals?

NeverDie — Thu, 01 Jul 2010 14:47:59 GMT

Here's the scenario:

A Person can have many attributes, such as:

Name, Surname, AddressLine1, Cell, WorkPhone, PassportNumber, IDNumber, ApplicationGUID etc...

However; only a small subset of these fields are compulsory for a Person, the rest can be NULL.

Do we:

[ A ]

Create a wide table with many NULLABLE columns?

[ B ]

Break the relational model and have a concept of Two Vertical Tables along the lines of tb_PersonAttribute and tb_PersonAttributeDefinition?

[ C ]

Have multiple tables that JOIN to person?

What are your thoughts on this?

"SQLBoot.exe is corrupt or has been tampered with" - What causes this?

horatio — Thu, 03 Jun 2010 07:26:51 GMT

Hi all,

I've had an interesting couple of days with one of our clients running their app on a SQL 2000 Enterprise installation. What happened was the SQL Server service did not want to start after a reboot (and multiple reboots later), stating that SQLBoot.dll was corrupt or tampered with. After uninstalling and re-installing SQL, SQL Server worked for a few hours, then wen't down again with the same issue. We had to backup the d.b.'s, format and re-install the box.

Now, keep in mind maintenance is not done often on that server, so it could be one of a few things that caused the problem. Our IT guys say it could be a virus that attacks SQL, THEIR IT guys are blaming the fact that the primary partition had only 1 GB of space to host the swap file (the server has 2GB of RAM). We sorted out the space issue now, we have over 30GB free space currently.

What could have caused the SQLBoot error? I even tried to copy a fresh DLL to the SQL directory and re-register it, but no luck.

Any guidelines/suggestions/ideas would be much appreciated.

Regards,

The H......................................................................................

INSTEAD of Trigger Happy :)

NeverDie — Thu, 13 May 2010 12:54:56 GMT

Is there a HUGE performance implication when it comes to using INSTEAD OF triggers on a Table?

Resize Transaction Log Default Size to Less Than It's Current Size

GDI Angel — Tue, 30 Mar 2010 07:20:48 GMT

An interesting bit of education that came out of a "small" issue that I had with a transaction log not shrinking below it's initial size over at SQLServerCentral.com

Pun intended :-)

SQL Server 2005 xml query

terencen — Thu, 25 Mar 2010 10:43:02 GMT

I have a table in SQL Server 2005 called ITC and an xml field in the table called ResponseXML. I wish to check if certain tags exist in the xml.

I tried something like this:

SELECT

ResponseXML.query('

declare namespace a="http://www.w3.org/2001/XMLSchema";

declare namespace s="https://secure.transunion.co.za/TUBureau";

/a:BureauResponse/s:ConsumerInfoNO04/RecordSeq'

) FROM ITC

with no luck.

This is an extract of the xml:

BureauResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

RawData xmlns="https://secure.transunion.co.za/TUBureau"> ZAC</RawData>

ResponseStatus xmlns="https://secure.transunion.co.za/TUBureau">Success</ResponseStatus>

ProcessingTimeSecs xmlns="https://secure.transunion.co.za/TUBureau">0.953</ProcessingTimeSecs>

ConsEnqTransInfo0102 xmlns="https://secure.transunion.co.za/TUBureau">

PossibleAdverseIndicator />

ConsEnqTransInfo0102>

EchoData0001 xmlns="https://secure.transunion.co.za/TUBureau">

ConsumerInfoNO04 xmlns="https://secure.transunion.co.za/TUBureau">

RecordSeq>01</RecordSeq>

Part>001</Part>

PartSeq>01</PartSeq>

ConsumerNo>481612284</ConsumerNo>

Surname>PRINSLOO</Surname>

Forename1>JACOBUS</Forename1>

Forename2 />

Forename3 />

Title>MR</Title>

Milli Mystery Second -> What can't we write Millisecond 999 and 998 to the Database?

NeverDie — Sun, 27 Sep 2009 15:19:24 GMT

What can't the millisecond values of 999 and 998 be written into a DateTime field?

*.999 writes *.000 to the database

and *.998 writes *.997 to the database.

Here is some sample code you can run:

IF NOT EXISTS (SELECT 1 FROM sys.tables (NOLOCK) WHERE NAME = 'tb_TestTheDate' )

CREATE

TABLE

tb_TestTheDate

(

theDate

DATETIME

)

INSERT

INTO tb_TestTheDate (theDate) VALUES ('1 Sep 2009 23:59:59:999'

)

INSERT INTO tb_TestTheDate (theDate) VALUES ('1 Sep 2009 23:59:59:998'

)

SELECT * FROM tb_TestTheDate

IF EXISTS (SELECT 1 FROM sys.tables (NOLOCK) WHERE NAME = 'tb_TestTheDate')

DROP TABLE tb_TestTheDate

SQL 2008 Upgrade

SilverFox — Wed, 02 Sep 2009 13:36:46 GMT

We are about to start our SQL 2000 to SQL 2008 migration. I need to determine the best way to do it (for us). Does anybody know somebody in the Western Cape who has any experience with this and is willing to visit my site for a day to help with the migration planning.

You may contact me on 022 701 3171.

Please have a price per hour / per day ready when you talk to me.

SQL Server 2005 Auto Incriment on complex key

dequadin — Tue, 01 Sep 2009 13:06:25 GMT

Hi,

I wonder if someone could tell me if the follow is possible. I know I can make a column in a table auto incriment by using IDENTITY(1,1) in the create table script.

But can I do the following:

Main_Table

Key_Field

Sub_Table

Key_Field SubKey_Field

1 1

1 2

2 1

2 2

So basically Key_Field is the primary key in Main_Table and auto incriments. Key_Field & SubKey_Field are the primary key in Sub_Table with Key_Field being a foreign key back to Main_Table. SubKey_Field then auto incriments based on the value of Key_Field.

I hope than all makes sense :)

Which Query is more effecient ?

darkpoetry — Fri, 21 Aug 2009 13:37:27 GMT

two queries both return the exact same result. which one would be more efficient ?

SELECT [table one].*, [table two].id
FROM [table one], [table two]
WHERE [table one].id = [table two].id

SELECT [table one].*, [table two].id
FROM [table two]
LEFT JOIN [table one]
ON [table one].id = [table two].id

Stored procedure for multiple ids

Skulk — Fri, 17 Jul 2009 16:43:02 GMT

if I have a stored procedure like bellow wat format does the string of id's have to be in?

ALTER proc [dbo].[sproc_tbl_Orders_GetByMultiID] (@IDs as varchar(4000))

Declare @SQL varchar(7999)

set @SQL = 'SELECT [OrderID] ,[QuoteID] ,[UserID] ,[CompanyID] ,[SubCompanyID] ,[ProductionStage] ,[DateTimeStamp] ,[OrderValue] ,[Archeived] ,[Comments] FROM [tbl_Orders] WHERE [OrderID] in (' + convert(varchar(4000),@IDs) + ')' EXEC(@SQL)

Thanks in Advance

Visual Studio 2008 DB Tools and SQL 2008

DoubleJ — Thu, 20 Aug 2009 10:54:49 GMT

Don't know if you've run into this issue, but the database tools in VS2008 SP1 do not suport sql 2008.

for example, if you try and use the schema compare, it gives you this error "Schema Compare does not support SQL Server 2008"

Luckily,i found this update for VS 2008. It enables SQL 2008 support

Download

Weird problem with SQL results

WilliamVL — Mon, 03 Aug 2009 12:29:03 GMT

I have the following SQL Statement, but for some reason, it is not functioning correctly on this specific database.

SELECT * FROM dbo.impSourceEmployee WHERE JobTitle_Payroll NOT IN (SELECT PayrollValue FROM dbo.impMatchProfileValues)

impSourceEmployee table contains 150 rows and the query above should return two rows as all other values are matched in the impMatchProfileValues table. If I remove the "NOT" in the statement, 148 rows are returned, but with the "NOT", no rows are returned.

impSourceEmployee.JobTitle_Payroll is varchar(150) with default collation.

impMatchProfileValues.PayrollValue is varchar(200) with default collation.

The field sizes are different, but the value here is basically 70 characters and this statement works on other databases. The problem seems to be on this specific database.

Invite Microsoft BI User Group Meeting - JHB

shivanii — Mon, 17 Aug 2009 07:23:46 GMT

2 September 2009

Come and share your experiences, both business and technical, at the South African Business Intelligence User Group Meeting.

Customers will be sharing case studies, partners will be showcasing evidence and techies will be giving expert advice and opinions.

Learn, Share, Grow.

Date	Wednesday, 2 September 2009
Venue	Auditorium 2, Microsoft South Africa, 3012 William Nicol Drive , Bryanston
Time	13h00 to 15h30

To register: PLEASE CLICK HERE or
Call 0860 22 55 67

1. Registration

2. Welcome

3. BI Power Hour:
Converse with Industry Specialists on Various BI Products and find out the latest in MS BI innovations

4. Discussion

5. Next Meeting

2 Resultsets

SilverFox — Wed, 15 Jul 2009 12:58:12 GMT

On SQL 2000:

I need to extract data from an ABB system. The only method that works correctly and fast thus far is a DTS package. I am now trying to make this solution generic. I execute the DTS package (that puts the data in a table) inside a stored proc and the stored proc returns the data from the table. The problem is that the DTS package gives an output and so the stored proc returns 2 resultsets.

Does anybody know how I can suppress the DTS output or is there a way in TSql to distinguish between the resultsets. Example of what gets returned:

output
------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSActiveScriptTask_1

DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 60 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 60

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun: Package execution complete.

NULL

tID      tTagName                                       tDateTime                               tValue
------- ------------------------------------------- ------------------------------------- ----------
151    FAB2-FEED-RATE:value,log1     2009-06-01 07:00:12.000          0.0
151    FAB2-FEED-RATE:value,log1     2009-06-01 07:01:12.000          0.0
151    FAB2-FEED-RATE:value,log1     2009-06-01 07:02:12.000          0.0
151    FAB2-FEED-RATE:value,log1     2009-06-01 07:03:12.000          0.0
151    FAB2-FEED-RATE:value,log1     2009-06-01 07:04:12.000          0.0

get statements

Skulk — Tue, 14 Jul 2009 07:14:00 GMT

Is there a way to get the statements of a procedure, view or function

eg: sp_getStatements 'sproc_tblCustomers_getAll'

and that would return something like

select * from tbl_Customers

Thanks

Question about SQL Server Collations

horatio — Thu, 09 Jul 2009 10:32:09 GMT

Hi all,

Quick question about something we were discussing in the office just now.....

Our biggest client in the UK is preparing to transfer all the databases for their clients to SQL 2008 sometime in the future. Now, we have had issues in the past with different collations being installed on the SQL servers (SQL 2000). Now, when they begin to migrate everything to SQL 2008 (no servers have been set up yet, this is just talk now, but we want to be ready), which collation should be used on the new servers? Currently, they are using Latin1_General_CI_AS, we are debating on whether or not to use SQL_Latin1_General_CP1_CI_AS or the first-mentioned. I am personally leaning towards the latter, my bosses towards the former.

Which one would be best considering our current situation?

Thanks heaps!!!!!

The H........................

Sql server deciding to change its port.

kosmik — Fri, 26 Jun 2009 15:08:36 GMT

Now here's a funny one I came across today.

Scenario: Company A will allow Company B access to their internal network via internet however locking down the connection to a specific originating IP and port. Company B would access a sql instance, polling a table for records. Now in theory this is all pretty easy, setup the link, allow the sql port from that IP access and ensure that you have locked down a sql account on your server to prevent mischief.

Well lets begin:

Nice fresh dev server installation with all the MSDN tools, visual, sql os everything. BLOCK. No-one can access the sql server from outside. However the dev team can and the problem made folks scratch there heads a bit. Then I come accross a intresting little tidbit that SQL Dev edition does NOT allow remote connections to the instance. You can via name but not via ip. So , re-install.
Now we have a nice clean installation, sql enterprise standard. Machines on the network can access the instance via either name or ip. BLOCK. Can't access the server from outside.

Now, since the server could be accessed from within the network, I was pretty sure that it had been configured correctly. So I double checked the communication port in Sql Configuration Manager and yep, it was set to the standard 1433 which Company B would be coming in on. So I thought, it must be the firewall. Checked the server, no firewall blocking or enabled. Checked with the independent firewall on the network, nope all traffic being routed correctly. Checked with the ISP, nope all traffic being pushed from them through the router to the firewall.

Now our Firewall guru's, our ISP , Company B's rep and myself were all stumped. Everything was configured correctly from instance to ports and traffic routing. We tried and played for days to get it working. Finally, at a suggestion from our firewall guru I installed "WireShark", awesome packet sniffing tool, go play with it. Now according to that, all sql traffic was communicating on a different port! Double checked the setup,nope stil correct. So what the hell, gave the new port to Company B and we're all happy and up and running on a different port which is not such a bad thing from a security perspective.

So, has anyone come across this before? I wonder if it's a consequence of the vitalization but it really shouldn't be as the server is virtual running on proper vmware enterprise architecture? Will be posting the question in a few places. I't almost like the server had a wrapper around the port but the funny thing is all our other management studios connected as normal.... through same said port. ?????

SqlTransaction and Stored Procedure

WilliamVL — Tue, 21 Apr 2009 20:49:14 GMT

Hi

I have an employee object that gets transported between the client and server application.

To retrieve the employee object from the server, I call a stored procedure called GetEmployee on the database and the object is constructed with the returned dataset.

Then, when I need to save the employee, I send the employee object to the server, a new SqlConnection is created and a SqlTransaction is started, and then a bunch of executions are done on the server side to save the employee. After that, the transaction is committed, then a new employee object is created from the database based (to include all the new IDs and so on) and that is then returned to the client. (so the object is saved and refreshed in one call).

The problem is, that when retrieving the new object directly after saving it, the old information (before being saved) is returned instead of the updated data. If I do a direct Select statement on the employees table, then the latest data is returned, but if I call the stored procedure, the old data is returned.

Any advice will be appreciated.

Rolling back to a particular point in time

GrahamD — Tue, 05 May 2009 15:45:20 GMT

Hi,

We have a SQL Server 2005 database, whose Recovery Model is set to Full. The Transaction log hasn't been backed up in... who knows?

Does anybody know how we can use the current transaction log to roll back the database to a particular point in time? (No, the last full database backup was also done far too long ago)

Cheers
Graham

Transactional Replicatoin: A Show of Hands Please

NeverDie — Sun, 26 Apr 2009 21:53:57 GMT

I often come across new issues and very little answers on the net regarding Transactional Replication.

Please can I have an indication of how many of you actually use Transactional Replication on a daily basis on Production systems with Live Data?

Refresh module dependencies

GarethStephenson — Tue, 07 Apr 2009 15:20:07 GMT

Hi guys,

I've just written this SQL script to update all modules in a SQL database so dependency information is updated.

I'm not guaranteeing that it works 100% when it comes to dependencies, but it does a pretty good job.

Herewith the code:

DECLARE @proc_name nvarchar(776) DECLARE module_cursor CURSOR FOR SELECT object_name(o.object_id) FROM sys.objects o JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.type IN ('P', 'FN', 'IF', 'TF', 'V') AND m.is_schema_bound = 0 OPEN module_cursor FETCH NEXT FROM module_cursor INTO @proc_name WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_refreshsqlmodule @proc_name FETCH NEXT FROM module_cursor INTO @proc_name END CLOSE module_cursor DEALLOCATE module_cursor

Can't Execute Proc as Owner

NeverDie — Mon, 06 Apr 2009 14:01:49 GMT

I can call the following just fine:

MASTER.dbo. xp_fixeddrives

EXEC

But when I try to put it into a stored procedure and want to execute is as the OWNER; I get no results. Do you know why?

CREATE PROC dbo.ShowDriveTest

WITH

EXECUTE AS OWNER

BEGIN

EXEC MASTER.dbo.xp_fixeddrives

END

-- exec the proc

EXEC

dbo.ShowDriveTest

-- exec xp_fixeddrives outside the scope of an SP

EXEC

MASTER.dbo.xp_fixeddrives

INSERT INTO/SELECT Into, but Not Into a New Table

GDI Angel — Fri, 20 Mar 2009 09:10:47 GMT

Hi guys

CREATE TABLE [dbo].[b1](
[b1] [int] NOT NULL,
[b12] [varchar](10) NOT NULL
)
GO

CREATE TABLE [dbo].[b2](
[b2] [int] NOT NULL,
[b22] [varchar](50) NOT NULL,
[b1ref] [int] NOT NULL
)
GO

truncate table [bob].[dbo].[b1]
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (1,'bob11')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (2,'bob12')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (3,'bob13')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (4,'bob14')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (5,'bob15')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (6,'bob16')
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES (7,'bob17')
GO

truncate table [bob].[dbo].[b2]
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (1,'bob21', 1)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (2,'bob22', 2)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (3,'bob23', 3)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (4,'bob24', 4)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (5,'bob25', 5)
INSERT INTO [bob].[dbo].[b2] ([b2],[b22] ,[b1ref]) VALUES (9,'bob29', 9)
GO

select bob.dbo.b2.b1ref, bob.dbo.b2.b22 from bob.dbo.b2 where bob.dbo.b2.b1ref in (select bob.dbo.b2.b1ref from bob.dbo.b2 except select bob.dbo.b1.b1 from bob.dbo.b1)

/*
INSERT INTO [bob].[dbo].[b1] ([b1],[b12])VALUES
(select bob.dbo.b2.b1ref, 'HelloWorld' from bob.dbo.b2 where bob.dbo.b2.b1ref in (select bob.dbo.b2.b1ref from bob.dbo.b2 except select bob.dbo.b1.b1 from bob.dbo.b1))
*/

I'm a bit of a TSQL n00b, so I apologise for what I assume is a rather basic question.
As you can see from the commented out statement above, I need to insert rows into an existing table that exist in another table, but not the target table - the rows returned in the select statement. The documentation says that SELECT INTO creates a table and that INSERT INTO cannot use SELECT as part of its expression. What would you recommend that I do?

Thank you.

(BTW-how does one insert CODE tags?)

When your master table dies

CalmYourself — Thu, 19 Mar 2009 13:50:20 GMT

... but you have a backup of it, how do you restore the backup if sql server doesn't start because it can't load the master database?

SQL 2000

Error messages in event log at startup has to do with a whole lot of Failed Assertion: seenCkptEnd errors.