how can i tell sql server to execute the "convert" function using the literal hex value, 0xC0, rather than the string value '0xC0'? for example, this works: declare @myhexint as int set @myhexint = 0xC0 select convert(int, @myhexint) but this does not: declare @myvarchar as varchar(20) ...more >>

How to create a temporary table where the name of the table is the user's ID. #@IDUsuario ...more >>

I'm looking for some brief summaries or comparisons that support an IT management decision to implement a new system with SQL Server as the database and not an alternative db (oracle, informix, whatever). Specifically looking for things like return on investment (ROI) and total cost of owner...more >>

Hi, I am trying to execute an MS SQL Server DTS package from my Microsoft Access form. Here is the code I am using: Private Sub cmdImportScats_Click() Dim pkg As DTS.Package Set pkg = New DTS.Package pkg.LoadFromSQLServer "NTOMT76SQL,7115", "", "", DTSSQLStgFlag_Default, "", "",...more >>

Hi there, This is a view base on Northwind database SELECT dbo.Orders.CustomerID, dbo.[Order Details].UnitPrice AS NewUP, dbo.[Order Details].UnitPrice FROM dbo.Orders INNER JOIN dbo.[Order Details] ...more >>

Hi, In trigger, there's this sql statement, select * from inserted & select * from deleted. May I know what're their purposes ? Any links for additional info ? Thanks !...more >>

I am trying to create the following SP: ***************** CREATE PROCEDURE [dbo].[SUBJECT_Update_Properties] ( @PROJ_KEY varchar(4), @CDKey varchar(10), @UIKey varchar(8), @ProjectName varchar(50), @Builder varchar(50), @CommunityName varchar(50), @MasterPlan varchar(50), ...more >>

Hi, I have a stored procedure which takes a base 64 string as an input parameter. Can anyone tell me how to decode the base 64 string using sql so I can store the data as a SQLServer2000 image data type? Thanks, Paul ...more >>

Howdy I've got no idea how to do what I want to do. Directly below is my main SQL statement which is fine, but which I want to add a count to (as per text in square brackets). SELECT tbl_Users.UserID, tbl_Users.Firstname, tbl_Users.Surname, 1 AS LeadWorker, [INSERT COUNT HERE] FROM tbl...more >>

I have a query which has to return a specific number of records per region. Example Region Region ID Percent Total California 50 15% 3,750 Midatlantic 35 9% 2,250 Central 24 9...more >>

is there a function in sql server to select the REVERSE order of a 32bit hex value? then i also need to convert each byte to a decimal number. any help is much appreciated.. tia, jt ...more >>

Hi, I need to select only those rows from Activations table which are not present in the Logs table. I used the Left outer join but it returned all records from the left table. Below is the query I used: SELECT MobileID from ACtivations LEFT OUTER JOIN Logs on ACtivations.MobileID=Logs...more >>

Can someone point me to some articles on why Outlook needs to be installed on the SQL Server in order to configure SQL Agent Mail to send notifications? Also, is there any other ways of sending alerts and stuff through Agent Mail without having to install Outlook? Thanks all....more >>

Hi there, I created a view look like this select amount from table and I added condition as another column look like this (SELECT amount As "newAmt" from vPivotT where Flag "=" 'X' And tr_staus "=" 'A' And Acct "<>" '1410')ct "<>" '1410') As "NewAmt" But I'm getting an error can't p...more >>

Could someone help me update a working stored procedure to output 3 parameter? I'd like to retrieve 3 parameters from a stored procedure based on a sample written by Erland Sommarskog My stored procedure thus far accepts 5 INPUT parameters MRN, MemNAME, DOB, SEX, SSN the fields that I ...more >>

Here is the query and error message... The following error message was returned from the SQL Server: [107] The column prefix 'PremiumRateSetsJunComponents' does not match with a table name or alias name used in the query. The column prefix 'PremiumRateSetsJunComponents' does not match with ...more >>

I have a problem. My website hosting company can only host sql server = database that are less than 100 mb. Usually that is not a problem for = most of my applications, however I have one client who has a database with = over 270000 records which makes the database over 100MB. What can I do? ...more >>

I am trying to create a crosstab query using SQL 2005 and the PIVOT operator using the following data: Id MetricId Metric BaseVal PeerVal 012141 1 MarketValueChange 0.08 0.46 012141 2 BarraPredictedBeta NULL 1.44 012141 3 Beta ...more >>

I would like to have a field in a table that is a Date only data type instead of the datetime. Could someone offer any advice on this in SQL Server 2005. Thanks ...more >>

