In DMO I could return a column object like this: PKCol = tbl.Columns.Item(strPKName); However SMO doesn't have an Item object and I cant find anything that would work like the line above. Im I going to have to iterate through all the columns looking for a matching name to the string strPK...more >>

Hello - I copied a database from work and attached it on my home computer. When I tried to access the diagram I received the following message: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of...more >>

I am constructing a stored procedure to populate a temporary table, I have created and tested the queries in isolation to be sure they all work, the first query: SELECT tblJobs.job_id, tblJobs.job_date, O.staff_name AS owner, A.staff_name AS assistant, tblJobs.job_site, tblJobs.job_notes, ...more >>

I am using SQL 2005, transferring data from Sybase on AIX via text file to SQL table(s). The file arrives as a compressed .gz file which is extracted to a .dat file. This is a comma delimited text file with no header row. When I use the following BCP script bcp "RatesMastr.dbo.AssetDefin...more >>

Hello Could someone explain the differences between RIGHT OUTER JOIN and RIGHT JOIN? If outer allows a table to have nulls and right join dont, wouldnt make any sense since there are inner join in that case. /Lasse ...more >>

Hello there For my job i need to make reserch about using phsycal memory against database size. Does someone have direct documentation about it? ...more >>

I have noticed that the addition of a try/catch block alters a specific error caused by a stored procedure I have. The following code is a simplified version. I understand the error (return with no commit/rollback) and have taken steps to fix, but I was wondering if anyone thinks if this i...more >>

I am not sure if this is the right forum but I am having problems doing what I thought would be a simple query, my table looks like this ...more >>

I have an Account table with fields SSN, Account_Number, and Market_Value, in SQL 2000. I populate SSN and Account_Number from some other source. I have a scalar function called GetMarketValue that takes three parameters: SSN, Account Number, and Date, and returns Money. Then I do this...more >>

there is a tree structure saved in a table. table has Id and ParentId columns, that is, table references itself. there is also a stored procedure that uses local cursors to recursively read that table. my worry is, that once some nodes have been read, someone else may modify them and tree will...more >>

Trying to make into function, want to combine the specify project_id to get the result in same row with all the information such as project_name, budget_code_id etc without any repeats. can someone help. thanks in advance. Declare @Full_Name varchar(5000) Declare @FinalString varchar(5000) D...more >>

I'm trying to restore a database by issuing the RESTORE DATABASE command via a SQL connection in ASP and it's failing with a file permissions error. The ASP error is, "Cannot open backup device '<PATH>'. Device error or device off-line. See the SQL Server error log for more details. RESTORE DATA...more >>

hey all, i have 2 lines of code inside my try block. they both divide to 2 variables. if the first line fails it appears that the second line doesn't run at all. is this true? and if so, is there a way to tell it to run the 2nd line anyway? thanks, rodchar...more >>

