I want to create a desktop shortcut that will open QA with a .sql file and also open with the master database as the current database. I found the example syntax on the web, but still need some help. If my server name is "myServer" and my script file is "c:\sql\myScript.sql" and I'm using S...more >>

Hi all, I'm migrating a process from SQL 2000 to 2005. it generates an XML file using the cmd: bcp "EXEC PROD.dbo.client_export_events_xml 1" queryout "e:\cli_DP\cli_events.xml" -S"PRD-SQL1\SQL2005" -Uxxx -Pxxxx - c -r -t The proc works fine in SQL 2005 SSMS, BCP crashes with the following e...more >>

How can I view the actual date and time value of a Transact-SQL 'timestamp' column? I see that it's a string of binary numbers. Can this be translated into a real data and time, or is just a way of version-stamping table rows that really has nothing to do with an actual date and time? Tha...more >>

Have a simple sales tax table containing: CityId, TaxRate and EffectiveDate colums. The table contains historical data as well as planed rate changes in the future. Need to return a set of records, one for each CityId with the rate that is in effect, to make it simple, lets say today. You...more >>

SQL SVR 2000 I have a list of about 100 stores DECLARE @store varchar(4), @storeList varchar(600) SELECT Store_CD from STORE In the end, I want a variable to contain all of the store_cd's delimited by comma's. expected output (variable contents): 01,02,03,04...etc. I can cursor throu...more >>

I want to create an alert for an error # and the alert message will include the hostname (not spid) which causes the error. 1. How to simulate an error to test the alert? 2. How to include the include the hostname as part of the alert message? Thanks....more >>

I've always been taught that stored procedures are better than writing SQL in client code for a number of reasons: - runs faster as is compiled and lives on the database server - is the more proper tier to put it since is a data function But then I've heard that writing SQL in my client .NE...more >>

Hi I am learning to use SQL Server 2005 I created a database called db on the DOTCOM server but when i try adding a new user using the SQL server authentification i get no accounts on the dropbox. How can I create a user that would appear in the login ...more >>

When using views; does the view get executed completely before adding the where clause in a select? Example: Test1 is a view. Select * from test1 where name = 'John' Will all data get pulled from test1, then rows eliminated according to the where clause? How does it work exactly? ...more >>

I have the following structure: line_id Int, job_id Int, totaltime Int, catergory Int Here is a data sample: 1 1 10 1 1 1 20 1 1 1 15 2 1 1 25 2 I want to have: 1 1 30 1 1 1 40 2 using one SQL statement. Can someone help, please?...more >>

Google isn't showing me the love today. I know it's possible to back up and restore SS2K diagrams, but I just can't find the article(s) at the moment. Anyone? Thanks, Paul ...more >>

Hello, In Sybase I can use the select @@serverName, but I was wondering if there was an equivalent statement that I could use in Oracle. Thanks, Andy ...more >>

we have very critical databases running n 24/7 production environment. I want to set optimization job (Reorganize data/index pages AND Remove unused space from database files) Whats the impact on the system if I set these optimization jobs? During these jobs, will application slow or perfor...more >>

Is there a way to use ISQLW to query an MSDE database? Thank you. Robert *** Sent via Developersdex http://www.developersdex.com ***...more >>

I have a stored procedure that looks like: UPDATE Openquery(DEVDB3,'select Crew_Member_ID, fleet_Type_nbr from crew_member') set fleet_type_nbr = Crew_DataSQL.dbo.CrewVacationBiddingDataUpdated.fleet_type_nbr From Crew_DataSQL.dbo.CrewVacationBiddingDataUpdated Where Crew_Member_ID = @Crew...more >>

Hi, I've been looking at previous posts to try and figure out how to get my query to return the most recent date for each item and I can't figure it out. Here is my query: select vw.item_code, vw.qty_onhand, rh.date_rcvd from incinvw vw, incinv1 v1, podinrh rh where v1.lot_control_ind =...more >>

