Tag: sql-server-2005

Encryption SQL Server 2005

I have a SQL Server 2005 Database and I am planning to encrypt some of my columns using PassByPhrase. But these columns have to be displayed in the original form on my ASP.Net web pages and on some Crystal Reports. Now I know that I will have to create a Stored Procedure to decrypt the […]

SQL using GROUP BY or DISTINCT to get back first records

I have a table like this: group id 1 10 1 11 1 12 2 13 3 14 I want to get back the first record of each group, so my table should return something like this: group id 1 10 2 13 3 14 What’s the best way to do this in SQL?

How to get new clients by months in SQL Server

I have a table Clients. Every client has da_reg (date registered in our system). I need to make a report: By months – total number of clients (count(distinct customernumber)); and new customers by da_reg date (I can do this per month like insert all clients from past month into temp table and then compare WHERE […]

how to add a fetch_status in perl

I have this code for SQL Server 2005, and it works properly declare @GroupID varchar(11) DECLARE ConMaster CURSOR FOR SELECT groupid from collectorgroup where (CollectionDate BETWEEN ’08/01/2013 00:00:00′ and ’08/01/2013 23:59:59′) AND oRnO <> ” OPEN ConMaster FETCH NEXT FROM ConMaster into @GroupID WHILE @@FETCH_STATUS = 0 BEGIN exec x_sp_TDCR_Summ @GroupID FETCH NEXT FROM ConMaster […]

SQL query taking too long, are there any optimizations possible, need to be pointed into right direction

Question I recently learned about CTE’s and how to use them for performance winning, however I think I am overusing them in this case. The below query is taking about 1 minute with only 1000 examinations and I am sure it can be done much faster. Please suggest optimizations or terms that can point me […]

flat table of time periods

I am trying to create a sproc that spews out a dynamic flat table of user friendly time periods to be consumed somewhere else. This is my first attempt: CREATE PROCEDURE aaLogi.GetQuickPeriods AS BEGIN SET NOCOUNT ON; DECLARE @Today DATETIME; DECLARE @WeekStart DATETIME; SET @Today = GETDATE(); SET @WeekStart = DATEADD(WEEK, DATEDIFF(WEEK, ‘19050101’, @Today), ‘19050101’); […]

How to speed up this group by with CSV column?

I have a simple table: create table breaktypes (parent_id int, child_id int). The data is basically this: parent_id child_id 125 1 125 3 125 19 125 3722 126 32 126 44 126 13 126 14 127 266 Basically a list of parent_id values with child_id values that belong to it. I would like the data […]

How to get a stored proc to reference a DB from outside its current execution scope

I have 2 DB’s, Building and Contact, and a stored proc that executes from the Building DB, building_sp. building_sp needs to update a table, TblContact, within Contact, and they way I have been referencing it is by [Contact].dbo.[TblContact] Since the Contact table can be named arbitrarily, I need to remove this dependency. The options NOT […]

sql Pivot how can I do this

I have 3 tables ———————— users 1 -> mark 2 -> adel 3 -> khali 4 -> piter 5 -> adam ———————— groups 1 -> group 1 2 -> group 2 3 -> group 3 4 -> group 4 ———————- usersGroups 1 -> 4 3 -> 2 4 -> 3 1 -> 2 I want […]

Changing Vabinary(Max) back to text<MS SQL “2005”>

0x323031332D31312D30372031303A34393A32393B2032C2F7BFF820B9D9C4DAB5E520B8F0B5E220B7CEB5E52E2E2E0D0A323031332D31312D30372031303A34393A32393B20B8AEC6F7C6AE20BCADBAF1BDBAB8A620BBFDBCBAC7D5B4CFB4D90D0A323031332D31312D30372031303A34393A32393B2042494E445F49503A20302E302E302E302C20504F52543A203730363720BCADBAF1BDBAB0A120BDC3C0DBB5C7BEFABDC0B4CFB4D90D0A323031332D31312D30372031343A32353A34353B2032C2F7BFF820B9D9C4DAB5E520B8F0B5E220B7CEB5E52E2E2E0D0A323031332D31312D30372031343A32353A34353B20B8AEC6F7C6AE20BCADBAF1BDBAB8A620BBFDBCBAC7D5B4CFB4D90D0A323031332D31312D30372031343A32353A34353B2042494E445F49503A20302E302E302E302C20504F52543A203730363720BCADBAF1BDBAB0A120BDC3C0DBB5C7 Hello. Newbie asking for help. What I have to do is extract this binary data from a mssql 2005 DB and write it in to a file. I found out that I can convert the data using CONVERT but I don’t know how exactly I should type it in the query. A lot of […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.