In practice, I find encryption kind of messy to use -- opening, closing keys, use of certificates, all the while trying not to give away the password in cleartext. If our .NET programmers are to use it efficently in high-level code we need a function call, say "EncryptValue(<input value>)", that simply returns the encrypted value. Is it possible to write such a function? (And of course, we need the mate "DecryptValue(<encrypted value>)".
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK) WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
Coming from Oracle background:How do you carry thiese admin tasks in SQL 2000 Environment :Check instance availabilityCheck listener availabilityCheck alert log files for error messagesClean up old log files before log destination gets filledAnalyze tables and indexes for better performanceCheck tablespace usageFind out invalid objectsMonitor users and transactionsThanks for help.
Ok this may have been answered somewhere but to be honestly I don't even know what I need so its hard for me to search for it. Here is what I have going on lets see if anyone out there can answer this one. I have a 20 year old system that is tied to some lab equipment that we use for coal testing. The equipment runs back to an old win98 PC which is running a DOS program that has long since been abandoned by the manufactures. I can't upgrade the program because it will mean upgrading the hardware which is somewhere in the neighborhood of $100k at least. The program saves everything in what I believe is a dBase IV file (.dbf) and using Excel I can easily see the data inside. What I am trying to do, if I can figure out a way other than writing my own custom app, is to do a daily export/import of the data from the dbf file into a more usable database, then create some spiffy forms to mess with the data. Each day the dbf file is wiped out by the ancient program and a new one is created. My database experience is somewhat limited I have at best a rudimentary knowledge of SQL but I should be able to follow along even if I have to do a little research. Mainly what I am looking for is a way to import the dbf file into an already existing database (new or existing table) automatically via a script or something. I want to retain all the data from past days with out editing the the original DBF file. Can this be done or am I just asking the impossible?
Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February). Thanks for the help !!!!
[code] Set @Req_Month = '2'
SELECT Distinct a.contract_nbr, Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol Else 0 End As Day_2 from TIES_Gathering.dbo.contract a Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH') and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) and (c.rcpt_dlvry_ind ='R') Group by a.contract_nbr [code]
I have a database with multiple items each day. I'm looking to extract monthly information from this data. So I'm thinking that I would like to have a drop down list with each available month in the list. How would I extract which months (for multiple years) have data from a datatype = smalldatetime? I'm using c# Thanks!
I have to import data into 2 tables on a daily basis. The data is provided as a flatfile. In order to fullfill this task the tables have to be truncated first. Are there any possibilities to do this job automatically with dts, or do I have to write an Interface in VB or something like that?
I am not a SQL Server expert, normally work with a few other databases. We are running SQL Serer 2008 R2. I need to determine by day how much log space is being used. This is needed so I can size the file system appropriately to handle an outage of our backup system. The goal would be to have the log file system large enough to handle 24 hours worth of logs. I have found statements for current log usage, but not one for daily total logs generated.
I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load. Here is the approach and possible implementation.
Approach I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.
Here is the implementation given by a member of dbforums -
You'll then have to deal with 3 potential actions,
INSERT: New records on the file. DELETES: Records that don't exists. UPDATES: Records that are on the file, but attributes have changed.
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5) SELECT '1','1','a','b','c' UNION ALL SELECT '1','2','d','e','f' UNION ALL SELECT '1','3','g','h','i' UNION ALL SELECT '1','4','j','k','l' --DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5) SELECT '1','1','a','b','c' UNION ALL--NO CHANGE SELECT '1','2','x','y','z' UNION ALL-- UPDATE (My comment - Instead of an update I want to insert a new record) SELECT '1','3','g','h','i' UNION ALL--NO CHANGE SELECT '2','3','a','b','c'--INSERT GO
SELECT * FROM myTable99 SELECT * FROM myTable00 GO
--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition) DELETE FROM a FROM myTable99 a LEFT JOIN myTable00 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5) SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5 FROM myTable00 a LEFT JOIN myTable99 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- UPDATE
UPDATE a SET Col3 = b.Col3 , Col4 = b.Col4 , Col5 = b.Col5 FROM myTable99 a INNER JOIN myTable00 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND ( a.Col3 <> b.Col3 OR a.Col4 <> b.Col4 OR a.Col5 <> b.Col5) GO ------------
Can anybody look at My comments and answer them or revise this code template if need be?
Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"
select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180
If it meets this criteria I can change the select to a delete? Please Let me know what you think
I am trying to Invoice all of the records in my 'Orders' table. After each of the records has been invoiced I would like SQL to flag that record as having been completed so that when I run my query the next day it will ignore those having been completed already. Any feedback would be greatly appreciated.
This is the query I wrote to invoice one Order at a time by specifying each Order_Num seperately. As you can tell...I'm a n00b. Thanks all.
select convert(varchar, getdate(), 107) as Date select order_num as 'Invoice No.' from orders where order_num = '20009' select c.cust_name as Customer, c.cust_address as 'Street Address', c.cust_city as City, c.cust_state as State, c.cust_zip as 'Zip Code' from customers as c, orders as o where c.cust_id = o.cust_id and order_num = '20009' select oi.order_item as 'Line Item', oi.quantity as QTY, p.prod_name as 'Product Name', oi.item_price as 'Sale Price', oi.quantity*oi.item_price as Total from orderitems as oi, products as p where oi.prod_id = p.prod_id and order_num = '20009' order by oi.order_item select sum(quantity*item_price) as Subtotal, sum(quantity*item_price*.089) as 'Tax 8.9%', sum(quantity*item_price)+ sum(quantity*item_price*.089) as Total from orderitems where order_num = '20009'
What daily/weekly checks do you guys currently perform on your servers and databases?
I recently ran across with an article from SQLServerCentral that listed a couple of daily checks that I'm thinking about implementing on my environment, and some of them are: DB Missing Recent Backup - Report DB Missing Recent Log Backup - Report Drives Low on Disk Space - Report Error Log Messages Report - Report Instance Recently Restarted - Report Job Failures - Report Large Databases Log File - Report
I already have in place: Verify is SQL Agent Service is running Check Disk Space Available
Since I'm going to spend some time on this, I was wondering if there's anything else that you guys have in place or any other 'nice to have' that you guys also might have, so I don't leave anything behind...
Hello all,I have the following requirement (SQL 2000, SP2)1. Need to restore production database(A) to another database (B) onanother server (No direct connection)2. In the restored back-up(B) SP codes should not be visible (even tosa) or all SP's can be deleted .3. This needs to be carried out daily4. New tables may be added frequently to A5. This job should be scheduled6. I can overwrite the entire database (B)7. I can not encrypt SP in the original database (A)I thought of Snap-shot replication first. But when new tables areadded , I need to add new tables manually to publication .Can some one suggest most reliable and implementable method ?ThanksSrinivas
I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.
I mean, are there big differences between store data as XML format and nvarchar?
I need to create daily check list for SQL 2000. Here is what I have done:
1. Check the connectivity of each server over the network. (Ping/open Enterprise Manager and proof connection) 2. Check whether the services are running for each server. (Green light) 3. Check the scheduled tasks on the production servers are running normally. Enterprise Manager of each server or your email (Set up SQL Mail to notify you). 4. Check the hard disk space available on the SQL Servers. 5. Check all the database and transaction log space on each server. If the database or transaction log space runs out, the transactions will fail. 6. Check NT event Logs for any error messages. 7. Check SQL Error Logs for any errors occurring within SQL Server.
My question is: any other tips & ideas for every day routine?
I have a client that is using the free MSDE database engine. There is no graphical interface that I know of to manage the day-to-day tasks. I need to do a daily backup to a disk file. I want to start the backup at 7:40pm. My client then will copy that disk file to tape duing his system-wide backup at midnight. I setup a batch file and executed it in osql but the job is not working. No disk file has been created in the last 3 weeks. I don't have a clue what I am missing. Can someone please look at this and tell me how to fix it??
Thanks in Advance!
Debbie Erickson USE master EXEC msdb..sp_delete_jobserver @job_name='PetDB Backup', @server_name='Server06' GO USE master EXEC msdb..sp_delete_jobschedule @job_name='PetDB Backup', @name = 'ScheduledBackup' GO USE master EXEC msdb..sp_delete_jobstep @job_name='PetDB Backup', @step_id=0 GO USE master EXEC msdb..sp_delete_job @job_name='PetDB Backup' GO USE master EXEC msdb..sp_add_job @job_name='PetDB Backup', @enabled=1, @description='Backup Petdb' GO USE master EXEC msdb..sp_add_jobstep @job_name='PetDB Backup', @step_name='Nightly petdb maint', @subsystem='TSQL', @command='BACKUP DATABASE Petdb TO DISK = ''E:PetlicData BackupPetdb.bak''', @database_name='petdb' GO USE master EXEC msdb..sp_add_jobschedule @job_name='PetDB Backup', @name = 'ScheduledBackup', @freq_type=4, @freq_interval=1, @active_start_time='194000' GO USE master EXEC msdb..sp_add_jobserver @job_name='PetDB Backup', @server_name='Server06' GO
I have a database of details of users that visit my site. Among other things, it saves the ipaddress and the date/time of the visit. I would like to be able to determine the unique visitors (by ipaddress) that i have on any given day. The problem though is that the data is saved as a date and a time, thus when I try and determine the unique visitors, since they are all a different time, MSSQL thinks they are all unique visitors. Any ideas how I can get around this without acctually modifying the data in the underlying table?
My goal is to recreate a table daily so that the data is updated. This could be a bad decision performance-wise, but I felt this was simpler than running a daily update statement. I created a stored procedure:SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE PROCEDURE sp_CreatetblImprintPhrase
AS
DROP TABLE tblImprintPhrase
GO
CREATE TABLE tblImprintPhrase ( CustID char(12), CustName varchar(40), TranNoRel char(15) ) GO However, I was looking to edit the stored procedure, changing CREATE to ALTER, but when I do so, I am prompted with: Error 170: Line 2: Incorrect syntax near "(". If I change back to CREATE, the error goes away, but the sproc cannot be run because it already exists. Any thoughts?
I am implementing an application that is looking into another System’s database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.
hi, my company needs to import 3 access or excel ,customer order table, into ms sql database daily.orders(basic customer info), items(product info and quanity), options (options of quantity). The problem is that 3 table has to get additional column when it is imported into sql. For example, when an order comes in its intranet application, it will keep track if it is in stock or out of stock checking the product table. those 2 databases ms sql and access or excel has identical columns but sql one has more in addition . This is my first time to work this kind of case and if somebody could give me a abstract step or suggestions to accomplish this.I'll be very happy;)
I'm a web developer who is new to T-SQL scripting.
I need to write a script that will update a table from a text file daily; the columns will be identical but their content will vary. I immediately thought that dropping the existing table and creating a new one from the file as a scheduled job was the answer.
My boss thinks that this approach is risky and suggests comparing the new and old data and altering the existing table instead. My gut says that this is an inefficient and unecessary solution.
Any advice?; if so, any sample scripts you can point to?
I want to send auto email to my subscribers(no. of subscribers always vary) This auto email has to be send daily. Could it be done by generating certain Stored Procedure ? if yes then please help me by providing Stored Procedure.
I have now had the request to pull out more information but now lits all the days and the call that were logged.
I have tried to adjust the script I was given in this forum but I do not know enough to get it working.
It ultimatly does what I require but nw listing all the days in a month with a count of records.
This is the script I was given.
select month(DateRaised) as mth , count(*) as mth_count " from HD_Call where [DateRaised] >= '2007-01-01' AND [DateRaised] < '2008-01-01' AND [Completed] IN(1, 0) GROUP by month(DateRaised)
I want to be able to create a duplicate database and update / refresh it daily automatically. This database copy would be used for testing purposes so I don't want it to write back to the original database. Is replication of some kind the answer?
I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.
We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.
Has any one else had a similar issue, any ideas what could be causing this to occur.
I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it. How to make this code daily?
SELECT Receipt ,"Date" ,Item ,Reason ,Division ,SUM(Cost) AS Cost
I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it.
I have a series of daily data and I am looking to filter for month end business days and create a sum.
This piece of code gets what I need, arbitrarily, for the 7th day of every month. How can I amend the code so that rather than 7, I have the last business day of every month?
select FROM_DATE, PORTFOLIO, SUM(VALUES) from MyTable where DATEPART(day,FROM_DATE) = 7 group by FROM_DATE, PORTFOLIO
I have the following code for last business day of month (from a previous question raised on these forums), but when I set DATEPART(day,FROM_DATE) equal to the following piece of code, I get no results.
CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7 WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one day WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two days ELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1) END