Hello, I have to add a column to my table that is suppose to be like a serial number. As my table has 1500 records and i don't want to delete them or add an incremental field, i would like to write an update SQL Scripts, but i don't know how to write it to put the record number (row number)...more >>

Looking for a recommendation on a good db diagramming tool. TIA ...more >>

Hi, I have a table with process_id and process_started_at and process_completed_at which are both datetime datatype. I need to calculate the total time taken for a particular process for a given date-range. and calculate average time per day per process_id. How can I do it I have MS SQL...more >>

Hi How can I select the last record from all my tables in a database based on a specisic date? All my tables has timestamp columns....more >>

I have a SQL Server 2005 database with fields: start_time (smalldatetime) has value of: 1/4/2006 10:15:00 AM stop_time (smalldatetime) has value of: 1/4/2006 2:30:00 PM We need the TIME only -- not the date portion of the field. I want to use a SQL Select statement in VB 6.0 to calcula...more >>

I am trying to write a query that gives me the top X [users], by hour, for a particular day. so, by order of hours, for example, I want to see the top 3 for 12am, top 3 for 1am, 2am, etc. all in one result set. My first thought was to, within a while loop, add 'where hour = xx' to the clause, ...more >>

I'm making a report in Reporting services while getting data via odbc from an Informix database Somehow I can do this in SQL query analyzer with the linked informix server if 2 > 1 begin select * from Informix.economi.root.lande end But if I do this in reporting services with a odbc so...more >>

hi- i'm working with a database that stores dates in UTC format. does anyone know of, or have, a function to convert from this format? tia, jt ...more >>

Have a table with a TEXTIMAGE specified. The text column was dropped, but the TEXTIMAGE does not drop automatically with ALTER. This is not a major issue with the current database, but forces having to edit this out on every script that includes this table. Is there a way to drop TEXTIMAGE ...more >>

I have a query where I pull data based on a min function the gives me the newest order. However when I pull the data I will get four records as I need to pull all the data for this order. What I need to know is how do I take the data that I need assign a variable to it and only return one re...more >>

The TPC site has a result in review (hp Integrity Superdome ) configured with sp1 Does anyone know if this will be available soon? http://tpc.org/tpcc/results/tpcc_result_detail.asp?id=105112801 http://sqlservercode.blogspot.com/ ...more >>

Hi - I'm wondering if there is a way to have an "Execute Packge Task" run even if the package that triggers it has multiple tasks running simultaneously? So I don't want the next package to run until the current package is finished, but there is not a definite start and end point to the ta...more >>

I am runnig query select count(*) from A I like to display the count in following format. For ex. If the count is 45 then it should display 000045 If the count is 145 then it should display 000145 If the count is 5 then it should display 000005 If the count is 0 then it should display 00000...more >>

help! I'm using a SQL server 2000 box and I'm a member of sysadmins on it and I have a remote drive mounted on the server that I can copy files to with Explorer and the command line. I've created a job that has the following line of SQL in it xpcmdShell 'copy "C:\Program Files\Microsoft SQL...more >>

A gentleman from here helped me to write a function to convert oracle date to a MS Sql date. Here is the link http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/874dc9e1d4c06e12/ac366d00a8ebc33c?lnk=st&q=dbo.to_date&rnum=1&hl=en&utoken=dgGivEkAAACAT_kPml7nPa...more >>

Table A has a PK, 2 Non clustered indexes already. Recently a new column Uniqueidentifier is added to this table and a highly transactional SP is modified to use this new GUID column as the driving factor to look for rows. This column is not indexed (uses index scans now) and performance is go...more >>

I'm doing a query on a database that has 250,000 records. The query is returning a rowset with just 12 records in it. When I do the MoverFirst() on the rowset it takes 10 seconds to accomplish. And when I do subsequent calls to MoveNext() on the rowset it takes anywhere from 2 to 12 seconds to...more >>

Hello, We are implementing clustering (Active/Passive) in our Production environment. So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and ProdDbServer2) I will install SQL Server on both the machine and i will create my database (FinDB) on both the machine's SQL SERVER...more >>

I have the following data in a table date StartTm ElapseTm shift Job# Op# WorkCtr Qty1 Qty2 20051218 0030 0030 13 165990 225 9 .0000 0 20051218 0100 0030 13 165990 223 9 .0000 0 20051218 0130 0145 13 165...more >>

I have 2 databases, say db1 and db2. In db1 I have a table tbl1 and in db2 i have a table with the same structure but different data. (db2 is a copy of db1). The structure is as follows: db1 customer totalytd totallyr db2 customer totalytd totallyr The customer fields have the same da...more >>