Is there a way to use a single SELECT statement to "combine" multiple "LIKE" records in a one record output? In this case, the common field between these two tables is "frecno = item_rec" Thank You. Jack Table 1: Item Item_code desc1 frecno table 2: Item_status Item_rec Loc...more >>

Hello, I am selecting some articles and some comments related with it: SELECT a.ArticleID, a.Title, a.Content, c.CommentId, c.Title, c.Comment, u.UserName AS ArticleAuthorName, u.UserEmail AS ArticleAuthorEmail FROM Articles a INNER JOIN Users u ON a.AuthorID = u.UserID INNER JOIN C...more >>

I have a somewhat complicated billing process, and the only solution I can come up with is using nested cursors and dynamic SQL. I'm hoping there is a better way. Big picture view: an administrator can create custom rules that will trigger certain fees on a set of users that will be invoiced. ...more >>

Hi all, I need to be able to pull back a list of rows (user data - names etc) from a database who exist lower in a hierarchy than the user who is calling the stored procedure. I know I can use Microsofts new CTE in SQLServer 2005 to get a list of all the hierarchy points for the hierarchy of ...more >>

I'm looking for a reality check into the best approach to solving an issue. We currently have a single server that houses many databases. We are undertaking an effort to migrate these databases off onto two different servers. Some of the databases contain confidential data while other do no...more >>

Hey, I have a stored procedure, which requires current timestamp as one of it's parameters. I tried resolving the issue using the following piece of code strcpy(sqlString,"{call Pi_FUNCTION1 (?,?,?,?,GetDate(),?,?,?,?)}"); returnVal = SQLExecDirectA(hstmt, (UCHAR*)sqlString, SQL_NTS); On...more >>

I've done a lot of searching on this and can't find anything that looks like what I want to do. I'll simplify things greatly. I have a variable coming into a stored procedure that contains the columns I want to select on eg: @metrics = 'CPU_Average,CPU_Peak,MEM_Average' the select stateme...more >>

