Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts

Friday, March 23, 2012

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns defined in rowset

Hello Experts,
I have a stored procedure. I used ADO to called this stored procedure. In
runtime, it says "No columns defined in rowset".
Here is my stored procedure:
========================================
========
CREATE PROCEDURE [sp_MigrateAccount]
@.i_vchSessionID VARCHAR(50),
@.o_intRetVal int OUTPUT
AS
-- Variables for error handling.
DECLARE @.intError int
DECLARE @.insAcct_error int
DECLARE @.insAcctSum_error int
DECLARE @.insAddr_error int
DECLARE @.drop_error int
DECLARE @.intRecordErrorCounter int
-- Temporary storage
DECLARE @.chSourceAccountID char(12)
DECLARE @.vchSourceAccount char(128)
DECLARE @.vchSourceMainPhone char(32)
DECLARE @.vchSourceFax char(32)
DECLARE @.chSourceUserfield5 char(80)
DECLARE @.m_cintMigrateSucceed int
DECLARE @.m_cintRecordDataError int
DECLARE @.m_cintInsertToRealTableError int
DECLARE @.m_cintDuplicateKeyError int
SET NOCOUNT ON
-- Assign value to constants
SET @.m_cintMigrateSucceed = 0
SET @.m_cintRecordDataError = 2
SET @.m_cintInsertToRealTableError = 3
SET @.m_cintDuplicateKeyError = 7
-- Perform data validation --
-- Check for duplicate ACCOUNTID
SET @.intRecordErrorCounter = 0
SELECT @.intRecordErrorCounter = COUNT (a1.ACCOUNTID)
FROM sysdba.ACCOUNT AS a1
WHERE a1.ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID)
IF (@.intRecordErrorCounter > 0) -- There exists at least one validation
error.
BEGIN
-- Update ErrorColumn in the source table. --
-- 1 is an arbitrary value for the ErrorColumn.
-- If ErrorColumn is NULL or 0, this record has no error.
UPDATE sysdba.ACCOUNT_TEMP
SET ErrorColumn = @.m_cintDuplicateKeyError
WHERE (SessionID = @.i_vchSessionID)
AND ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT)
-- No need to update the destination table, thus we quit the SP
-- Note that the error number used by SQL Server is greater than 0.
SET @.intError = @.m_cintDuplicateKeyError
GOTO Quit_SP
END
-- Up to this point, the data validation is passed.
-- We can insert record into the destination table. now.
BEGIN TRAN dataMigration
-- Insert records to the ACCOUNT table
-- People from newsgroup said that you are required to
-- enter uppercase Account Name into ACCOUNT_UC field.
INSERT INTO ACCOUNT
(ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC)
SELECT ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC
FROM sysdba.ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID
SET @.insAcct_error = @.@.ERROR
IF (0 = @.insAcct_error) BEGIN
COMMIT TRAN dataMigration
END
ELSE
BEGIN
IF (0 <> @.insAcct_error)
-- Const m_cintInsertToRealTableError = 3
SET @.intError = @.m_cintInsertToRealTableError
ROLLBACK TRAN dataMigration
END
Error_Handler:
Quit_SP:
IF (@.intError IS NULL)
-- No value assigned to @.intError in the
-- body of this stored procedure.
-- Thus, migrate data is successful.
-- Const m_cintMigrateSucceed = 0
SET @.o_intRetVal = @.m_cintMigrateSucceed
ELSE
SET @.o_intRetVal = @.intError -- Failure encountered.
RETURN
GO
========================================
=
Here is my VBScript that used ADO to call the store procedure:
========================================
===
' :
' ADO database connection....
' :
With ADOCmd
.CommandText = "sp_MigrateAccount"
.CommandType = adCmdStoredProc
strInputText = i_strSessionID
lngParamLen = Len(strInputText)
If lngParamLen <= 0 Then
lngParamLen = 1
End If
Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
adParamInput, lngParamLen, strInputText)
.Parameters.Append ADOParam
Set ADOParam = .CreateParameter("@.o_intRetVal", adInteger,
adParamOutput)
.Parameters.Append ADOParam
Set .ActiveConnection = objCon
.Execute
lngRetVal = .Parameters("@.o_intRetVal").Value
End With
' :
' :
' :
'=======================================
==
The ".Execute" statement caused the "No columns defined in rowset".
If I try the following statement:
.Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Or,
.Execute , , adExecuteNoRecords
I cannot get the output value (@.o_intRetVal) from the stored procedure.
May I know how to solve this problem? Thanks a lot.
SammySa wrote:
> Hello Experts,
> I have a stored procedure. I used ADO to called this stored
> procedure. In runtime, it says "No columns defined in rowset".
That does not sound like an ADO error. It sounds more like a T-SQL error.
ADO errors usually refer to recordsets, fields and records. "rowset" makes
it sound
like a T-SQL error.
Have you verified that this procedure runs correctly in Query Analyzer? Like
this:
declare @.ret int
exec sp_MigrateAccount 'some session id',@.ret output
select @.ret [Output Value]