CREATE TABLE [dbo].[Category]( [CatID] [nvarchar](16) NOT NULL, [CatNO1] [smallint] NOT NULL, [CatNO2] [smallint] NOT NULL CONSTRAINT [Category$PrimaryKey] PRIMARY KEY CLUSTERED ( [CatID] ASC, [CatNO1] ASC, [CatNO2] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = O...more >>

I recently re-wrote a simple sp that given a start and end date it will spit out all the days that are not weekend days or holidays as specified in a holiday table. This function originally did a WHILE loop incrementing the days then checking each individual day if it was a holiday or weekend,...more >>

hey all, i have a working stored procedure and i did a test on it and the return value returns a -6. how do i know what that is? thanks, rodchar...more >>

Hello I have an item table and a detail table that I would like to join. Table1 ItemId Subject Type --------------------------------------- 1 Item001 5 2 Item002 8 Table2 DetailId ItemId DetailName DetailValue ------------------------...more >>

I have a table with 121 rows, 5 columns; last column being a currency value. How can I rank from the currency value with the max rank being 100, with every record below 1 ranking as 0. I have tried DENSE_RANK() OVER(Order by CurrencyValue ASC) but I get the max currency value as 110 and every ...more >>

hey all, i have a stored procedure that takes 2 variables and divides them. what's the best method to handle the exception of dividing by zero? thanks, rodchar ...more >>

I have been given a task, with 2 parts, which I've never used. I've searched this newsgroup, as well as the net and haven't come up with an exact solution, so I need to ask for help. We have a stored procedure here at work, that has been scheduled to run (in a dts package), nightly. However, a...more >>

Here's a simple query: SELECT TOP (100) PERCENT I.IBITNO, I.IBITCL, I.IBITSC, I.OHQ, I.AvgCost, = ISNULL(dbo.fnConvertDate(H.IATRDT), '2001-12-31') AS TDate, = ISNULL(H.IATRQT, 0) AS IATRQT=20 FROM dbo.vwInventoryAgingI AS I LEFT OUTER JOIN dbo.IAHST AS H ON LTRIM(RTRIM(I.IBITNO)) =3D LTRI...more >>

Hello, Hopefully, I am posting to the right newsgroup. I am getting the following error on SQL Server 2005 using stored procedures on Win 2003 server. There are records in the tables referring to CRF and AEPCC. The ASP code is included below for aewk.asp and aewk_action.asp. I am not sur...more >>

Or just my imagination?! I have been using SQL 2005 for a year and dislike it more and more as time goes on, any improvements to the product have been overshadowed by all the problems I have encountered. I don't see a lot of postings about the many annoying behaviors of this product and wanted ...more >>

I am trying to write a query which looks something like below: Product Table: ProductId int Type int Quantity int Type in the table above can have any value between 1 and 5. There can be multiple records in table with same ProductId and Type. To end users Type is classified in Type A, B...more >>

I have a store procedure running everyday (a Job excute this store procedure). Everytime after I re-boot the server, it only take less than 1 minute to run. Day after day, it running slower by a few seconds or more, eventually it took more than an hour to run. And it slow down the whole syst...more >>

Hello Experts, I would like advice about when to use uniqueidentifier and when to use Int data type columns as Keys and Foreign Keys. Thanks in advance, x-rays...more >>

I am wondering if there are stats on stats? (SS 2000 SP4) I've got a server that has a lot of databases - about 250 - and I want to make sure that stats is getting run on all of the databases. (Yes, I've got Auto Update Stats and Auto Create stats turned on.) Is there any way to get this f...more >>

Can anyone tell me why this is a syntax error? declare @user_id as varchar(8); set @user_id='TEST'; DROP USER @user_id; GO Thanks! ...more >>

I am having problem with my code it is just printing the last record, and skipping everything else. If someone can take a look n help me out. That will be great help. Thanks in advance. Declare @Full_Name varchar(5000) Declare @FinalString varchar(5000) Declare @Project_ID varchar(3) declare...more >>

I have the following two qeuries producing the following results: select step,JobName,Status='None' from WorkFlowDetail where WorkFlowName=dbo.GetWorkFlowName_fn(42) order by step 1 Gancho_Approval None 2 Gancho_venta None 3 Gancho_FTP None 4 Gancho_file None select step,JobName,Sta...more >>

Hi, If I have a tbl1 with col1, col2, col3, col4 Is there a way I can do a simple SELECT * FROM tbl1 and I could exclude col1? Thanks, hj ...more >>

USE SUTDNet DECLARE @id nvarchar(14), @err int, @Dt datetime SELECT @id = CD_ID FROM S_CD WHERE CONVERT(int, SUBSTRING(CD_ID, 10, 5)) = 2 SET @Dt = GETDATE() exec @err = DoCDSend @ID_User = 11, @ID_CD = @id, @Date = @Dt, @Desc = 'Send for Example' I get an error " Server: Msg...more >>

I'm changing sql server configuration using a sql query .. these changes needs restarting the server . So .. Is it possible to restart the server , & how ?? Thanks...more >>

hey all, i have a query with 2 variables that i'm trying to divide to get a percentage. for example, i'm trying 1 / 4 to get 0.25 but all i'm getting is 0.00. do i need to open an incident with microsoft? thanks, rodchar...more >>

USE SUTDNet DECLARE @id nvarchar(14), @err int SELECT @id = 'CD1122BOM00001' exec @err = DoCDArchive 1, @id, GETDATE, 'Archive for Example' Why do I get an error "Server: Msg 8114, Level 16, State 4, Procedure DoCDArchive, Line 0 Error converting data type nvarchar to datetime." (I use M...more >>

i want to group the results by 'descr' and sum the 'amount' column at each group. I can't quite figure out the script. any help would be appreciated. thank use myDB declare @mynum integer set @mynum = 136 select company_id as comp, amount, batch_code as code, batch_number as batch, batc...more >>

I have a table which is expected to grow very big over time ( in the order of millions of rows). I have some standard queries that I need to run to extract results from this table. I was hoping to create database views using the queries. Is this going to impact the database performance at all? ...more >>

Hi, I have created a View that works the way I want it to; but when I try to paste it into a Stored Procedure I get the following error message "Msg 102, Level 15, State 1, Procedure procInboundPostSelect, Line 33 Incorrect syntax near ')'." Now as far as I can tell SQL is complaining about m...more >>

Hi, I am trying to convert some SAS into SQL and I am struggling with the following What I need to do is for each distinct reference ID populate the table with this ID and then in the other column add 0,1,2,3,4. The table should look like this. ID Decision_Week 12345 ...more >>

I have a requirement to create an audit trail for a set of SQL Server 2000 database tables. The tables have no primary keys. The audit fields are createdBy dateCreated UpdatedBy dateUpdated I successfully updated the table with the 4 fields for audit trail The first 2 audit fields (cr...more >>

I have a method (in c#3.0) which loops through all the SPs in a sql05 db and adds them to a tree control. my problem is that I only have about 100 SP, but it takes for ever because its looping through all the system SPs as well. is there a flag I can use so the database object only returns u...more >>

Hello, I have 2 tables: Users and Documents. Then I have many other tables which are related with FKs to Users and Documents. I want to delete all the dependent records on all tables when I delete a document in Documents table. However, I don't want that to happen when I delete a user in ...more >>

I've been landed with the task of converting an existing piece of VB.NET software that currently uses DMO to use SMO. The code in question instaniates a DMO object, as iluustrated: Private m_dmoInstance As SQLDMO.SQLServer Subsequently it calls it's methods of:- LoginSecure Connec...more >>

Hello Experts, Which of the following statements is best to use?: select datepart(year,getdate()) OR select year(getdate()) Thanks in advance!...more >>

Hi, Can anyone please tell me why sqlservr.exe uses very high memory usage? In one of our application, we found that the system becoming too slow because of this SQLSERVR.exe. The momory usage sometimes crossing>1 GB. Can you please tell me What is the function of SQLSERVR? How to release...more >>

hi, If I have below statement where there are records on month April, it will appear the sum, as per normal SELECT SUM(item1) as Sum_Item1, SUM(item2) as Sum_item2, SUM(item3) as Sum_item3 FROM tblItemTable WHERE month(dDate) = '4' and year(dDate) = '2007' Result: Item1 Item2 Item3...more >>

Hi, My website is hosted by a company which is located in Canada. All database servers and databases are in SQL_Latin1_General_CP1_CI_AS collation and I am unable to change the default settings of the server because of security issues. I can intsert, update, delete the entries in my databas...more >>

sql 2000 having table tab1 copied into table tab2 with ordered select, like select * into tab2 from tab1 order by key1 is there some guarrantie that tab2 will then also appear ordered in key1 order in subsequent raw selects, like select * from tab2 is there some physical memory or...more >>

Hi, I want to avoid using a trigger and following is the situation I am in. I send a dynamic sql from front end to insert rows in a particular table. Now, with the inserts some other tables should be updated/inserted with values based on the new rows coming in. right now, i use the trigger...more >>

Is it possible to encrypt the data in SQL Server 2005 such that even the Database administrator cannot decrypt the data? ...more >>

Hi All, I am an VC++ programmer. I had written an VC++ application, which will fetch data from SQL Server. I am doing DB Connectivity by creating a DSN. My application works perfectly. But it lacked with need some performance issues when the DB Connectvity fails. ...more >>

Hi All I need to find out if a column constraint exists (i.e default value in a SQl Server 2005 Table column) The following code does not detect a constraint that exists and hence I get an error that the constraint already exists if not EXISTS (select * from INFORMATION_SCHEMA.TABLE_CON...more >>

Hi all, I have a complex trigger that must be applied on almost every table of my database. I'd like to create a SP or a FUNCTION with just one parameter (the table name) and call it on every table in order to create the trigger on the provided table. Is it possible ? If not, have you an i...more >>

Hi! Did I get this right? * One connection has one SPID (select @@SPID returns an integer that only is valid for the current connection). * No other process can share the same connection unless it is closed (con.closed) in which case it is returned to the connection pool. Something li...more >>

Moving from VB6 and now a newbie with VB in Visual studio and SQL 2005 please can you help with 2 questions? First -------------------- I have a database and a webpage with a table view linked to a formview to select and display a selected record. This works fine. I would like to be able t...more >>