Here is another option.

-- Samle working table

CREATE-- DROP

TABLEOrdersMissing

(

OrderNumINTEGERNOT NULL,

MissingValuesINTEGERNOT NULL

)

-- Sample data

INSERT

INTOOrdersMissing

VALUES(1234, 3),

(1235, 23),

(1783, 5)

-- Produce one record for each item listed as missing.

SELECTm.OrderNum,

Tally.N,

'Other needed columns here...'

FROMOrdersMissing m

JOIN(

SELECTROW_NUMBER() OVER (ORDER...

Well, a cursor MAY not be the way to go; it depends on the complexity of the function. If it is a large or complex function, you may see...

A cursor is not the way to go.

UPDATECustomer

SETPercentage = i.Percentage

FROMCustomer u

JOIN(

SELECTCustomerID, dbo.GetPercentage(Value) AS Percentage

FROMInserted

) i

ONi.CustomerId = u.CustomerId

The "i" subquery gets one row per record in the Inserted table, with the...

CREATE-- DROP

TABLEOriginalFact

(

AccountVARCHAR(10)NOT NULL,

OrganizationVARCHAR(10)NOT NULL,

[Year]INTEGERNOT NULL,

PeriodSMALLINTNOT NULL,

AmountINTEGERNOT NULL

)

CREATE-- DROP

TABLEDerivedFact

(

AccountVARCHAR(10)NOT NULL,

OrganizationVARCHAR(10)NOT NULL,

[Year]INTEGERNOT NULL,

PeriodSMALLINTNOT NULL,

AmountINTEGERNOT NULL,

TypeCodeCHAR(1)NOT NULLCHECK(TypeCode IN ('C', 'P'))

)

-- Sample data for the OriginalFact table.

INSERT

INTOOriginalFact

(Account, Organization, [Year], Period, Amount)

SELECTAccount, Organization, [Year], Period,...

It appears that your first line of data has only a single "/":

MYSITE_Lion/EEStaticHeaderIncludes

Searching for the second "/" will return an error because the location of the starting position for the...

Let's make sure I have this right. You have an "OriginalFact" table and you want to populate data into a "DerivedFact" table, using the "prior" month's data if "this"...

Celko: Are you saying we should be able to do the following?

DECLARE@OneVARCHAR(20),

@TwoVARCHAR(20),

@ThreeVARCHAR(20)

SET(@One, @Two, @Three) = ('One', 'Two', 'Three')

SELECT@OneAS One,

@TwoAS Two,

@ThreeAS Three

I get an error when using paretheses after the SET...

Another possible way to accomplish what you need is to use subqueries to get the parts and left join them to the main table:

SELECT e.emp_id, e.emp_name, e.join_date,

...

I enjoyed the look at the various method of data access you compared. Thanks for taking the time to put this together.

I do want to comment on your assessment...

This is a very helpful script. Thank you for posting it.

I am getting several blank lines in the output. It occurs when the "class" column of table "database_permissions"...

The downside to the methods described is that they require a query to be run on every table. The downside to my method below is that it uses the...

@Randall: Understand that this is using the sys.sysindexes view that may not be supported in future versions.

SELECTLEFT(OBJECT_NAME (i.id), 50)AS TableName,

LEFT(ISNULL(i.name,'HEAP'), 50)AS IndexName,

CASE WHEN u.object_id IS NULL THEN 'No Data' ELSE...

It is also helpful to know how much space is used by the index. Here is how I did that:

CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed

EDIT: I should note that...

I use the querying capability built in to Excel to import from SQL Server rather than push things to Excel:

xlApp = GetObject(, "Excel.Application")

xlQueryTable = xlApp.ActiveSheet.QueryTables.Add(Connection:=psConnectString, Destination:=xlApp.ActiveSheet.Range(psCurrentCell))

With xlQueryTable

...

I've tested it using 36014 characters (list of email addresses) and the response time is under 1 second:

-----------------------

2011-05-25 12:46:54.640

2011-05-25 12:46:55.253

For the few times that I will be using this, that...