Prefix On Encrypted Columns
Apr 26, 2007
I read a previous post that Raul responded to on the format of the data prefixing Symmetric key encrypted columns, is it possible to reproduce these from a client? Given I know the key name, i can pull back the first 16 for the GUID, for now 01000000 will work for the version, the IV can be created or read, but the last 8 bytes were marked as 'header', can I get an explanation on what this is or if it is required?
The purpose is in being able to do SQL Server compatible encryption on the client side, given a shared certificate for the public password. I certainly can do this with a CLR function and use my own encryption, but comparably it is dog slow, the built in SQL functions will encrypt/decrypt 100k rows in about 20 seconds or less on my test box, where as the CLR function takes 5.5 minutes. This performance difference is too huge to ignore.
I would like to be able to generate a SQL Server compatible prefix for a Symmetric keyed column or find a way to improve the CLR function performance of an AES_256 (rijndael) up to something at least remotely close to the built in functions.
Any help would be appreciated.
John Gooding
View 1 Replies
ADVERTISEMENT
Jan 23, 2006
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key
encryption by password = 'testAppleA3';
create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test
drop certificate test
drop master key
Then recreate the master key:
create master key
encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
again but this does not resolve the issue.
Thx.
View 6 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
Nov 6, 2014
I have idea on SMK, DMK and symmetric and asymmetric keys. I have also idea on TDE. But Is there any way to encrypt all the records of all the columns of a table in a database? actually I need to encrypt the database. Someone .... thinks that when someone will write select query he will get the encrypted records. As per as I am concerned it is not possible. I can encrypt the specific column using symmetric or other keys...
Is there any software or any tool which will provide encrypted records of database?
View 1 Replies
View Related
Jul 20, 2005
I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s
View 10 Replies
View Related
Dec 22, 2005
Hi,
For those implementing encrypted columns, what is the recommended approach when allowing users to also do partial searches on encrypted data? (ie email or creditcard info where the tables contain millions of rows). I understand one cannot have the encryption without performance impact, but the searches can be 10 to 20 times as long as when the info is stored in normal char(20) columns. Just looking as a way to try and lessen the impact.
Thanks
View 1 Replies
View Related
Jun 29, 2015
I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.
USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]
I tried doing above still no luck.
View 3 Replies
View Related
Apr 30, 2008
Am I right in saying that the MS Full-text search engine does not support a wildcard at the beginning of the word? Only at the end? E.g. "VB*" works but "*NET" doesn't.
View 2 Replies
View Related
Oct 13, 2004
Hi, I have created a table with the following
docmd.runSQL "Create Table #Test(A int)"
in Access 2000 and cannot find the table on my table list, it's not on my enterprise manager either. My guess is that maybe it has something to do with the prefix #? So I started searching for "Create table #" on google and dbforums and cannot find anything. Can someone tell me what's special about having a #prefix on a table and where can I find more information about it?
Thanks
SHK
View 2 Replies
View Related
Jun 14, 2007
Good Morning Db forum.
I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.
Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?
View 3 Replies
View Related
Oct 11, 2007
What does the ap_ stand for as a prefix to stored procedures?
A DBA tried to teach me something about the prefixes etc.. but I can't recall what it meant.
In this case ap_ may represent "ONE" persons naming convention?
something procedure...
other common ones in our database are dt_ and zz_
View 2 Replies
View Related
Oct 2, 2006
SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
Any ideas?
Thanks,
Tom
btw, sql newbie.
View 5 Replies
View Related
Aug 29, 2007
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?
View 2 Replies
View Related
Apr 12, 2006
Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
View 2 Replies
View Related
Mar 21, 2007
Hi,I would like to know details about the table name starts with prefixin sql server 2000.Actually i'm working on existing code.The existing code insert a record into a table, but the table name inthe code and table name in database are differentTable name in database : tbl_mmm_oxIn coding they are using table name as mmm only, the records areproperly inserted into mmm tableis it possible?The sample code is like that(using ado object)oCmd.CommandText = "INSERT INTO mmm (no,name) values (1,"mm")"The above code is perfectly working and inserting record intotbl_mmm_ox.Could anybody explain how is it possible?whether we can leave the prefix(tbl) and suffix(ox) and sql servertake care of this?Thanks & Regards,Mani
View 7 Replies
View Related
Mar 5, 2008
Hi forum
I need some advice regards setting a serial prefix to PK ID colomn. Currently it starts at row 1 with increased increments of 1. Thats all good! but I would like is to add a prefix before the 1, ie W2W01/1 next W2W01/2 an so on. Many thanks Paul
View 1 Replies
View Related
Aug 18, 2006
I get the error below from the following SQL. This SQL worked until I tried adding a third table "ship_to_salesrep". If I comment-out the third join and the last condition it works.
I have tried putting the "r" table in the FROM statement and still it does not work. Everything looks right to me -- what am I doing wrong?
Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'h' does not match with a table name or alias name used in the query.
SELECT distinct
m.inv_mast_uid,
CONVERT(DECIMAL(10,4),0.00) as 'inv_cost',
CONVERT(DECIMAL(10,4),0.00) as 'oe_cost',
l.invoice_line_type, s.cost,
h.invoice_no, h.order_no, h.order_date, h.invoice_date, h.customer_id,
h.ship_to_id, h.ship2_name, h.ship2_address2, h.ship2_city, h.ship2_state, h.ship2_postal_code,
h.terms_desc, h.po_no, h.salesrep_id, h.salesrep_name, h.period, h.year_for_period,
h.ship_date, h.total_amount, h.amount_paid, h.terms_taken, h.allowed, h.paid_in_full_flag,
h.last_maintained_by, h.printed, h.printed_date, h.shipping_cost, h.invoice_reference_no,
h.invoice_adjustment_type, h.memo_amount, h.bad_debt_amount, h.invoice_class, h.period_fully_paid,
h.year_fully_paid, h.approved, h.other_charge_amount, h.tax_amount, h.original_document_type,
h.date_paid, h.print_flag, h.print_date, h.customer_id_number, h.date_created, h.date_last_modified,
h.consolidated, h.sold_to_ah_uid, h.sold_to_customer_id, h.invoice_batch_uid, h.sales_location_id,
h.source_type_cd,
l.qty_requested, l.qty_shipped, l.unit_of_measure, l.item_id, l.item_desc, l.unit_price,
l.extended_price, l.gl_revenue_account_no, l.gl_salse_tax_account_no, l.pricing_quantity, l.line_no,
l.sales_cost, l.commission_cost, l.other_cost, l.other_charge_item, l.exceptional_sales, l.pricing_unit,
l.invoice_line_uid, l.invoice_line_uid_parent
into jch1.dbo.sales_history_invoices
FROM
invoice_hdr h, invoice_line l
left join inv_mast m on l.item_id = m.item_id
left join inventory_supplier s on m.inv_mast_uid = s.inv_mast_uid
left join ship_to_salesrep r on h.ship_to_id = r.ship_to_id
WHERE
l.invoice_no = h.invoice_no and
h.invoice_date >= '2006-07-01' and
h.invoice_date < '2006-08-01' and
l.invoice_line_type = 0 and
m.inv_mast_uid is not NULL and
r.primary_salesrep_flag = 'Y';
Thanks,
Charles
View 3 Replies
View Related
Aug 23, 2004
hey all,
I've posted about this before so apologies to anyone who read it the first time.
I want to have an ID column in my table, but I want the automatically generated number to have a prefix such as ABC. I'd also like to be able to change this prefix from an asp page depending on what is selected from a drop down.
Is any of this possible? I'd really appreciate a reply, even if it's to say this can't be done. Thanks
View 2 Replies
View Related
Apr 11, 2014
I have one table called tblCustomer.I have done a SQL select query
Select SalesDate, OrderNo
From tblCustomer
Table 1 is result of SQL
OrderID is Primary Key
There are two fields I want to convert to number starting with prefix C on each row then followed by combining SalesDate + OrderNo
SalesDate OrderNo
01/1/2014 0678
12/1/2014 9099
15/1/2014 8745
I need to covert this two field into one field which I want it to start with Prefix C AS SalesOrdeNumber column.Find below example what I want after completing this exexercse.Table 2 third column (SalesOrdeNumber) is what I need as result of Concatenation (SalesDate+ OrderNo +C)
SalesDate OrderNoSalesOrdeNumber
2014/01/20 0678 C 1401200678
2013/09/259099 C 1309259099
2013/04/308745 C 1304308745
SalesOrderNumber is made by combining SalesDate +OrderNo Fields (yymmdd+OrderNo+C (as prefix on every row))
Point to Note
Date Format on SalesDate Field YYYY-MM-DD
OrderNo field is maximum digit is 4
View 4 Replies
View Related
May 31, 2015
Am just on the lookout to find out whether there is a way to put a prefix at the beginning of the transaction log backup file that is created by SQL Server logshipping.
So, instead of having <DatabaseName>_<DateTime>.BAK (which is what SQL does), I would like to have TL_<DatabaseName>_<DateTime>.BAK.
I have looked into a parameter for the sqllogship.exe command, but cannot find any & have looked at fields in the logshipping tables, & can't see anything there either.
View 0 Replies
View Related
Jun 5, 2008
I'm just getting into creating charts and one I'm working on could realy do with a prefix on its axis label, the symbol '£'.
I honestly have no idea how to do this and what syntax to use, can anyone help ?.
Also as the label could do with having a thousands seperator as well but as you can tell I'd have no idea how to do this either.
Thanks.
View 1 Replies
View Related
Apr 18, 2006
Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.
Please email me at dickson.lau@rogers.com
Thanks again.
Dickson
View 15 Replies
View Related
Aug 10, 2007
column prefix 'a' does not match with a table name. Any ideas?
This happens when i run the DTS package.
SELECT'Dublin' + SPACE(1) +
CONVERT(VARCHAR(10), RTRIM(FS6.QB@SCS)) + REPLICATE(SPACE(1), 10 - LEN(CONVERT(VARCHAR(10),RTRIM(FS6.QB@SCS)))) +
CONVERT(VARCHAR(10),a.IHIVDT,20) +
CASE
WHEN RM.RMRSSC IN ('01', '03', '04', '05', '14', '28', '34', '35', '36') AND RS.RSRSCL = 'FG'
THEN 'CLOSURE' + SPACE(3) + 'TAPE' + SPACE(21)
WHEN RM.RMRSSC IN ('02') AND RS.RSRSCL = 'FG' THEN 'CLOSURE' + SPACE(3) + 'ADHESIVE' + SPACE(17)
WHEN RM.RMRSSC IN ('06', '07') AND RS.RSRSCL = 'FG' THEN 'UNITIZING' + SPACE(1) + 'STRETCH' + SPACE(18)
WHEN RM.RMRSSC IN ('08') AND RS.RSRSCL = 'FG' THEN 'DISPLAY' + SPACE(3) + 'SHRINK' + SPACE(19)
WHEN RM.RMRSSC IN ('09', '10', '15', '39', '40') AND RS.RSRSCL = 'FG' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25)
WHEN RM.RMRSSC IN ('19', '22', '29', '30') AND RS.RSRSCL = 'FG' THEN 'SPECIALTY' + SPACE(1) + SPACE(25)
WHEN RM.RMRSSC IN ('37') AND RS.RSRSCL = 'FG' THEN 'CUSHIONING' + 'MAILERS' + SPACE(18)
WHEN RM.RMRSSC IN ('38') AND RS.RSRSCL = 'FG' THEN 'CUSHIONING' + 'BUBBLE' + SPACE(19)
WHEN RM.RMRSSC IN ('11') AND RS.RSRSCL = 'RM' THEN 'SPECIALTY' + SPACE(1) + SPACE(25)
WHEN RM.RMRSSC IN ('14', '17') AND RS.RSRSCL = 'RM' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25)
WHEN RM.RMRSSC IN ('12', '13', '15', '16', '51') AND RS.RSRSCL = 'RM' THEN 'SPARE PART' + SPACE(25)
WHEN RM.RMRSSC IN ('08', '09') AND RS.RSRSCL = 'WP' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25)
ELSE SPACE(35)
END +
CONVERT(VARCHAR(50), RTRIM(RM.RMDESC)) + REPLICATE(SPACE(1), 50 - LEN(CONVERT(VARCHAR(50), RTRIM(RM.RMDESC)))) +
CASE
WHEN RM.RMRSSC IN ('06','07') AND RS.RSRSCL = 'FG' THEN
CASE
WHEN RM.RMPRCL IN ('134', '135', '137', '159', '1073') THEN 'STRETCH-MACHINE' + SPACE(10)
ELSE 'EXCLUDED' + SPACE(17)
END
WHEN RM.RMRSSC IN ('01') AND RS.RSRSCL = 'FG' THEN
CASE
WHEN RM.RMPRCL IN ('173', '175') THEN 'HOT MELT HAND ROLL' + SPACE(7)
WHEN RM.RMPRCL IN ('174', '174L') THEN 'CARTON SEALING' + SPACE(11)
ELSE 'EXCLUDED' + SPACE(17)
END
WHEN RM.RMRSSC IN ('05', '28') AND RS.RSRSCL = 'FG' THEN 'CARTON SEALING' + SPACE(11)
WHEN RM.RMRSSC IN ('03', '04', '14', '34', '35') AND RS.RSRSCL = 'FG' THEN 'INDUSTRIAL TAPES' + SPACE(9)
ELSE 'EXCLUDED' + SPACE(17)
END +
CASE
WHEN RM.RMRSSC IN ('06','07') AND RS.RSRSCL = 'FG' THEN
CASE
WHEN RM.RMPRCL IN ('134', '135', '137', '159', '1073') THEN 'STRETCH-MACHINE' + SPACE(10)
ELSE 'EXCLUDED' + SPACE(17)
END
WHEN RM.RMRSSC IN ('01') AND RS.RSRSCL = 'FG' THEN
CASE
WHEN RM.RMPRCL IN ('173', '175') THEN 'HOT MELT HAND ROLL' + SPACE(7)
WHEN RM.RMPRCL IN ('174', '174L') THEN 'HOT MELT MACHINE ROLL' + SPACE(4)
ELSE 'EXCLUDED' + SPACE(17)
END
WHEN RM.RMRSSC IN ('03', '04', '14', '34', '35') AND RS.RSRSCL = 'FG' THEN 'INDUSTRIAL TAPES' + SPACE(9)
WHEN RM.RMRSSC IN ('05') AND RS.RSRSCL = 'FG' THEN 'WATER ACTIVATED' + SPACE(10)
WHEN RM.RMRSSC IN ('28') AND RS.RSRSCL = 'FG' THEN 'NATURAL RUBBER' + SPACE(11)
ELSE 'EXCLUDED' + SPACE(17)
END +
CASE
WHEN RM.RMRSSC IN ('06','07') AND RM.RMPRCL IN ('134', '135', '137', '159', '1073') AND RS.RSRSCL = 'FG' THEN
CASE
WHEN a.IHCRMM = 0 THEN
CASE
WHEN (IV.IVQYSD * RM.RMNETW)IS NULL THEN '000000000000000'
WHEN (IV.IVQYSD * RM.RMNETW) > 0 THEN '+' + REPLICATE('0', 23 - LEN(IV.IVQYSD * RM.RMNETW))
+ SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), 1, LEN(IV.IVQYSD * RM.RMNETW) - 13)
+ SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), LEN(IV.IVQYSD * RM.RMNETW) - 11, 4)
WHEN (IV.IVQYSD * RM.RMNETW) = 0 THEN '000000000000000'
END
WHEN a.IHCRMM IN (1,3) THEN '000000000000000'
WHEN a.IHCRMM = 2 THEN
CASE
WHEN (IV.IVQYSD * RM.RMNETW)IS NULL THEN '000000000000000'
WHEN (IV.IVQYSD * RM.RMNETW) > 0 THEN '-' + REPLICATE('0', 23 - LEN(IV.IVQYSD * RM.RMNETW))
+ SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), 1, LEN(IV.IVQYSD * RM.RMNETW) - 13)
+ SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), LEN(IV.IVQYSD * RM.RMNETW) - 11, 4)
WHEN (IV.IVQYSD * RM.RMNETW) = 0 THEN '000000000000000'
END
ELSE '000000000000000'
END
WHEN RM.RMRSSC IN ('01','03', '04', '05', '14', '28', '34', '35') AND RS.RSRSCL = 'FG' THEN
CASE
WHEN a.IHCRMM = 0 THEN
CASE
WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NULL THEN '000000000000000'
WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NOT NULL THEN '+' + REPLICATE('0',17-LEN(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD))))
+ SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), 1, LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 13)
+ SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 11, 4)
ELSE '000000000000000'
END
WHEN a.IHCRMM IN (1,3) THEN '000000000000000'
WHEN a.IHCRMM = 2 THEN
CASE
WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NULL THEN '000000000000000'
WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NOT NULL THEN '-' + REPLICATE('0',17-LEN(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD))))
+ SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), 1, LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 13)
+ SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 11, 4)
ELSE '000000000000000'
END
ELSE SPACE(15)
END
ELSE SPACE(15)
END +
CASE
WHEN a.IHCRMM NOT IN (1,2) THEN
CASE
WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) IS NULL THEN SPACE(13)
WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) > 0 THEN '+' + REPLICATE('0', 13 - LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)))
+ SUBSTRING((CONVERT(VARCHAR(13), IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))), 1, LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)) - 3)
+ RIGHT(CONVERT(VARCHAR(13), (IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))),2)
WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) = 0 THEN '0000000000000'
ELSE SPACE(13)
END
ELSE
CASE
WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) IS NULL THEN SPACE(13)
WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) > 0 THEN '-' + REPLICATE('0', 13 - LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)))
+ SUBSTRING((CONVERT(VARCHAR(13), IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))), 1, LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)) - 3)
+ RIGHT(CONVERT(VARCHAR(13), (IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))),2)
WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) = 0 THEN '0000000000000'
ELSE SPACE(13)
END
END +
CASE
WHEN a.IHCRMM IN (0) THEN
CASE
WHEN IV.IVQYSD IS NULL THEN '00000000000'
WHEN IV.IVQYSD > 0 THEN '+' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD))
ELSE '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD))
END
WHEN a.IHCRMM IN (1,3) THEN '00000000000'
WHEN a.IHCRMM IN (2) THEN
CASE
WHEN IV.IVQYSD IS NULL THEN '00000000000'
WHEN IV.IVQYSD > 0 THEN '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD))
ELSE '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD))
END
ELSE '00000000000'
END as 'XPEDXRESULTS'
FROMIPGDB.dbo.INVHDR AS a
LEFT OUTER JOIN IPGDB.dbo.FSA06 AS FS6 ON a.IHSOCS = FS6.QB@CUS AND a.IHICON = FS6.QB@CMP
LEFT OUTER JOIN IPGDB.DBO.INVDET AS IV ON a.IHICON = IV.IVICON AND a.IHINVA = IV.IVINVA
LEFT OUTER JOIN IPGDB.DBO.RESMST AS RM ON RM.RMRESC = IV.IVRESR
LEFT OUTER JOIN IPGDB.DBO.RESUBC AS RS ON RS.RSRSSC = RM.RMRSSC AND RS.RSRSCL = RM.RMRSCL
WHEREFS6.QB@BGP = '133'
AND MONTH(a.IHIVDT) = ?
AND YEAR(a.IHIVDT) = ?
AND IV.IVRESR IS NOT NULL
=============================
http://www.sqlserverstudy.com
View 5 Replies
View Related
Feb 19, 2007
In a system I'm maintaining there is a Stored Procedure calleddbo.MyStoredProcedure. I didn't create this - it was created by adeveloper who has now left. I don't know how the object came by its"dbo." prefix, but I think he created it in QA.Anyway, there were some performance issues (it was taking between 4and 10 seconds to complete) so I copied the SQL into a QA window andit consistently ran in under 1 second. So I created a new SPROC withSQL exactly identical to the old one, but without the "dbo." prefix,and that too runs in <1 second.Any thoughts?Edward
View 6 Replies
View Related
May 30, 2006
I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me
Msg 102, Level 15, State 1,.......Line 1
Incorrect syntax near 'a'
The Select statement looks like:
select count(*) from schema.table a where a.customer_id=1234
The Delete looks like:
delete from schema.table a where a.customer_id=1234
What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix
View 3 Replies
View Related
Dec 27, 2006
Hi all,
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
Any ideas?
View 4 Replies
View Related
Nov 16, 2006
Hi,
I have created a new user in my database called "acm", I also created a new schema called "acm" as well, the "acm" user is its owner, and the default schema for "acm" user is the "acm" schema.
I also created a login called "acm" (surprise surprise) and set its default database to "mydb" database.
In "mydb" database I have a table "mytable" that is owned by "acm". In my connection string I am using "acm" as the user, but when I do:
"select * from mytable" I get error ("bla bla object bla bla"), when I use "acm" as a prefix for the table name it works fine... What do I miss??
My connection string is:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAccountManagerDb.mdf";Persist Security Info=True;User ID=acm;Password=acm;Connect Timeout=30;User Instance=False
Thanx,
Yuval.
View 7 Replies
View Related
Feb 28, 2007
Hi,
I am developing an .net application and for this application I am trying to locate System Procedures by tracing. In my trace file I find stored procedures with msdb.dbo prefix.
I have been searching it in the master db and other db, but cldnt locate it. Can anyone let me know where I can find it?
Thanks
Niranch.
View 1 Replies
View Related
Jun 9, 2008
When we add parameters in stored procedure we have to add parameter prefix "@" in case of SQL Database and in case of ORACLE Database we have to add parameter prefix "v_" but i need to know is there any class library which its handle the headache of parameter prefix either i connect to SQL Server or ORACLE or any other and i just add parameters without prefix..Please if you got my point so tell the solution ASAPThankyou!
View 2 Replies
View Related
Aug 29, 2007
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?
View 4 Replies
View Related
Mar 17, 2004
Hi Guys,
I have a program that connects to SQLServer 2000 through ADO connection.
the program executes the following query:
SELECT ax.AccNo,
(SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product
FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo
WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC
This query works in th program and in Query Analyer on my machine.
However, On a different Machine (and different SQLServer) the query works in Query Analyser but does not work in the program, the following exception is thrown:
The column prefix 'h' does not match with a table name or alias name used in the query
Any help is greatly appreciated..
thanx in-advance,
TNT:)
View 5 Replies
View Related
Aug 21, 2014
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
View 1 Replies
View Related
Jun 30, 2015
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
View 2 Replies
View Related
