I was trying to debug a stored procedure today, and ran into the following situation: Table MAIL and table APPEALS both contain a column called APPEALCODE. I ran these three queries in Query Analyzer: Select count(*) from Mail --returns 4000000 Select count(*) from Mail where Appealcode I...more >>

If i want to get the number of days between 2 dates, how do I do so ? For eg: how many days is it between 5/1/2007 and 6/15/2007 ? Thanks ...more >>

Hi I recoded a stored procedure into an UDF , where do i put the Maxrecusion option ? In my old stored procedure i did put it richt behind the last select, but in the UDF version i get an error.: Incorrect syntax near the keyword OPTION ALTER FUNCTION dbo.GetPartsInDesign ( @designid ...more >>

Hi All, Do any one know idea about Notification Services in SqlServer 2005. If so, can u plz give some basic information on that. Thanks in Advance ...more >>

Hi, I have a .net dll that wraps a non .net dll (c++). I need to call the ..net dll from sql, but where do I place the .net dll so it can be accessed ok but the registered assembly? Where do I place it and what are the steps needed? Thank you in advance...more >>

Hello, I'm running MS SQL Server 2005 developer edition on the Windows Server 2003 Small Business Edition. Few days ago I have updated to the SP2 via windows update. Since then I cannot connect to the SQL server remotely (using Management Studio). - If I log remotely to the server box I ...more >>

I have the following query and I am calling UDF defined function. But when i runuit it gives me following error message Msg 229, Level 14, State 5, Line 1 EXECUTE permission denied on object 'TimeDifference', database 'twcsan', owner 'dbo'. SELECT tck.tcktreceived, tck.ticketid, tc...more >>

select a.event, a.customerNo, b.name, count(*) AS theCount from customermembershipallocationsused a join events b on a.event=b.id where customerno='10006' GROUP BY a.event, a.customerNo, b.name will give Event CustomerNo Name Coun...more >>

Hi, i have 2 tables. itemFamily table. itemID familyID active deactivateAdvertising table: itemID familyID here's what i need from the query: i want to make sure that when I add a record to the deactivateAdvertising table, i need to check if all the items in the family are present in...more >>

Hi, I have the following schema: Words ( WordID int identity, Word varchar(20) ) UserWordsLink ( UserID int, WordID int ) I need to insert rows into Words where the Word doenst already exist. Then get the ID for the new and existing words. These along with a U...more >>

select a.event, a.customerNo, b.name from customermembershipallocationsused a join events b on a.event=b.id where customerno='10006' will give Event CustomerNo name 531 10006 Sleeping Beauty 531 10006 Sleeping Beauty 531 10006 Sleeping Beauty 533 10006 Char...more >>

All, I have a column where the data is stored as below: "113";"0";"INV147110";"";"0";"10000" "113";"0";"INV168905";"";"0";"210000" "113";"0";"INV155938";"";"0";"130000" "113";"0";"INV160176";"";"0";"70000" "113";"0";"INV107466";"";"0";"50000" "115";"0";"SCP103759";"";"0";"10000" I'm tr...more >>

I need to write a CASE statement which would choose the subquery in the where clause based on an input variable. I would really appreciate your help I am using this in a stored procedure - select code,first name,last name from employee join dept on x=y where column1='some value' and ...more >>

When I use join query returns duplicate values such as ID Name CustomerNo NumAllocations 531 Sleeping Beauty 10006 10 531 Sleeping Beauty 10006 20 533 Charlotte's Web 10006 10 533 Charlotte's Web 10006 20 instead of ID Name Custome...more >>

I have a table of users and each user can have a manager. I am creating an administrative view for managers where they can see all users, and if they are the manager for that user, I want to show a checkbox that is checked. Is there a way using SQL to add another column to the Select statement...more >>

I have a keyed database with some corrupted records (apparently) when i try to do an export - i get an error message at row 66246 "Syntax error converting the varchar value 'P' to a column of data type int. and the export stops. I am exporting to a text file. I tried to bypass it by ignor...more >>

Hi Assume that you have a large query, you want to split it up for performance/readability. Whats the best way to do this and whats the pros/cons of these approaches - temporary tables - subqueries - views Views is probably the area I'm most confused about, reading about seems to suggest...more >>

Hi all, don't know if I have this in the correct usergroup but here goes. I have a database in MS Access 2003. I also have a DTS package in SQL Server 2000 that takes data from the Access db and updates some tables in SQL Server. What I want to do is, after the package runs successfully, I wa...more >>

What Microsoft ZIP (compression) function(s) are available to reduce the size of files on a server? The intended files I am trying to compress consist of SQL Server backup files with a .BAK extension. The files are created by the maintenance plan utilization on SQL Server 2000. I would like to...more >>

Hi, I have the following query. my message field is of datatype text. But I want to grag just first 50 characters + ... from that "message" field. How can I do that. The following is my sql statement select tck.ticketid,tckmsg.message from tbtickets tck inner join tbticketsmessages...more >>

I have an issue with my date fields in sql server 2005… Datatype is : datetime Whenever there is new entry from the application (ASP.NET) and if no date is entered … field gets populated by 1/1/1900 12:00:00 AM Why this happens? If no date is entered … this field should be NULL ...more >>

We are installing a new server X replacing server Y. Many of our OLAP users (over 200) have exported the OLAP reports they frequently refer to, into EXCEL documents on their desk tops, pointing to the database in Y server. We are wondering if there is a way (through creating an alias for ser...more >>

Hi, I have the following select statement. for the tcktmessage field I need to grab just first 50 characters + .... . How can I do that in query. select tck.ticketid,tckmsg.tcktmessage from tbtickets tck inner join tbticketsmessages tckmsg on tck.ticketid = tckmsg.ticketid....more >>

Hi, I am trying to insert the stored procedures into a table such that the name is in a filed and the whole code is in other field and variables , dependencies and date of change in a separate field. so that the change can be track by going through the table . please suggest me how t...more >>

Hello. I have the database with 2 users. In SQL Server 2005 there is the option to create a user without the login. But I need to create it in the SQL Server 2000. Please help me with that. Best regards, Dmitry. ...more >>

I would like to use the features of xp_printstatements on a sql 2005 server. The dll that was required in 2000 (xprepl.dll) for xp_printstatements exists in 2005 under y:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xprepl.dll, however I loaded the proc to the 2005 server: SETUSER...more >>

Below is my table PT_RackPrice. I would like to run a query that will list the most current record based on Rack_effectiveDate, Rack_effectiveTime ( max(date) & max(time)? ) for each Rack_terminalID, Rack_SupplierID, and Rack_ProductID The resulting dataset should include all the columns in th...more >>

I'm creating a program using MS SQL 2005, VS 2005, and will have around 30 tables. The program will be run on a LAN and on stand alone notebooks with SQL Server Express, it will not be run on Internet or over WAN. I need to know how to create my datasets so that it is fastest for the enduser. ...more >>

We need a CDC (changed data capture) solution with a vendor hosting our GL and the vendor does not want to replicate to our local server. Instead, they propose log shipping the GL's database to our local server. I am interested in identifying the data that -changed- in certain tables and wou...more >>

Does anyone know where to find job scheduling information. The table sysjobschedules does not provide the job scheduling information i need. It only tells me when the job is going to run next, not the actual schedule information. Where is this information stored....more >>

Hi, I have a database role APPLICATION_ROLE Is there a possibility to execute a sql command to grant execute rights for on all stored procedures? Manualy I can mark the SP -> Properties -> Permissions->ADD->BROWSE->Mark the role, check Grant execute rights -> OK. But how can I do this o...more >>

Hi, I'm using the SP sp_help_job to get historical data on job runtime etc., When I query all jobs I seem to be getting duplicates. Every job history record is duplicated in the output, apart from maybe a slight 1 second differnce in run-time now and again. the only thing I can think of is Du...more >>

Hi, I am trying to convert a char date value of '18062007' to a date value of 2007-06-18 but not matter what I do it just doesnt seem to work, the script that I am using is like this, the Dataset Due field contains the formatted date 18062007 and is a Char field! select CONVERT(varchar(8...more >>

Hi, i have 2 quries: SELECT ([PRODUCTS].[PRODUCTS]) AS [PRODUCTS], ([MEASURES].[SALES AMT]) AS [SALES AMT] FROM (SELECT ([DREK_VIEW].[PRODUCTS]) AS [PRODUCTS] FROM [DREK_VIEW] GROUP BY [DREK_VIEW].[PRODUCTS]) AS [PRODUCTS] INNER JO...more >>

In SQL 2005 is it possible to create and validate a security token based on a supplied system (not SQL) user name and password? ...more >>

How can I determine using C# which instances of SQL Server 2000, MSDE, 2005 or 2005 Express, if any, are installed on a client computer? For instance, by knowing the computer's name, I could guess at a named instance of the data provider and use it in a connection string, assuming Windows Int...more >>

Is it possible to Switch scheme by using Value in a select statement Like i have 2 Shemes Sheme Country1 ---------------------- Table - Users ------------------- Field - ID Field - Name Field - Password Field - ConnectedScheme Field - ConnectedUserID Sheme Country2 ----------------...more >>

I have a tree data structure in a C# app that I need to project into a SQL table. For the sake of discussion, the columns basically look like: int nodeId <node columns for properties> int parentNode The table has a default root node and a foreign key on parentNode to a nodeId. The tree ...more >>

I have a situation where I need to insert a new row into a table. A number of the fields to be inserted I already have values for, but there are also a lot of fields that I can only get the value from by looking at a particular field in table 1, getting the key for that row, and then using that...more >>

I have a SP with a CTE SP has 2 parameters the statement for CTE is ....... WITH rulaj (debit, suma) AS ( select debit, sum(suma) as suma from nc where data between @data1 and @data2 --parameters of SP group by debit ) ........ I receive 'Must declare the scalar variable "@data1"' Can C...more >>

can we get the nth record from any table directly wtihout using the primary key of the table . Ravishnakar Maduri ...more >>

I have to produce both sql server and mysql versions of my application and the following sql isnt understood by mysql. The trouble is, I know it produces the result i need but Ive forgotten what the following is actually doing. I wont be able to convert wthout having a full understanding. ...more >>

Hi, This is hard to explain so I will try my best for my own sake! I have a website that will compare two or more items. Lets say MP3 players. I need to comapre them side by side. Lets say the phone has the following attributes to compare; Model Size Memory OS Now it would be ea...more >>