> Here is my stored procedure:
> ========================================
========
> CREATE PROCEDURE [sp_MigrateAccount]
Nothing to do with your error, but using "sp_" for a non-system stored
procedure can cause a slight performance dip, since the query engine treats
all procedures beginning with this prefix as system procedures, looking for
them first in Master, and only looking for them in the current database when
it fails to find them in Master. If you mistakenly name your procedure the
same as a real system procedure, you've got a knotty debugging problem ahead
of you ...

> @.i_vchSessionID VARCHAR(50),
> @.o_intRetVal int OUTPUT
> AS
> -- Variables for error handling.
> DECLARE @.intError int
<snip>
> SET NOCOUNT ON
I doubt this is the problem, but I generally make this the first statement
in my procedures - before any other statement, even DECLARE's.

>
<snip>
> ========================================
=
>
> Here is my VBScript that used ADO to call the store procedure:
> ========================================
===
> ' :
> ' ADO database connection....
> ' :
> With ADOCmd
> .CommandText = "sp_MigrateAccount"
> .CommandType = adCmdStoredProc
> strInputText = i_strSessionID
> lngParamLen = Len(strInputText)
> If lngParamLen <= 0 Then
> lngParamLen = 1
> End If
> Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
> adParamInput, lngParamLen, strInputText)
> .Parameters.Append ADOParam
> Set ADOParam = .CreateParameter("@.o_intRetVal",
> adInteger, adParamOutput)
> .Parameters.Append ADOParam
> Set .ActiveConnection = objCon
> .Execute
> lngRetVal = .Parameters("@.o_intRetVal").Value
> End With
> ' :
> ' :
> ' :
> '=======================================
==
> The ".Execute" statement caused the "No columns defined in rowset".
> If I try the following statement:
> .Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Should be:
.Execute , , (adCmdStoredProc + adExecuteNoRecords)
HTH,
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.sql

No column to map

Hello,

I am using oledb source and running sp_helpdb procedure but when I open the columns windows there are no column available.

your help is appreciated.

Thanks.

got the answer workaround is to use

SET FMTONLY OFF

Wednesday, March 21, 2012

no available input columns

Hi ,

Im trying to build a package that will copy data from excel to SQL

in a program

unfortunately , when I open the package xml file and I drill into the

oledb destination I see that I have no available input columns

what could be the problem ?

thanks ahead

Eran

p.s

the script:

Dim p As Package = New Package()

Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = CType(e, TaskHost)

thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Create excel connection MANAGER

Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")

excelCon.Name = "ExcelSourceConn"

excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""

' Create sqldev connection Manager

Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")

sqlCon.Name = "sqldevConn"

sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"

''create source component

Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

excelSource.Name = "ExcelSource"

excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"

Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()

excelInstance.ProvideComponentProperties()

excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))

excelInstance.SetComponentProperty("AccessMode", 0)

excelInstance.SetComponentProperty("OpenRowset", "business_codes$")

excelCon.AcquireConnection(Nothing)

'excelInstance.ReinitializeMetaData()

excelInstance.ReleaseConnections()

Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

sqldev.Name = "sqldev"

sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"

Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()

sqldevInstance.ProvideComponentProperties()

sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))

sqldevInstance.SetComponentProperty("AccessMode", 0)

sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")

sqldevInstance.AcquireConnections(Nothing)

sqldevInstance.ReinitializeMetaData()

sqldevInstance.ReleaseConnections()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))

MsgBox(excelSource.OutputCollection.Count)

'For Each input As IDTSInput90 In sqldev.InputCollection

' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' ' Call the SetUsageType method of the design time instance of the component.

' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

' Next

'Next

Dim app As Application = New Application()

app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)

Hi,

I found your post and was wondering if you ever got a response to this? I'm having the same problem

Thanks

Cat

|||

When you open the package in the designer do you see any columns published on the source adapter?

Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?

The commented piece of code is supposed to add columns to the input column collection.

Thanks.

no available input columns

Hi ,

Im trying to build a package that will copy data from excel to SQL

in a program

unfortunately , when I open the package xml file and I drill into the

oledb destination I see that I have no available input columns

what could be the problem ?

thanks ahead

Eran

p.s

the script:

Dim p As Package = New Package()

Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = CType(e, TaskHost)

thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Create excel connection MANAGER

Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")

excelCon.Name = "ExcelSourceConn"

excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""

' Create sqldev connection Manager

Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")

sqlCon.Name = "sqldevConn"

sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"

''create source component

Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

excelSource.Name = "ExcelSource"

excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"

Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()

excelInstance.ProvideComponentProperties()

excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))

excelInstance.SetComponentProperty("AccessMode", 0)

excelInstance.SetComponentProperty("OpenRowset", "business_codes$")

excelCon.AcquireConnection(Nothing)

'excelInstance.ReinitializeMetaData()

excelInstance.ReleaseConnections()

Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

sqldev.Name = "sqldev"

sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"

Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()

sqldevInstance.ProvideComponentProperties()

sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))

sqldevInstance.SetComponentProperty("AccessMode", 0)

sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")

sqldevInstance.AcquireConnections(Nothing)

sqldevInstance.ReinitializeMetaData()

sqldevInstance.ReleaseConnections()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))

MsgBox(excelSource.OutputCollection.Count)

'For Each input As IDTSInput90 In sqldev.InputCollection

' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' ' Call the SetUsageType method of the design time instance of the component.

' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

' Next

'Next

Dim app As Application = New Application()

app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)

Hi,

I found your post and was wondering if you ever got a response to this? I'm having the same problem

Thanks

Cat

|||

When you open the package in the designer do you see any columns published on the source adapter?

Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?

The commented piece of code is supposed to add columns to the input column collection.

Thanks.

Monday, March 19, 2012

Nice problem: How to combine different columns into a table

It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.

But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.

select col1 from table1
go
select col2 from table2
go

Now I want to combine them so table3 is made of col1 and col2.Go into more detail as to what you are trying to do - if I understand what you are attempting to do, how do you guarantee the relationship between col1 and col2, so that when an insert into table3 occurs you have the correct col1 and col2 together ?|||Originally posted by rnealejr
Go into more detail as to what you are trying to do - if I understand what you are attempting to do, how do you guarantee the relationship between col1 and col2, so that when an insert into table3 occurs you have the correct col1 and col2 together ?

I basically go through gathering a list of statistical info for all users.
So say I have 10 users. I do one pass of some rather complex queries to get the data for first column. Then I do another pass for another column. Now I want to combine them all into one table.

There is no need to guarantee the relationship between columns, this is merely for reporting purposes so once combined there will never be row insertion to the table ever.

e.g.

User [dataset 1] [dataset 2] [dataset 3]
1 10 20 2
2 23 30 4

Thanks!|||Originally posted by waspfish
I basically go through gathering a list of statistical info for all users.
So say I have 10 users. I do one pass of some rather complex queries to get the data for first column. Then I do another pass for another column. Now I want to combine them all into one table.

There is no need to guarantee the relationship between columns, this is merely for reporting purposes so once combined there will never be row insertion to the table ever.

e.g.

User [dataset 1] [dataset 2] [dataset 3]
1 10 20 2
2 23 30 4

