Hi all, is there a way to retrieve the members of the Sysadmin (or any other) role via a certain API-call? Or is my only chance to connect to the SQL server? If so, how can I connect without any information about users / passwords that are allowed to connect to the server? Is there something l...more >>

Does SQL server has functions to pad a string with leading zero? "1" --> "001" I wrote my own, but is looking for some native features. -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 10:50pm up 1 day 14:54 ...more >>

Given these two tables: Table1: table_no, table_name 1, "table one" 2, "table two" Table2: table_no, group_no 1, 'A' 1, 'B' 1, 'C' 2, 'D' 2, 'J' I want to build a table like this: table_no, table_name, groups 1, 'table one', 'A B C' 2, 'Table two', 'D J' Any easy way ...more >>

Hi All I have one problem. The below are 2 queries i have written for same purpose. But the 1st one is slower then 2nd one and the 1st one gives correct output without duplicates. But the 2nd one is fatser but gives duplicate if inner query / table has multiple rows for one ACCNO. I want th...more >>

Dear All I'm already get data from file. But I cann't use to query in database. the data in file is ('Custom Sta-Sof - Intl (jap)') but Data in database are ('Sta-Sof Custom') ('Sta-Sof Custom Jap') ('Sta-Sof Custom USA') ...more >>

given this table PositionHeld: EmployeeID EffectiveDate Position OtherFields How could I retrieve the most recent record with one T-SQL statement? IN another language's SQL, I did this: select max(AA.EffectiveDate) as XDATE, BB.* from PositionHeld AA, PositionHeld BB grou...more >>

I have a database table that has a "date" field that is smalldatetime format. This field is populated with the current date-time when the record is generated. What I want to do is to analyse every record in the table and return an analysis of how many records were generated during which hour ...more >>

Hi All, I am running SQL server 2003 I have my server "SQL-001p" that holds a list of all of my employees NT id's I then have my clients sever "SQGN002p" which holds information of my employees actions on their customer database. What I want to do is pull back all the rows that ap...more >>

Posted this also in adonet usegroup - but I could be using jdbc to do this as well. ---------------------------------------------------------------------------- ----- Is there a way I can expose the metadata from sql server of a database in a well defined XML format that sql server can supply...more >>

I have a table with monthly snap-shots that capture all eligible members on the 15th of the month. I want to be able to count all eligible members as of the 15th of the last month even if I run the report today. i.e. If I run the report in March, I want to be able to count the membership a...more >>

I have the following SP: CREATE PROCEDURE Orders_CreateOrder @CustomerID int, @LocationID int, @CustOrderNo varchar(20), @OrderDate smalldatetime AS Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate) Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate) If @@R...more >>

This is FYI for anyone interested. I was looking for a way to pass parameters to a stored procedure to determine the order of the resulting table. I wanted to put parameters in the ORDER BY clause, such as: SELECT * FROM tblTable ORDER BY @p_OrderField This gives an error such as "The SELE...more >>

Hi, I have a sproc returning 2 records, but I only want to return the one with the greatest date. I coukd use max on the date column, but I am returning a lot of columns with different values, so this does not work. Is there any way to do this in a query Thanks...more >>

Fix your clock. ...more >>

Here is code for my SP CREATE PROCEDURE insert_profiles @email as char(100), @password as char(100) AS Declare @enc_password as varbinary(85) Set @enc_password = encrypt(@password) INSERT INTO [profiles] ([email], [password] ) VALUES ( @email, @enc_password) GO ...more >>

