OK, so here's the deal: Using .NET 2.0 Membership DB for basic username, password, and other login data. However, due to it's limitations and the headache that is Profiles, I have the rest of my user information in another database on the server, (that I call MyBigDB). I wrote an SP within...more >>

I am querying the system tables in my database to report on security for profiles and functions. The data I am querying is: SELECT SP.PROFDESC, FUNCDESC, SFSP.XREAD, SFSP.XWRITE FROM SYSFUNCSYSPROF SFSP INNER JOIN SYSFUNC SF ON SF.SYSFUNCID = SFSP.SYSFUNCID INNER JOIN SYSPROF SP ON SP.SYSPRO...more >>

Hi, I have table that has ext, tomb, dateceased, used. I am trying to create a scheduled job to exectute daily to check if today's date - the dateceased value is equal or greater then three then update the tomb field. So i am trying to do a search for all the entries that have tomb mark...more >>

Hello All! Could somebody check this trigger and expalin whay it works this way. The table has 1 column, and a bit data type. The value gets changed either to a 1 or a 0.When it's a 1, the trigger writes to the file, but when it's a zero it doesn't. I was was hoping when a 0 comes up, it wou...more >>

Is there an easy way to copy a record from an existing record to another record where all the fields are copied directly (except the ID)? I asked a similar question in another post where I was copying a record to another record I was creating and the SB looked something like this: ********...more >>

Hello there I'm using table Created function. Is there a way to return dinamic table that the fields can be set in the function according to parameters? i need it for: I get one parameter from vb6 with all the parameters with | seperator. for example: 102|Roy|Goldhammer. this mean Clie...more >>

I need to determine the maximum and average number of records entered into a table by the date they were entered, so I have: SELECT COUNT(DateReceived) AS RecordsPerDay FROM CUSTOMER_REQUEST GROUP BY DateReceived How can I then get the MAX and AVG using a single SELECT statement? Somet...more >>

I'm looking for a database tool that is designed to produce entity diagrams from an existing database (reversve engineering). Basically, I'm looking for a tool that will work directly from an existing database. Just like the database diagram tool that is built into SQL. However, unlike SQL,...more >>

I am trying to track the percentage of rollback after a spid is killed. how can i redirect the output of kill command to a variable/sql table? i tried following but does not work. TIA declare @pid int declare @sql nvarchar(100) declare @out nvarchar(100) create table #temp(m...more >>

