Problems With Decrypting Columns

Mar 31, 2006

Hi!

I want to encrypt a whole column in my table and I do this with this SQL code:

OPEN symmetric key Sym_Key DECRYPTION BY certificate My_Cert
GO

UPDATE [My_demo].[dbo].[My-DemoList]
SET [Test_crypt] = encryptByKey(Key_GUID('Sym_Key'),[Test])
GO

CLOSE all symmetric keys

GO

And this seems ok but when I want to decrypt it with the view I have created it seems that I get a "rubbish" character between each "real" character.

So my questions is: What am I doing wrong?

Because if I do an insert like this

OPEN symmetric key Sym_Key DECRYPTION BY certificate My_Cert
GO

INSERT INTO [My-DemoList] (Test_crypt) VALUES(encryptByKey(Key_GUID('Sym_Key'),'1234567'))

GO

CLOSE all symmetric keys

And then use my view to look at the decrypted value that I put in its ok.

Many thanks in advance!

View 4 Replies


ADVERTISEMENT

DecryptByPassPhrase Not Decrypting Varchar Columns After Copying A Database

Jan 18, 2007

I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:

DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT

SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.

View 5 Replies View Related

Decrypting Data

Jun 14, 2006

Hi there,

I am having a table in Sql which has 2 columns which has data in encrypted format. It shows data in junk format (ascii format). But in frontend (tht is in software)it shows data in proper format. Can any one help me in decrypting data.

Thanks,

Regards,


mystical

View 16 Replies View Related

Decrypting The Password.....

Sep 8, 2007

I have a SQL Server Login and I forgot the password for it.
Is there any way I can decrypt the password. I don't want to
change the password.

Thanks
Venu

View 8 Replies View Related

Encrypting And Decrypting Data

Dec 22, 2006

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

View 15 Replies View Related

Decrypting Returns A NULL Value

Apr 26, 2007













I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:

Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value


Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'

-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go

-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO

-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';

-- I then verified if the key is opened
SELECT * FROM sys.openkeys




If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!




Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.

Thanks in advance!



faiga16



3 Posts

View 9 Replies View Related

Decrypting Column In Transformation

Oct 2, 2007

I have a OLE DB Source that has a varbinary column of encrypted data. The sorce table is on a hosted SQL Server database where I cannot install a asymetric key. The SSIS package is running on a local SQL Server box that does have the asymetric key installed and working. Can you recommend the best way to transform this column is SSIS using a connection to the local SQL Server box that has the installed public key?

select @encryptedstuff = 0xA19B9F77E5319283311F325D6D29265721A8451148DCD33FA37DF34737C29690BEE35F99972AD7D40F31E8EFE81A30A9B830ABCD1B6BE386462071D67198CE6E52E15FAD84CA62AA35F847948F40B3F8CF4F50D5F2A14D0CCD9FF990F3C1701784F0A8771B93D329144528455937511EF2691BB42A0D4505AC8F9296BF6700801ECE05B102F0CC6DAF204F4EA4C8317AAEDDEC7D83BCD78BA1718C9E55C840AEA280D8BC9CC58D8E05AAE0AE5AC4B7DA0CE7D5DF1DDCAEEA1FB7431ACDF20BBDB2F29ECD744FDEE3D688920E56BEF5508D8224D0DE6AAE8FF944E389D376138885FC4300AFD281C8CC677CDA1762B56D8D1363C7878EAA7A65FC10B8AE168E75

SELECT CONVERT(nvarchar(50),DecryptByAsymKey(AsymKey_ID('rsakey'), REVERSE(@encryptedstuff), N'P4ssw0rd'))



Each row of the OLE DB Source contains a varbinary column with the ecrypted data. I need to to transform that one column and end up with a new record set that contains all of the columns from the OLE DB Source plus a new decrypted column.

Thanks,

Steve

View 4 Replies View Related

Decrypting Provided Data

May 2, 2007

I have following problem. I would like to provide to my STP encrypted data and decrypt them inside it. To decrypt the data I'd like to use DecryptByKey. Encryption should be made on the Win32/.NET client.



Unfortunately I cannot find the way to make the data understable between the both worlds. First how to assign the same key on the both side? Do I get the same key from




Code Snippet

create symmetric key MyKey with

algorithm=triple_des,

key_source='abrakadabra'

encryption by password='aaa'



and from




Code Snippet

string Key = "abrakadabra";

byte[] bKey = Encoding.ASCII.GetBytes(Key);