Thanks!

1. Create an @.table with an identity. Fill that table.
2. Create another @.table with an identity. Fill that with the second table.
3. Query both tables and relate them using their identities.|||you mean something like this >>>

select id, (select count(*) from sysobjects), (select max(id) from sysindexes) from syscolumns

Monday, March 12, 2012

NFR with merge in em shows weird seed

I've set identity columns to automatic ranges with merge. The publisher
ranges is for a million and the subscriber side is for a million. Now, when I
go to the EM, and go to the Desgin Table in EM for a table, I see the
identity seed to 1. Is this normal? But when I ran the script and everything
and also when I go to the article properties, it shows the correct identity
ranges. Please let me know. thank you very much...
On the publisher this is correct. On the subscriber it should be at
1,000,001.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:6B8E4284-B565-4D0C-90E5-5948AA21E136@.microsoft.com...
> I've set identity columns to automatic ranges with merge. The publisher
> ranges is for a million and the subscriber side is for a million. Now,
> when I
> go to the EM, and go to the Desgin Table in EM for a table, I see the
> identity seed to 1. Is this normal? But when I ran the script and
> everything
> and also when I go to the article properties, it shows the correct
> identity
> ranges. Please let me know. thank you very much...

Friday, March 9, 2012

Newspaper stykle 2-columns for a Table in RS.

