Hi, I need help with a problem regarding data saved in a parent-children model table and a report I need to build upon it. I've already tried searching for topics about parent-children issues, but I couldn't find anything useful in my scenario. What I have A Microsoft SQL Server 2000 database server. A categories table, which has four...
I am extracting data from multiple tables. mt query is as follows: SELECT p.Record_Num as RecordNum ,p.GCD_ID as GCDID ,p.Project_Desc as ProjectDesc ,p.Proponent_Name as ProponentName ,st.Station_Name as StationName ,p.OpCentre as OpCentre ,s.Sector_Name as SectorName ,p.PLZone as PLZone ,f.Feeder_Desc as FeederDesc ,d.DxTx_Desc as DxT...
Hi everyone, I've got a problem with using for xml explicit in SQL Server 2000 (so I can't use the new path() stuff from sql 2005/8) Essentially I have two tables and the XML structure I want to have is <xml> <table_1 field1="foo" field2="foobar2" field3="foobar3"> <a_row_from_table_2 field1="goo" field2="goobar2" field3="gooba...
I tried to use text or ntext as the variable's type but they are disallowed. Is there a way to bypass this? ...
We have a function that pulls the date from the first 8 characters of a string. Because of the way this system was written, the users are told they have to enter the date in an 8 character format in the field. For example, 12/06/10 must be entered. The way the original function was written, 12/6/10 or 12/06/2010 would generate a wrong...
Is there a way to use the SQL Model (dbml) builder in VS2010 using SQLServer2000? It works fine in VSExpress2008 + VS2008 but throws an "Upgrade SQL to 2005" error in VS2010 which seems a tad unreasonable. ...
I am trying to make the following work: declare @ActTable as varchar(1000) declare @cPK as VarChar(100) declare @SQL as nvarchar(2000) declare @FK as VarChar(100) declare @FKRef as VarChar(200) declare @TblRef as varchar (100) create table #temp ( M2MTable varchar(50), PK varchar (100), FK Varchar(100), FKRefTable Varchar(50)) insert...
hi, I have some C#/ado.net code which acts differently with SQL Server 2000 and SQL Server 2008. SqlConnection con = new SqlConnection("connecstionstring"); SqlCommand cmd = con.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "MySp"; con.Open(); SqlTransaction trans = con.BeginTransaction(); cmd.Transac...
i'm going to running some queries against an SQL Server database, followed by a delete. Ideally all this happens inside a transaction (i.e. atomic). But practically, because the data has long since been purged from the buffers, SQL Server will have to perform a lot of physical IO in order to complete the transacted T-SQL. This can be a ...
i have a stored procedure that performs a join of TableB to TableA: SELECT <--- Nested <--- TableA Loop <-- | ---TableB At the same time, in a transaction, rows are inserted into TableA, and then into TableB. This situation is occasionally causing deadlocks, as the stored pr...
Hello all, First of all, I don't want to use a "join" because that will make my query longer and difficult to read. So what I need to do must be withing the same SELECT statement. My columns in myTable are A, B , C , D , time, ID and H H columnd tells if a record is 'Open' or 'Close', here how my query looks like. SELECT A, B, C, ...
I've got some code that runs all lovely on SQL Server 2008, however I'm forced to try it on SQL Server 2000 server, where it falls down. Basically I'm looking to combine two columns, with a comma in-between. SELECT COALESCE(cardesc1, '') + ', ' + COALESCE(cardesc2, '') AS "Car Summary" FROM macros; ...
If the itemcount property does not exactly match the number of indexed rows is that a problem? Is there a numerical way I can ensure that I have a complete full-text-index? update: the property fulltextcatalogproperty('database','itemcount') does not equal the rowcount for the indexed tables. It is off by a few thousand. Does that indic...
I have SQL server 2000 dev edition. But it isn't compatible with my new win7 pro 64 bit computer. Is there a free version of 2000 that I can install on my 64 bit pc for development? As I understand it, I can restore a 2000 database to sql server 2008. But I wont be able to restore the database back to the 2000 server after making any ch...
I'm trying to insert row by row in a table about 100k records.. I get this error after some 140 or so.. Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated. In this case the primary key is an IDENTITY column. I do not include that column in my IN...
Trying to run a cross-server update: UPDATE cmslive.CMSFintrac.dbo.lsipos SET PostHistorySequencenNmber = ( SELECT TransactionNumber FROM Transactions WHERE Transactions.TransactionDate = cmslive.CMSFintrac.dbo.lsipos.TransactionDate) Gives the error: Server: Msg 117, Level 15, State 2, Line 5 The number name ...
i have a query that retruns rows that i want, e.g. QuestionID QuestionTitle UpVotes DownVotes ========== ============= ======= ========= 2142075 Win32: Cre... 0 0 2232727 Win32: How... 2 0 1870139 Wondows Ae... 12 0 Now i want to have a column returned that con...
Hi, I am currently developing completely on a local machine and have a DTS package that on completion should run a local exe file that compresses the csv files that were just imported. If I run the package in the designer or from the list of packages then all is fine and the files are compressed. If I run the file from SQL Agent as a ...
Hi folks: I see a node named "Bookmark Lookup" in my execution plan. What does it means? It costs most among steps inside the plan. Any suggestion to optimize it? ...
Hi, I tried to turn on the Activity Monitor using SQL Server 2008 Management Studio (SSMS) through the options window of the application (Tools | Options | Environment | General | At Startup). I restarted SSMS and I am getting the following message: "This operation does not support connections to Microsoft SQL Server Standard Edition ...