It is my understanding that SQL Server 2000 is "thread-safe". Am I correct in concluding that I therefor do not need to use EnterCriticalSection() and LeaveCriticalSection() when performing queries on my database?...more >>

Hello World, I have an aggregated query (A) that works fine that uses inner joins. As soon as the joins are changed to left joins (query B) the following error occurs: The grouping aggregate operation cannot take a uniqueidentifier data type as an argument. This makes sense b/c a uniqu...more >>

Is it possible to restore just a table from a database backup? If so what does the T-SQL look like to perform the event? I couldn't find any resources specifically on how to do this. Thanks, Ron ...more >>

I am looking for assistance in writing a query that will display a 13-month rolling average based on the current date. My table looks like this: DATE PROGRAM REVENUE 12/31/2004 Department A $150 1/31/2005 Department A $100 .... 12/31/2005 Department A...more >>

Hi all, I have the following tables: Member, Dependent, Doctor. I am trying to get, based on the name entered, all members and dependents that have seen a certain doctor. For example, I supply the last name SMITH and the docotor ID 1234567890. I need to get all of the members with the...more >>

I need to delete a set of IDs (unknown before the delete). I then need to immediately turn around and delete all correspoding FK table record that belong to the IDs I just deleted. Seems like there's a collection or array I would build during the delete then traverse it to deleted the detail...more >>

I have created a little store procedure that goes as shown below. This may not be graceful but it works, and this isn't the problem. The problem is (and I will be cross posting this) that when I try to link this stored proc into Crystal 8.5, I get a "Invalid file link. Field is not indexed" ...more >>

Hello, I have a query that looks something like this: select LEFT(dbo.Charges.service_date + SPACE(10), 10) AS ImmunizationDate from dbo.charges I need output to be in mm/dd/yyyy format without changing users date/time settings. Service_date is of datetime data type. -- Deki PA ...more >>

Right now, i have a T-Sql statement that grabs the latest dated article from my db to display in a blog like manner, but what I really need to do is not display the latest article until after 12 midnight server time. If the author submits an article for 2006/1/12, it will appear immediately if h...more >>

Hi, I have a DTS package that runs numerous tasks simultaneously for time and performance reasons, but I would like to add an "Execute Package Task" to this package so that another package will run, but only after all tasks have finished in this package. Is there a way to do this? Thank...more >>

I need one other favor. I am trying to delete the detail records from the detail table before deleting the header record from the pk table. I have put together the following SQL. The SELECT statement gets the 2 records I need from the detail table, but I'm not sure how to pass the ID's to t...more >>

I am in the proces of upgrading from access to SQL Server 2005, and I am having a strange problem. When I set the value a column in a data row to DateTime.Now and save it to the server the date is two days in the future! This happens for both typed and untyped datasets. ex DataTable dt=new D...more >>

I have 'intermediate' MSAccess development knowlege and I'm trying to apply that knowlege to SQL2005. Obviously is it vastly more powerfull (and complicated) so I'm struggling with a few things (at the moment...). Is there a simple way, using SQL2005 to set the default value of a column eq...more >>

Hi, I am new to SQL server. Can someone please suggest any FREE online tutorial / book which is really helpful for a beginner. I want one which starts from basics and goes to advanced level including T-SQL, OLAP and all. SQL server's books online section is not at all helpful. Please he...more >>

I'm trying to add a carriage return in the below function, but nothing is happening. The end result is to cut-n-paste into notepad with carriage returns. What am I doing wrong? thanks! CREATE FUNCTION dbo.fctConcatTitles ( @O VARCHAR(32) ) RETURNS VARCHAR(8000) AS BEGIN DECLAR...more >>

Hi, I have a table of entities. Each of which has a unique ID. For a certain subset of those entities, a particular set of values can apply to a pair of entities. Let's call the entity ID's E1 and E2 and the associated values x, y and z. E1 and E2 are GUID's and x, y and z are floats....more >>

Hello, I've got a little trouble because updates seem to trigger SqlDependencies even if the update is already finished. I'm watching data with an SqlDependency which I eventually update myself. When I update the data I do it like this: - Release current SqlDependency - Update Data - ...more >>

Hi! Apreciates any help. I have a 9 digit nvarchar identification where i'm using following code ddmmyy123. I wish to exctract the ddmmyy to a datetime BirthDate. The problem is if someone is born 14. march 1905 the code shows 140305. When converting the nvarchar to datetime it shows 1...more >>