byte[] salt = new byte[8];



RNGCryptoServiceProvider rnd = new RNGCryptoServiceProvider();

rnd.GetBytes(salt);

PasswordDeriveBytes pdb = new PasswordDeriveBytes(bKey, salt);



TripleDESCryptoServiceProvider prov = new TripleDESCryptoServiceProvider();

prov.GenerateIV();

prov.Key = pdb.CryptDeriveKey("TripleDES", "SHA1", 168, prov.IV);

???

I have read somewhere that create symmetric key calls CryptDeriveKey, but I'm not sure.



The next point is the format of the output data. I have noted, that the output from EncryptByKey is 16 bytes longer (after stripping key guid and fixed 0x01000000) than the one from Win32/.NET application. I assume, that it can lie in the inserting of IV as the first block, but should not IV be only 64 bit long in the DES family of algorithms?



Neverthless I have not magaged to perform encrypted communication such way between SQL Server 2005 and client application. Is it possible at all?







View 1 Replies View Related

Decrypting Encrypted Views/Sp/Functions.....?

Sep 19, 2006

Hi all,As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functionsin Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.Thanks in advance.Best Regards.

View 2 Replies View Related

Encryping - Decrypting Stored Procedures

Oct 19, 2004

Hi all...

Im wondering how do you encrypt and decrypt a stored procedure within sql server 2000 ?

i want to be able to encrypt data been inserted / updated using triple des algorithm and then on select / read - decrypt it.

Does anyone know a solution / stored procedure on how to do this? any ideas.

Thanks

Paul..

Im also wondering if there is a away of doing this on the code side without using stored procedures (asp.net c#) i have been able to encrypt and decrpt to a string but unable to do it through the datagrid object (dataset) or a datareader..

Any tips / help would be helpful

Thanks..

View 2 Replies View Related

Decrypting A Table Encrypted With EncryptByKey?

Jan 18, 2012

i have a ms sql base which contains tables encrypted with EncryptByKey, who knows how to make me a script do save the encrypted tables to clear text pm me in ym : hgfrfv or msn : [URL], i have all the keys used on encryption and all those stuff.

View 1 Replies View Related

Decrypting WITH Encryption User Functions...

Jul 20, 2005

....it's possible without any third party application?I need to recover some encrypted user functions but the sources have beenlost long time ago, someone can help me?--Lav.

View 2 Replies View Related

Decrypting And Encrypted Stored Procedure

Sep 6, 2007

Hello,
In SQL 2000,
I have created a Stored Procedure as follows,




Code Snippet

CREATE PROCEDURE MyTest
WITH RECOMPILE, ENCRYPTION
AS
Select * From Customer
Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.




Code Snippet

sp_helptext mytest
But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error,
So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also,
or not,
If yes then wht it is and if NO then wht will be the alternative way for this,
?????

View 2 Replies View Related

SQL 2012 :: Decrypting Encrypted Fields From Another Database

May 5, 2015

I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:

OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION
BY CERTIFICATE [XXXX_CERT];
select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> ))
FROM Database2.dbo.TABLE1
CLOSE SYMMETRIC KEY [XXXXKey];

What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?

View 5 Replies View Related

Decrypting Password Of SysxLogins Table - SQL Database.

Dec 15, 2005

Hi,

    Is there any way of decrypting password value stored in sysxlogins table of SQL database?

Thx in Adv

 

 

View 4 Replies View Related

DECRYPTING A Column Permanently With Table UPDATE

Mar 17, 2008

In SQL 2005, I've created a test scenario in AdventureWorks where I:

1.) Create a database master key:

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'

GO

2.) Create a certificate:

CREATE CERTIFICATE HRCert
WITH SUBJECT = 'Comments'
GO

3.) Create a symmetric key:

CREATE SYMMETRIC KEY CommentKey
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HRCert
GO

4.) Add a test column to the HumanResources.JobCandidate table:

ALTER TABLE HumanResources.JobCandidate
ADD Comments varbinary(8000)
GO

5.) Open the symmetric key for use:

OPEN SYMMETRIC KEY CommentKey
DECRYPTION BY CERTIFICATE HRCert
GO

6.) Insert and Encrypt values ('Yes') to the newly created column:

UPDATE HumanResources.JobCandidate
SET Comments = EncryptByKey(Key_GUID('CommentKey'), 'Yes')
GO

-------


Great. Now all the textbooks say that, to view the column values in a decrypted state, you should:

SELECT CONVERT(varchar, DecryptByKey(Comments)) AS [Decrypted Comments], *
FROM HumanResources.JobCandidate


------

Great, I get it. But here's the rub. What is the best way to permanently decrypt the column and keep it in a cleartext state?

Running the following works, but keeps the column values in a varbinary (hexadecimal) state which is what we originally created:

UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO

But if I want to transform this varbinary(8000) column into a human-readable varchar column, the only thing I could come up with was a temp table solution:

UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO

DECLARE @temp varchar(1000)
SET @temp = (SELECT TOP 1 CommentsFROM Human Resources.JobCandidate)

CREATE TABLE #decrypt (Comment varchar(1000))
INSERT INTO #decrypt (Comments) VALUES (@temp)
GO

ALTER TABLE HumanResources.JobCandidate
ALTER COLUMN Comments varchar(1000)
GO

UPDATE HumanResources.JobCandidate
SET Comments = (SELECT Comments FROM #decrypt)
GO

DROP TABLE #decrypt
GO

--------

It works, but seems so inelegant to me.

Is there an easier way?

View 5 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View 1 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

Hiding/Showing Columns Based On The Columns Present In The Dataset

Jun 27, 2007

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that??

Any reply showing me the right way is appricited.



-Thanks,

Digs

View 3 Replies View Related

How Dose It Matter For The Non-clustered Index Key Columns And Included Columns?

Apr 24, 2007

Hi, all experts here,

Thanks a lot for your kind attention.

As I am creating the non-clustered indexes for the tables, I dont quite understand how dose it really matter to put the columns in the index key columns or put them into the included columns of the index?

I am really confused about that and I am looking forward to hearing from you and thank you very much again for your advices and help.

With best regards,

Yours sincerely,

View 4 Replies View Related

A Word About Meta-data, Pass Through Columns And Derived Columns

Oct 13, 2006

Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):

Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.

This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.

Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.

Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.

Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS

View 5 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

Matching A View's Columns To It's Underlying Table's Columns

Jul 20, 2005

Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name

View 2 Replies View Related

SELECT Query - Different Columns/Number Of Columns In Condition

Sep 10, 2007

I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View 1 Replies View Related

Identity Columns And Date Columns On Transactional Replication

Sep 16, 2006

Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

View 1 Replies View Related

SQL Server 2014 :: Get SUM Of The Sum Of Three Columns And All Three Columns Have Nulls?

Jul 13, 2015

Basically I need to get the SUM of the sum of three columns and all three columns have nulls. To make it more complicated, the result set must return the top 20 in order desc as well.

I keep facing different issues whether I try and use Coalesce, IsNull, Sum, count, anything. My query never returns anything but 0 or NULL regardless of if I am trying to build a CTE or just use a query.

So I'm using Col A to get the TOP 20 in order (which is fine) but also trying to add together the sums of Col A + Col B + Col C for each of the twenty rows...

View 2 Replies View Related

Calculated Columns Based On Multiple Columns?

Aug 12, 2014

MS SQL 2008 R2

I have the following effectively random numbers in a table:

n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i

I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.

if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1

So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.

declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers

View 5 Replies View Related

How To Select Columns When Columns Change

Aug 13, 2007

I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL.

I have four tables. Product Line, Item, Property, and Value.

A Product Line has many items and an item can have many property's and a property can have many values.

I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch.

Any help would be appreciated.

Thanks,
vmon

View 2 Replies View Related

Identity Columns And XML Columns - OK With Mirroring ?

Feb 13, 2006

Just to confirm, do identity columns and XML columns work OK with database mirroring ? That is, all data types are supported with mirroring, and identities aren't an issue ?

Transactional replication with identity columns was a right pain in the **** in SQL 2000. I'm assuming that mirroring doesn't have these issues, but want to be sure.

View 1 Replies View Related

Looping Through Excel Columns (256 Columns)

Sep 19, 2007

Hello All,

I have a problem and i wish i can get the answers or advices to solve it.

i have like 20 excel files and in each file there is 1 sheet (Planning) . What i need to do is to loop on the on the 20 files (actually this is the easy part and i already done it) the hard part is while looping i need to open each excel file and loop on the 256 columns in it and extract the data from it to a SQL server Database.


Any help will be alot appreciated.

View 7 Replies View Related

External Columns Vs. Output Columns?

May 23, 2006

Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?

TIA,



Barkingdong

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved