Hai guys, Thus far i am using the following statements for encrypting a password variable in sql server 2005 OPEN SYMMETRIC KEY SecureSymmetricKey1 DECRYPTION BY PASSWORD = N'StrongPassword'; DECLARE @encrypted_str VARBINARY(MAX) select @encrypted_str=EncryptByKey(Key_GUID('SecureSymmetricKey1'),@Password) Is this a good practice o...
I am having a strange problem. I have a function which returns a large string by concatenating several other strings. In some cases the string is too long and is getting truncated. For example, there is an instance when a string of length 379999 is supposed to be returned, but what I see is that the string is truncated and the length i...
I think it's a basic SQL question...sorry..new to it... :-) I want to execute a SP inside which { I want to execute a SP (using EXEC) and depending on it's result I want to execute another query (INSERT) which if fails for any reason should revert back what the SP has done? } How can I do this? Transactions? If yes, how? PLEASE...
Hi How can I distinct result of paged query? I want to distinct results that I paged using SQL Server 2005 paging method SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [dbo].[Tbl_Kartabl].[FKLoginID]) AS Row, [dbo].[Tbl_Kartabl].[FKLoginID] FROM [dbo].[Tbl_Kartabl]) AS PagedResults WHERE Row >= 1 AND Row <= 10 How can I ...
FYI: SQL Server 2005 I have a database user account (user_web) that has the ability to connect to and run queries and stored procedures in my database. Specifically, I have given the user the db_datareader and db_datawriter roles as well as granted them execute permission on the specific stored procedures it needs to be able to run. In...
I have designed databases several times in my company. To increase the performance of the database, I look for Normalisation and Indexing only. If you were asked to increase the performance of a database which has approx 250 tables and some tables with millions of records, what different things you would look for? Thanks in advance. ...
I have two table Book (BookID, BookName) Issue (BookID, IssueDate, Qty) So, how can i find the maximum "issued book name" for a specified date. ...
I am trying to read in a text file from an SQL query (SQL Server 2005) but am not having any luck at all. I've tried various things with EXEC and xp_cmdshell, but all aren't working. This is the general way I've tried to approach this: CREATE TABLE temp (data varchar(2000)); INSERT temp EXEC master.dbo.xp_cmdshell 'type file.txt'; I ...
I have a database where all of the tables are prefixed with a set of the same characters. This was done because at one time they were in a shared database set up for pet projects with hundreds of other tables. The application, and thus the database, is now ready to be moved out of that phase and ready to be out on it's own. I would like ...
I've got a query like the one below: Select ser.key From dbo.Enrlmt ser Where ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND ser.EnrlmtStatCode = '4321' AND ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND ser.Key not in (select Key from Enrlmt ser2 ...
How would I iterate through the tables of a database, and determine which publication and article those tables are associated with? ...
Edit: using SQL Server 2005. I have a query that has to check whether rows from a legacy database have already been imported into a new database and imports them if they are not already there. Since the legacy database was badly designed, there is no unique id for the rows from the legacy table so I have to use heuristics to decide wh...
I have the following select statement that finishes almost instantly. declare @weekending varchar(6) set @weekending = 100103 select InvoicesCharges.orderaccnumber, Accountnumbersorders.accountnumber from Accountnumbersorders, storeinformation, routeselecttable,InvoicesCharges, invoice where InvoicesCharges.pubid = Accountnumber...
How would I update data in a table in a separate database based on the records in the current database? For instance I want to update the field "status" in the database called "database_old" with the value contained in the database "database_new" . My current data exists in the database "database_new". I want to only update records in ...
Hi I know about Integrated Security =True/SSPI is same, but do not know about Persist Security=True Could you please explain Thanks ...
I'm working with SQL Server 2005. Let's say I have a document like this: <Item Type="Menu" Name="File"> <Item Type="Selectable" Id="Open"/> <Item Type="Selectable" Id="Close"/> <Item Type="Menu" Name="Export"/> <Item Type="Selectable" Id="As JPEG"/> .... and so on, with N-level of menus How can I tra...
Hi, I have a record which I viewed using DBCC page command. Here is how it looks :- Memory Dump @0x00E5C060 00000000: 30000800 01000000 02000001 001f8000 †0............... 00000010: 00d10700 0000009a 00000001 000000††††............... Slot 0 Column 0 Offset 0x4 Length 4 col1 = 1 ...
Hi Can somebody explain me the difference between Temp table and table variable in SQL server 2005? ...
So my employer has asked me to come up with list of "requirements" to put in a SQL Server. Just very general, however I am pretty inexperienced at such a thing. So my list looks like: SERVER SQL SERVER ENTERPRISE EDITION ADDITIONAL SOFTWARE and that is about it. I know very little about the make up of servers, so I have no idea what...
I'm looking to add some new functionality to an existing application (database is Microsoft SQL 2005). Basically, I'm trying to calculate how many minutes (or seconds) a particular department was "unmanned" for a particular set of date ranges. I'm looking to query the dataset ideally with one statement. I have a routine that loops throug...