Hi ,
I can't find a possible way of doing this at present
If I have a Table element containing 3 columns, how can I get it to display
the following way? Is it possible? If I get 10 rows, I want 5 rows in each
of the following. If there are say 7 rows, I'd want 4 ros on the left and
3 rows on the right.
Required layout follows....
ColA ColB ColC ColA ColB ColC
How feasible is this or am i expecting too much?
Thx,
PaulI have an example of multi column layout at www.MSBICENTRAL.com It is in the
downloads section... The only issue is that the column setup MUST apply to
the entire body of the report ( this is an RS restriction.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul Buxton" <paul@.nospamspireite.ndo.co.uk> wrote in message
news:1ihujo3tfvqbl$.5hm437dvn3y2$.dlg@.40tude.net...
> Hi ,
> I can't find a possible way of doing this at present
>
> If I have a Table element containing 3 columns, how can I get it to
> display
> the following way? Is it possible? If I get 10 rows, I want 5 rows in
> each
> of the following. If there are say 7 rows, I'd want 4 ros on the left and
> 3 rows on the right.
> Required layout follows....
>
> ColA ColB ColC ColA ColB ColC
>
> How feasible is this or am i expecting too much?
> Thx,
> Paul

Newspaper Columns

Does anyone know how to create Newspaper Columns in Reporting Services? I am
trying to create a company address and phone number directory.
Thanks for any help you can offer.In the Body section of the report, open your properties window. There is a
property under layout called columns. Set the number of columns here.
Then, if you want 2 3" columns w/ 1/2" between them, you would set your
report width to 6.5 and your body width to 3".
msflinx
"GR Pilot" wrote:
> Does anyone know how to create Newspaper Columns in Reporting Services? I am
> trying to create a company address and phone number directory.
> Thanks for any help you can offer.

Saturday, February 25, 2012

Newbie:help on maximum of a series of columns

Hi,
I have the following table:
tblMeasurements
Date | value1| value2| ...|value24|
I need to find (and display) the maximum per day, i.e. the maximum number
among the value1...value24 in the same row (date).
I tried MAX(value1,....,value24) to no avail.
The examples I see on the net seem to find the max for all the entries of a
*specific* column. I want the max of a specific row. Can it be done and am i
looking at the right function ?
I know its a simple question but i cant seem to find the answer.
TIA
-steveSteve
Why not to store like this
CREATE TABLE #Test
(
[Id] Int not null primary key,
[Date] datetime not null,
Value int not null
)
SELECT MAX(Value),Date FROM #Test
GROUP BY Date
"Steve" <try@.this.com> wrote in message
news:xOz0e.19063$ZC6.78184@.wagner.videotron.net...
> Hi,
> I have the following table:
> tblMeasurements
> Date | value1| value2| ...|value24|
> I need to find (and display) the maximum per day, i.e. the maximum number
> among the value1...value24 in the same row (date).
> I tried MAX(value1,....,value24) to no avail.
> The examples I see on the net seem to find the max for all the entries of
a
> *specific* column. I want the max of a specific row. Can it be done and am
i
> looking at the right function ?
> I know its a simple question but i cant seem to find the answer.
> TIA
> -steve
>|||Try,
select
[Date]
max(value)
from
(
select
[date],
case n.colA
when 1 then value1
when 2 then value2
..
when 24 then value24
end as value
from
t
cross join
(
select 1
union all
select 2
union all
..
select 24
) as n(colA)
) as a
group by
[date]
go
AMB
"Steve" wrote:

> Hi,
> I have the following table:
> tblMeasurements
> Date | value1| value2| ...|value24|
> I need to find (and display) the maximum per day, i.e. the maximum number
> among the value1...value24 in the same row (date).
> I tried MAX(value1,....,value24) to no avail.
> The examples I see on the net seem to find the max for all the entries of
a
> *specific* column. I want the max of a specific row. Can it be done and am
i
> looking at the right function ?
> I know its a simple question but i cant seem to find the answer.
> TIA
> -steve
>
>

Newbie: sql 2005 encryption or where do I put that key?

I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?

I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.

What are your ideas on this matter?

TIA,

barkingdog

Have a look at the encryption hierarchy in SQL Server: http://msdn2.microsoft.com/en-US/library/ms189586.aspx. Also check the other resources mentioned here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

If you store the key in the database, it is stored encrypted. I am not sure why would you think that this scheme offers "little security", can you elaborate on that statement? Before discussing a protection scheme, it would be helpful to state what you are trying to protect against - against what attacks do you want to protect the key?

Thanks
Laurentiu

|||

Laurentiu,

From what you said about storing the key in the database I obviously have a misconception here. But how then, is one supposed to access the encrypted key to de-crypt the data for later display in the UI? Is there a "proxy" stand-in for the real key once it is encrypted?

My concern is to prevent outsiders from, if they somehow gained access to the database (say a stolen or lost backup tape), from being able to decode sensitive fields such as Social Security number. At the same time, when a SSN is entered via the UI the application needs the key to drive the encryption of sensitive fields.

Barkingdog

|||

Basically, there are two ways to encrypt keys (hence two ways to decrypt them): one is to eventually use a password, so the password needs to be specified when the key needs to be used; the second protection is based on DPAPI, so no password needs to be specified. DPAPI basically uses the credentials of the machine and of the service account to protect the key, so to break it, one would have to know those credentials.

For details on DPAPI, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptprotectdata.asp. How DPAPI ties in to the key protection scheme is shown in the key hierarchy diagram from the first link in my previous message - you have a chain of encryptions rooted at the DPAPI encryption of the service master key.

For additional information and examples, see the blogs I referred to you earlier. The example from http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx has the symmetric keys protected so that no password is required for their use (access control for keys is done through permissions). The post from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx goes over additional considerations related to the use of encryption keys. For a discussion of the protection conferred by encryption, see http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx.

For the following explanation, I will assume that you have examined the key hierarchy diagram and that you understand the encryption chain. I will also use SMK and DbMK as shortcuts for the service master key and, respectively, database master key.

In the case of a stolen backup tape that contained a database with encrypted data, the thief cannot decrypt the data without knowledge of the password that protects the DbMK. So the data is secure because removing a database from the server will cut the database from the encryption chain that allows its data to be decrypted.

If you're worried about a stolen laptop scenario, this threat is a little different from the previous one because the thief might be able to figure out how to gain full control of the laptop so that he can connect to the server as usual and decrypt the data. For this scenario you would want to protect the encryption keys by password, and then have the password specified when you're accessing the data (think of it as an additional login operation that grants access to the encrypted data). This would protect against a lost laptop scenario because even with full access to the laptop, knowledge of the password is still required.

Thanks
Laurentiu

|||

Hi,

I have a different type of problem. Lets say I have created a Symetric key (without using a password) and i authorize that key to a user called ASP_NET_My_Appln.

This user is used by my UI(ASP.NET) for querying the DB.

All the programmers who are coding the project WILL know the key name that we are using for encrypting / decrypting the data.

Therefore any programmer who can log into the production server or for that matter the local environment (where we place production server dumps to get the latest data) can decrypt the data by passing a simple SQL like this:

select EncryptXXX(Key,Column) from table

To protect the same I had to resort to use a password phrase. But however there is a problem there too. All the SQLs that we use are stored in SPs. Therefore a sample SP would be:

Sp_GetData @.pwd varchar(10)
AS
OPEN SYMMETRIC...... Password=@.pwd
select EncryptXXX(Key,Column) from table
Close Symmetric...

Anybody who is running the profiler can now read the password as the profiler does not block the same.

What is the best way to overcome this?

Therefore categorising the problems:
1. As far as I see the decyprtion seems to be a very simple select statement. Therefore anyone who has access to the server and knows the correct Key name and table name etc can do the same (Which a programmer WILL know).
2. Profiler is capable of blocking the actual select statements that use encryption commands but NOT the SP that takes the password. How can I overcome that? Should I change my design? Once again the password cannot be hardcoded into an SP as any developer can open it and look into it.

Kindly correct me if I have misqouted anything.

|||

1. To decrypt, you need to have previously opened the key. The access checks on the key and the knowledge of the passwords used to access the key come into place at this time, and it is these checks that restrict the access and use of an encryption key.

2. You're right about not wanting to hardcode the password in a SP. You should treat the key password as a login password and issue a direct OPEN SYMMETRIC KEY statement whenever you want to use the key - the password passed to OPEN will not be traced.

However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Thanks
Laurentiu

|||>>However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).