hi, Can i have one trigger for both Update and Delete Delete Trigger --------------------- create Trigger [tr_delete_user_log] on [dbo].[user_log] for delete as begin insert into z_user_log select * from deleted end Trigger Update --------------------- CREATE Trigger [tr_update_user_lo...more >>

Hi, My procedure looks like; -->S001002DB01 is linkedserver create table #tmp(KutTracking nvarchar(50)) insert #tmp exec S001002DB01.BetaShop.dbo.Net_OrderGetTracking @promaxKey select top 1 @kut=KutTracking from #tmp drop table #tmp when i execute it, it throws an error like 'Th...more >>

Hi all, can anyone tell me whether i can install reporting service on windows 2000 or not. if yes how? thanks in advance. ...more >>

If I do a insert into tblA (id, name) select (newid(), frstname) from cust do the colums I'm inserting into have to match the columns I'm selecting from? so should it be insert into tblA(id, name) select (newid(), name) from cust or will insert into tblA(id, name) select (newid, ...more >>

Hello, I've to send data to a url. Example : Send data to "http://targeturl/articles.htm/" How can I do that in Transact SQL ??? Thank's a lot if you can help me... ...more >>

I have two sql server instances on two different machines with the same db; on the first machine some tables need to be copied on the second machine, and on the second machine some other tables need to be copied on the first; all the tables are related with the others in some way. It is possible...more >>

any function can change the string "200706" into numeric 200706 Thanks ...more >>

Hi, We have the following scenario: Suppose we have following scenario. StoredProcA :- This SP begins a transaction A and inserts a row in TableA. Now We call a StoredProcB from StoredProcA and in this SP, We Begin Transaction B and insert a Row in Table B. Now...more >>

Hi, I'm trying to change database from restricted_mode to multi_user mode. I'm running the following command: ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE If any user is connected to database, the above statement fails and gives the error as shown below: Msg 1222...more >>

Server A SQL 2000 SP4 Server B SQL Server 2005 SP1 They are connected through internet Server A and B are geographically located around 2000 miles apart Statement at Server A : select * from serverB.databseB.dbo.tableB No WHERE Clause in the statement . Currently 1 miiilion row ...more >>

I have a web application that uses a database and it does of course have new requests all the time. When I attempt to take the database offline, it runs forever. How can I stop new requests to the database, let existing requests run to complete and then get the database offline? Olav ...more >>

Hi, We need to migrate Access 2000 databases to SQL2005. the table names and the column names have spaces in them in the original access database. Acesss reports will be used against SQL2005 instead on Access after migration. An initial test of the reports againts a migrated SQL2005 datab...more >>

'LogActivity' table - Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. How can I increase the timeout value? Olav ...more >>

I need a field to hold the maximum amount of characters as possible. Which data type is best for such a requirement? I've used nvarchar in the past, but wanted to know if there are any advantages when using ntext instead of nvarchar. thanks for any insight. ...more >>

Having been in software industry for more than 9 years, I feel this is the time to share my little knowledge. I have started my own blog called SQLTips. This blog is totally focused on SQL Server 2005 tips and tricks and provides solution for day to day issues in SQL Server. I am trying to cov...more >>

We have data stored in a datetime field that is in the Universal Time (UT) Zone. We need to convert it into Pacific Standard Time or possibly Pacific Daylight Time. Is there a function for converting time from UT to PST or PDT? ...more >>

Hello, I have two tables: Articles and Comments I need to Select some articles (no more than 20) and for each article I need to select its Comments. I am populating a .Net class with the data. As far as I can see, I can do this in two ways: 1. Use Inner Join in my SQL stored procedur...more >>

Hey there I need to find the most popular course,, so the course(s) with the most amount of stdNo,, and not display the others. So far ive tried this SELECT R.courseID, C.cName FROM Register AS R INNER JOIN Course AS C ON R.courseID = C.courseID WHERE (COUNT(*) > ANY (SELECT COUNT(*) AS ...more >>

Hi I need to create a User DSN based on SQL Server driver under Data Sources (ODBC). As I need to do it on multiple PCs, is there a script or another automated way to do this quickly? Thanks Regards ...more >>

Hello, (SQL Server 2000 (s3)) I read this great article but i need let me say a little bit more http://www.sommarskog.se/dyn-search.html CREATE PROCEDURE search_orders @orderid int = NULL, @fromdate datetime = NULL, @todate...more >>

I have a strange problem. Sometimes I get a "ERROR [HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error" in my code (see code included below). How can the below code cause a Function Sequence error? Olav public static System.Decimal LogActivity(int userId, string ipAddre...more >>

Hi all I have a table name Group that has a relation with itself. GroupTable Code int, Name varchar (50), ParentId int in this table Code has a relationship with ParentId. I want to concat Name column heirarchically. thanks in advance ...more >>

I want to create a database if not exists, I tried something like: if not exists (select * fron master.sysdatabase... name = N'Employee) return else begin create table1... create table2... end go However, I got a error something like: cannot locate entry point.... ...more >>

I'm new at this and need some help. I tried to setup a situation where I delete the children related to a parent when a parent is deleted. But I am getting an error. I have changed the names of the columns and tables to make it easier. Can anyone tell me what I have done wrong? Thanks, Flomo ...more >>

I have the below function... CREATE FUNCTION ja.fctMI ( @O VARCHAR(32) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @r VARCHAR(8000) SELECT @r = ISNULL(@r+ Char(13) , '') + Instrument + ', ' FROM MI WHERE OrderNo = @O RETURN @r END - - - - - - - - - - - - ...more >>

When programming logics require "if then do something" and in the "do something" there could be another if then.. and so on, making the codes very difficult to read. It is a general programming skill to combat this by having one main function to call other subroutines. In traditional progam...more >>

Hello, I created an Update procedure. >From within this procedure I need to execute the procedure named syncronize which has Id and Text as inputs and Feedback as output: ... @Id UNIQUEIDENTIFIER, @Text NVARCHAR(MAX), @Feedback INT OUTPUT ... SELECT @Feedback = @@ERROR .....more >>