Hi , Looking for some help in Pivot -ing the data. I sumarized my requirement here.. Basically is it possible to use the two aggregation functions(SUM() for two diff entity) in the 'pivot_clause' ? I have following table Called ProductSales and looking to get the ProdSumSales table(menti...more >>

Hi all, I am trying to write an SQL statement (Im still pretty fresh at it). I am returning rows of patient information, in particular patient requests. So a (simplified) example output might be: PID SURNAME REQUEST _ID 1 Smith R12345 2 Jones ...more >>

I'm designing a new locations model. I need to support the standard locations structures (country, state/province, county/parish, etc.) and several code standards but also need to support known and to-be-user-defined regions. Since country, state, county and even city are technically "region...more >>

Does anyone know how to write a select to search all tables in a database and return the table name and column name of any bit data type that allows nulls? I inherited a database where some bit fields apparently do not have a default value of 1 or 0. Thanks. David ...more >>

I m running1 sp from .net application, n its not comleteing taking more than 10min or so. the same sp is executing in 10 sec from query analyzer. what looks is that .net client connection is unable to get the memory resource, while query analyzer is able to get memory,. How to check this n allow ...more >>

I am trying to find out if there is an easier way to create a new record from an old record. The following code works fine. The problem is that if I add fields to the table, I need to remember to add these fields to the SP. The table has able 50 fields and I just added 10 more and needed to ...more >>

Is it possible to save the contents of an image field to the file system? I have a table that has a varchar field with the outputfilename and an image field with the actual file (doc, xls, etc.) that I need to extract to the file system. I would like to do this in a stored proc or something q...more >>

I'm writing a query (that will be used for a combobox in sql reporting services) but I need it to also return an "empty" value so that the user can choose it if he wants to leave it blank. For example, my query "select dept_id from departments" returns 3 records, so I want it to return those ...more >>

Hi, when I run the query I get the following error message: "Unknown error" Does anybody knows what it could be? I have the following version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Win...more >>

I want to be able to pick a substring out of a text field that varies in it's location. For example I want to return the string http://www.somewebsite.com out of the string below: www.somewebsite.com#http://www.somewebsite.com# The string is always between the # characters but not always ...more >>

I’m using SQL Server 2000 SP4. The DDL for the table in question is: CREATE TABLE [dbo].[TBSAuthorization] ( [OP__DOCID] [int] NOT NULL , [OP__FOLDERID] [int] NULL , [OP__PARENTID] [int] NULL , [StartDate] [datetime] NULL , [EndDate] [datetime] NULL , [EnteredDate] [datetime] NULL ...more >>

Hi, I have stored procedure with complex logic inside, and this SP tenders to be the source of dead-lock in database from time to time. By running sp_who2 and dbcc inputbuffer(SPID) I can easily tell this SP. My question is, is there a way I can find out which exact sql statement or ...more >>

I have table [employees] and table [departments] and I want to know what records in employees do not have a department. I match them with dept_id. I started with the following query, which returns all the null values at the beginning of the recordset, but I want to return only nulls: select e...more >>

Hi, all I use visual C++ to insert record to SQL Server database, because I need to insert data to db from time to time, but when I monitor the memory usage from task monitor , I find the SQL server uses size got bigger and bigger, could anybody tell me why? Joseph ...more >>

Hello, I have the following table: Customer_ID, Acount_Number, last_access_date each customer_id may have several Acount_number and each Acount_number may have several customer_id I want to know who of the following customer_id didn't access his acount for 2 years. let's say that I have a v...more >>

Hi, We have just installed SQL Server 2005 on our test machines and I am trying to use the sql server management studio to create a new database as a test. I give the database a name, the file path and choose to create the script to a new query window. Here is the script that the wizard ...more >>

Can someone give me a good pattern for managing the rollback/commit scenario in SQL server stored procedures? Imparticular, how do I know from my .NET program whether or not a transaction needs rolling back? Some errors will automatically rollback the transaction, while others won't. Any go...more >>

Hello, I am learning SQL Server 2005 and I cannot undarstand semantics of APPLY operator. Could you explain me please? Thank you very much! /RAM/...more >>

Hi, I've the following problem. I must delete a column DEFAULT from a table, but I must do it with a script, independently from the server where it'll be executed. Locally I've tried with: ALTER TABLE [dbo].[PlanningDettaglio] DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC] GO ALT...more >>

I'm wondering if having a trigger to update the LastModified field for my tables is advisable. Here's an example of the trigger. CREATE TRIGGER [dbo].[SomeTrigger_TR] ON [dbo].[SomeTable] FOR INSERT,UPDATE NOT FOR REPLICATION AS Declare @ID Int Select @ID = i.ID From Inserted i U...more >>

if i have a SP that returns data and i want a column from that dataset into a variable, how do i do that? Here is an example. declare @column2 char(100) --returns a data set with column1 and column2 exec getdata 'someparameter' --i want column2 of the top row/first in the result set in @col...more >>

Dear all, I use the backup log command to trunacte the log size ,but why the size of the log file does not change to smaller? my command : Backup log test_db with truncate_only Thanks in advance! Joseph ...more >>

I have the following select query: SELECT PID, Number1, Desc1, PID_Name FROM dbo.DIR_Assets WHERE (Class = 'extension') AND (YEAR(Exp_date) = 9999) AND (Desc1 IS NULL OR Desc1 = 'private') OR (Class = 'extension') AND (Desc1 IS NULL...more >>

I'd like to know what is the proper way to pass a table name as a parameter in a stored procedure. I tried the following, with no avail: CREATE PROCEDURE delete_encounter_template @enc_id CHAR(36), @template_name varchar(30) AS DELETE FROM @template_name WHERE enc_id=@enc_id DE...more >>

Sp we can now, in 2K5, encrypt data in columns, but I have seen nothing on how to predict the resultant requisite column length. Lets presume we are talking about varchar data-types here. How can I predict the necessary length of a column that will store encrypted data based on the lengths o...more >>

Hi, I am new to SQL server stored proc development. I am trying to run a insert into select from query. Now my problem is the statement if partially runs and then has some error it never throws back and exception to the .Net code. The code in .Net maintains a transaction since it executes t...more >>

Insert into table or update record. I am trying to write a query where information from a temporary table will be inserted into a log table. If the item already exists, it will just update that item. This is what I have so far. Right now the insert seems to add all the records, when it shou...more >>

Hello all I'm new to my role as DB admin / support person for an ASP application with SQL Server 2000 backend. The code is the same on production and development, the data is similar too (at least in orders of magnitude). Yet a coupla pages load about twice as fast on development than in prod...more >>

Hello, I would like to know if something like that exist: To make it simple, I want to have a table in SQL server and a grid in my program. When I modify the table, it updates the grid automatically and without writting special code in my program. For the moment, what I found, it's "SQ...more >>

Hi We have a field containing all lines of the customers addresses (in the Addresses Table), we have another field (in the Countries Table) containing a list of country names. We need to identify the Addresses that contain any country that is in the list. We have the below code but as ...more >>

I'm trying to optimize a set of queries that inserts records into multiple tables. I've posted simple create table scrips to try and indicate the table structure for one of these queries: create table A ( accountId uniqueidentifier primary key, name ) create table B ( accountId uniquei...more >>

Hi folks; I have a DataSet composed of 3 tables I'm using in an application, and all 3 tables are related via primary key. For 2 of the tables I need to make use of some client input and apply expressions for the purpose of displaying to the client -- but do not need to retain this info in ...more >>

Hi Gurus Is there any way to know if SQL server is running or not. Thanks for any help you can provide. Steve ...more >>

I have a programmer developed query that I need to optimize. This is being imported from a COTS product's access database via DTS. There is a large text field that needs to be used for reporting counts of word strings, of which a single row may match a few times and need to be counted. The ...more >>

Are there any programs out there, or maybe just some queries I can query = the system tables with, that will allow me to know the NTusername of who = last created/modified a stored procedure. We had some problems here = with a stored procedure, and nobody wants to claim responsibility! Thanks...more >>

I'm completely new to SQL and I was logged into my sql server as admin. while there i wanted to add my other admin account to the server and delete the Administrator account. When i log in as the other user, i don't have administrator access, and i've already deleted the admin account. How do...more >>

SQL Server 2000 - Need to determine date/time of last reboot of Windows Server that SQL Server is running on, from within a stored procedure....more >>

! noticed jobs failed all of a sudden when send mail on completion status failed to send notification. Jobs status appear as Performing Completion actions. I tried xp_sendmail nothing.. analyser hangs then tried xp__stopmail..analyser hangs. Checked mail profile in Sql Server Agent and its gr...more >>

Hi Guys, Sorry if this a long winded question with a simple answer i have the following script that builds 2 tables including data Create Table tblProducts ( intProdID int IDENTITY(1,1), strName varchar(20) UNIQUE ) go insert into tblProducts (strName) values('A') insert into tblPr...more >>

I have a query which is not having any facility surgery count for current month or previous month from table. I currently run the query which will ask me for StartDate and EndDate. If I select StartDate as 6/1/2006 and EndDate as 6/30/2006. it will pull Surgery count for Facility from the table ...more >>

This statement on a particular SQL Server causes an "Invalid column name" error: SELECT * FROM DefectTypes ORDER BY PresentOrder I have tried this on our SQL Server 7 and SQL Server 2000 and it works fine. This has worked fine on dozens of other installations. Why is PresentOrder suddenly a...more >>

Hello I'm trying to add dynamically columns to a specific table, I use a cursor, my problem is when I have to assign a name to each column, I'm not finding out How to dinamicaly do this, I'm using alter table AND add... I searched in the internet, and haven't found anything about this subj...more >>

I have been landed with trying to create a view in SQL using Query Analyzer and I've never done any SQL before. The Query looks something like this and works fine (I've changed field names and got rid of stuff that isn't relevant here.) USE Database GO drop view dbo.VW_TestView go ...more >>

Is there any way to retrieve the just the columns referenced by a Stored Procedure? As an Example suppose I have the following Stored Procedure. CREATE PROCEDURE [dbo].[SelectAllCompanies] AS SELECT CompanyCode, CompanyName, LastName, FirstName, Address FROM dbo.Company I woul...more >>

Please excuse this offtopic post but I am trying to spread awareness of atrocities being commited to animals. If you love animals as I do, please help prevent the cruelty by boycotting IAMS and EUKANUBA products for their inhumane treatment of animals. Please spread the word! http://www.i...more >>

Hello, I have created one request working not so bad but I was asking if there is others (better/easier) ways to do... I have a view with a join inside (One IdGrp can have many Idx = index where IdLang is) and I want to dispatch only one IdGrp dependending on IdLang. But if IdLang is not p...more >>