Hi I am running the following BCP command to extract data from a table test_stu C:\test>bcp temp..test_stu out c:\test\out\test.rpt /S SERVER /U USER /P password /f c:\test\test.fmt create table test_stu (s_name char (6) s_year int (4) s_mean varchar (10) s_median va...more >>

Hi, We have weekly schedule db maintenance plan running on sql 2000 servers every Saturday. I have a store procedure which returns error alert values if data of tables becomes out-of-date (15 minutes or more behind getdate()) Also I have a schedule task which runs this procedure in applica...more >>

I want to offer a simple backup/restore option in my vb.net app. So I did BACKUP DATABASE MYDB TO DISK="D:\SOMEFILENAME.BKP I execute this via a SQLCommand object. backup is simple. But say the database gets wiped out, then they reinstall MSDE. I would need to either somehow connect an...more >>

Hello Sql Gurus In each and every stored procedure we use the following code to check whether the stored procedure exists or not I think most of the people are do this. if exists (select * from sysobjects where id = object_id ('dbo.tab1')) begin print 'Dropping old version of proce...more >>

One table with 2 columns emp_no end_dat 001 01/01/199 001 01/02/199 001 <null> ** The last record for this employe 002 15/12/199 002 15/12/2002 ** The last record for this employe How do I select the "l...more >>

....though I never seemed to have the need to do it before. And who better to ask than you guys? I need to make a simple select statement like, for example, "select * from member" but I'm really pressed to also get the actual columnname. Ie I'd like a result set that would look something l...more >>

If I go to the properties on a relationship between 2 tables in a diagram there is a checkbox to turn off Enforce Relationship For Inserts And Updates. How can I set this to no via T-SQL code. Thanks!!! ...more >>

Hello I have tried everthing. I looked everywhere. I have the foll functio create function GetQuantityDelByStore @storeID varchar(10) @productNumber varchar(10) @coverDate varchar(10 returns IN A BEGI declare @strSQL varchar(300 select @strSQL = 'select * from OPENQUERY(PROGLINK...more >>

Can the transaction log be suspended for certain inserts/updates?. I run a query everyday to update certain dates, I donot want the transaction log to be updated for this query. ...more >>

I am doing performance tuning on a data warehouse load process that executes a series of stored procedures to migrate data from one database to another. The processing is an elephant that takes 4 days, and I am currently focusing on indexing. I have a procedure within this load process that is...more >>

Hi I am keep getting invalid cursor state error while trying to modify column properties on existing table using enterprise manager. I read books online, it explain this error causes because server is out of space etc. I checked all that and none of them is valid in my scenario. Any i...more >>

I used to use this statement with Access db: SELECT TOP 1 CheckID From CheckLogAccount ORDER BY CheckID DESC to retrieve the last CheckID from table Recently I made links between Access tables and SQL Server and since that this statement doesn't work. It works directly in SQL Server, it works...more >>

I have been tasked with the restructuring of a website and the associated database. The site stores large text entries by taking the content submitted from the site, checking to see if it's more than 1200 characters long, and if it is, chopping it up into chunks which it then stores as var...more >>

Hi - We store the users and their passwords in a database table - the passwords are stored encoded and hashed and there is a form online through which the users can change their passwords if necessary. The question is how can I make sure that passwords are changed every 90 days and tha...more >>

It's been a while since Iv'e done something this stupid in a SP. I'm manufacturing data for footnotes. Select footid from #t2 where footnotes = @ap_adapt if @@rowcount =0 begin insert into #t2 (footid, footnotes) values (@id, @ap_adapt ) set @id = @id +1 end ...more >>

Howdy, I'm trying to compare numerical values in 3 varchar columns. The roadblock I'm hitting is the case where the numerical values are equivalent but the character representation is different, e.g. '10', '10.0', '10.' are all worth 10. My plan around this issue is in 3 steps: 1. Select ...more >>

Hey all, Quick and simple, I am running SQL 2k on Win 2k Server. Is there anyway to make a server-side cursor capable of going backwards on records. They seem to be forward only. All the info I have says there is no way. Any ideas?? Gerard Gulf Management Systems Programmer / P...more >>

Hi, I am trying to create a select query that selects various fields from various tables and then I want to create a new column through the query that is a running sum of one of the fields I am selecting. Simplified Example: Table looks like: Descr DIVCount ----------------...more >>

I am a new SQL Server programmer using Access as my front end. I have a table of names and addresses with a trigger that runs a stored procedure. This procedure concantenates address information into two lines for use on envelopes. When I attempt to add a new record on the Access side, ...more >>

Consider the following: select Item, AdditionalOrderQty + CASE (SELECT INT FROM OPTIONSTABLE WHERE STRING='Option') WHEN 1 THEN 0 ELSE ISNULL(DefaultOrderQty, 0) END As AdditionalOrderQty, MinOrderQty + CASE (SELECT INT FROM OPTIONSTAB...more >>

Hi all, I was wondering if there is a way to retrieve the column descriptions from SQL Server for a specific table? If anyone has any help to offer for this one I would be grateful. Best regards Rob Meade ...more >>

Hello All, What is a row id? Why do we have identity fields in a table, why not use row-id as a unique DB assigned primary key value rather than defining a column as an identity field? Thanks for your help in advance. regards, Abhishek....more >>

Could anyone see a problem with this trigger? It does not insert the values in target table..... Thanks ===================================================== CREATE TRIGGER ROX_candidacy_Degree_History_stage_10 ON candidacy FOR insert as BEGIN declare @numrows int select @numrows =...more >>

Hi Is this possibl declare @test in select @test = POS_GetQuantityDelivByStore "s0912070",92219,'08/25/2003' select @test + How can I add the value of two stored procedure?...more >>

Can someone pls guide to to some good URLs from where I can really learn about views I.e. besides other benefits, especially understand the advantages of using views rather than retrieving data directly from tables. Many thankxs in advance....more >>

Please do not yell at me for not being more specific. I am having a problem with a trigger and will get more specific if necessary. In general is there a reason why a trigger would fire properly when doing an update statement on a table from the query analyzer but seemingly NOT fire at all wh...more >>

I have a field that represents a year in this format - 96 I need to be able to convert it on the to 1996 or if it is 04 to 2004. Is there functions to do that. It has to be done in SQL. Thank you much, -- Ruslan Shlain nAlliance Corporation www.nAlliance.com ...more >>

I am trying to figure out a way for T-SQL to get a record upon change of a certain field. So, for example, In a table that contains treatments given to a patient with an admissionid, a date of service, and an insurance type that the patient had for that visit, I need to return the first record f...more >>

I am trying to setup an operator to receive a page when a scheduled job fails. I go to the Notification tab on the Job Properties window and select Page Operator....When the job fails. After the job fails I never receive a page. This is a major problem. Please any insight would be greatly apprec...more >>

I have a purely 'academic question', as I solved my problem using a paid of 'case' statements within a query. But in trying different ways, i could not make somethin gwork...that i know SHOULD work...so i have to find out to satisfy my curiosity. Say you have a table like this: InvoiceID,...more >>

Hi all, I know this has been asked and answered before, but I can;t find any info on it. Perhaps someone can help. I have a table thus IDcol Namecol ------ ----------- 101 Tom 101 Larry 101 Sam 3550 Tom 3550 Sam 7990 Bill 7990 Sam I would like to ...more >>

I would like to create a custom identity field. I want to use the built in SQL server identity field column however I would like to add 3 sequential characters which are none duplicating id to build a custom primary key. For example I would like to assign ids as below AAA111 AAB112 AAC1...more >>

Could I do something like this without using dynamic SQL? create procedure myproc as @table_name = "##"+newid() select * into @table_name from atable -- some other processing using @table_name drop table @table_name -- .~. Might, Courage, Vision. In Linux We Trust. / v \ ...more >>

What is the default encoding for UNICODE characters in SQL Server, UTF-8 or UTF-16?...more >>

Tis is the first Trigger I have created and am having some problems. When a record is inserted/updated on table 'TimeTrack' the trigger runs. It looks at the column 'j_ordnum' and based on the value outputs a value in 'j_recref' The trigger works somewhat and the conditional logic works...more >>

sql2k sp3 Howdy all. How would I get the count and value of only the max value grouped by another coulmn? create table #temp (RCSTK int, RCK int) insert into #temp(1,100) insert into #temp(1,100) insert into #temp(3,100) insert into #temp(2,110) insert into #temp(2,110) insert into #t...more >>

I need to get a SERIALIZABLE row level lock. My stored proc is currently using a TABLOCKX locking hint which is too coarse of a lock. I would prefer a ROW level serializable lock if I can get it. I've read the BOL description of the locking hints but the ROWLOCK locking hint description does not de...more >>

IS there in the internet any collection of queries for Northwind. I need lot's of examples how to use select, where, having,group by .......... ...more >>

Hi folks, Firstly thanks that people here are helpful always :-). I have come with another doubt today in my SQL Server learning process. Which is the good T SQL server book which has reasonably tough and good exercises at the end of each chapter?. My Intention is not just to learn the ...more >>

Hell I am trying to run this SP. The problem is that if one of my fields have null values it returns with an error 'Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query' - any help please CREATE PROCEDURE qfm_DiaryFormPMSchedul ...more >>

I think this may be an easy question for many of you, but I'm brand new to SQL and have no shame in seeking help from others much better qualified than me I have a simple 2 column table (e.g. Date, Yield) and all I want to do is return the TOP 10 Percent of records based on Yield for each Date. So...more >>

Hi Friends, What means the fowlling error: Operand type clash: varchar is incompatible with void type this error only happen in a specific server ! Thanks Johnny Silvestre...more >>

Hi I have the foll function. When I execute I get the foll error messag Server: Msg 245, Level 16, State 1, Procedure quantityDelivbyStore, Line 2 Syntax error converting the varchar value 'select * from OPENQUERY(SERVER,'select sum(quantity + overage - notdeliv - short - damaged) fro history w...more >>

Hi I have an imported table with duplicate keys but unique rows I want to remove the duplicate rows and keep only one (doesn't matter wich) The important thing is that all the information I keep comes from the same row The table has MANY columns (50+), so a select top 1 solution would probably b...more >>

Is there a way to create documetnation for SQL code like NDoc does for C# classes? I would like to create Visual Studio like documentation for a database i have created. --Papanii ...more >>

hi all i had to go to the clients and collect the data from their servers and then update my server in the office. this has to be done regularly for every week. there will not be any connectivity , so i need to copy the data to a disk and then bring it to the office. is there any possibli...more >>

CREATE procedure sp_save_placement_skill @ActionId int @NewPlacementId int @UserId in a declare @tableSkillList table (skillName varchar(100) declare @tempSkill_ID in declare @strPlacementSkillsUsed varchar(1000 declare @IntPos in declare @strSkillName varchar(50 EXECUTE p_GetActionFie...more >>