So the programmers themselves MIGHT be hackers...

Since I have to give access to my UI user, anybody who gets hold on the connection string can open a connection to the DB and remove the data by a very simple select stmt. So to protect this I wanted to have passwords for the key. But now i am stuck as to how to protect the password from hackers...|||

As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?

Thanks
Laurentiu

|||Hi,

When you mean "As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?"

Are you talking about the UI? If yes then the problem arises when I have to pass it to the DB (to an SP in the DB).

Where and how exactly do you want me to store the password that protects the key?|||

I am suggesting to have the user specify the password. I am not suggesting for the password to be stored somewhere where it can be programmatically retrieved, given that you are trying to prevent the developers of your application from accessing it. Also, I am not suggesting for the password to be passed around as an argument to stored procedures (which would make it visible in a trace) - it should just be passed to the OPEN SYMMETRIC KEY statement.

Thanks
Laurentiu

|||>>it should just be passed to the OPEN SYMMETRIC KEY statement

Exactly, but my open symmetric statement is inside an SP. There can be more than 100 SPs that have to access this password. In this case there are 2 options for me:

1. Hardcode the password in each SP.
2. Pass it as a parameter to the SP.

I choose the second one therefore the problem.|||

Why do you open the key inside the SP? Why can't you open it as part of the logon process for your application and keep it open for as long as you work with the encrypted data. Once you open a key, it is only available within the current session, so you don't have to worry about other users getting to it. Also, you don't need to open it and close it for each access to encrypted data. You can open it once, use it for many encryptions and decryptions (which can happen in stored procedures that you call - they will have access to the opened key), and then close it when you are done (or you can just disconnect your session and it will be destroyed).

Thanks
Laurentiu

|||Hi,

Before I implement your idea i would like to know more about the defenition of a current session.

I am using EntLib, therefore each call to an SP opens / closes a connection from the pool. How is the session defined in this case?

One more thing, to hide the data from the profiler i will have to pass the OPEN stmt as a direct SQL rather than using an SP right?|||

Hi,

You can use CLR function to open symetric key , The script will be in assembly

and the password is hidden at all.

Thanks,

Tarek Ghazali

SQL Server MVP

web site : www.sqlmvp.com

|||

Hi,

I am totally new to this. Could you possibly giude me to some tutorials on the same?