I created 2 tables with one to one relationship. if I add a record in table A, how does table B record get created? Does SQL do this automatically because it is one to one relationship? or do I need to create a trigger? if i need a trigger, how do I get the ID of new record to create the same ...more >>

I have an sql query that has specific criteria (like state='PA' or state = 'NJ'...) and would like to be able to have the user specify the criteria dynamically either through the web or from MSAccess or another tool. The query also does a GROUP BY the state and other variables that are part o...more >>

Database log of my DB is around 2GB. The database is using FULL recovery option. I want to reduce the file size of the log cause it takes up a lot of space. I'd do a full database backup, then backup the transaction log as well .... both backup performed with a check on the option "clear in...more >>

Is there any difference in the order of parameters in the where clause? Does it add any speed to declare the least refined first or last? ...more >>

I'm having this query: SELECT ss.subscription_id AS SubscriptionId, s.id AS ScopeId, s.[name] AS ScopeName, s.base AS ScopeBase, dbo.iqGetShapesByScopeAsString(s.id) AS ShapesAsString FROM subscription_scope ss, scope s WHERE ss.subscription_id = @subscription_id AND ss.s...more >>

Hi, I have a problem using serializable transactions. In one transaction I select a single specific row from a table using a where clause (this causes it to acquire and hold a range lock). In another transaction I attempt to insert a new row into the table (which does not match the first tran...more >>

Hello all, I'm using SS2K on W2K. Brieffing: Many months ago, I created a stored procedure only used by those with admin rights in SS. Now, someone else (without admin rights) has to run it. I gave him rigth to execute the SP but, at the second and more execution, he got a error message ...more >>

In VB 6, using ADO, SQL Server 2000 In my VB program I use the connection object (abc) to begin a transaction. abc.BeginTrans I then, in my VB App, using ADO, execute multiple stored procedures inserting records into temp tables ##Meetings, reading them, and then finnaly executing (from ...more >>

I've got a tool that accesses syslogins to pick up some information. When I run the tool, I get the error message that sasys that my login does not have sufficient permissions to read syslogins. If I run under my admin ID, everything is fine. HOWEVER, the intention is that this tool will be us...more >>

Within an SQL Script is it possible to send a SQLDisconnect command? (server is MSDE) I have used sp_dropserver, sp_droplinkedsrvlogin and do not get a SQLDisconnect. Each time I run the script a new nsql32.exe process loads up and they accumulate. use Sales Go sp_dropserver 'M', ...more >>

When I restart a machine, the following message appears, note that the SQL Server status icon in the server tray has a blank circle: The network path was not found. When I double-click on the SQL Server status icon in the system tray and select the SQL Server from the SQL Server Service Mana...more >>

Hello I have 2 procedures setup in master database, sp_RebuildIndexesMain and sp_RebuildIndexesSub The Sub just shows and execute DBCC commands for passed database context sp_RebuildIndexesSub(@listOnly bit=0, @maxfrag Decimal=30.0) This runs fine if I do pubs..sp_RebuildIndexesSub Howe...more >>

First off, sorry if my cross posting offends anyone. I'm posting this in Access and SQL Server groups - not sure which one is appropriate. I have a relatively simple ASP.NET/VB.NET application that is now hitting an Access 2000 database over an intranet. We have to migrate the database to SQL ...more >>

Hi, I was hoping someone could help me with what I'm sure is a very simple problem...I just can't seem to find the syntax! I'm wanting to update the rows in 'tbl_consolidate' from 'tbl_hold', but working through the records in 'tbl_hold' in the order of dates in a date field, rather than th...more >>

I am getting a failure on the db backup job of one of my maintenance plans. It is coming back with the generic error message of, "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." I then checked the Database Maintenance Plan History page, but this shows all the steps ...more >>

hi all, we have a SQL2000SP3 runing in W2K3. The application is JDEdwards. recently I've observed that once in a while (about a few hours), there would be a process from the ERP application that holds quite a number of extent locks in tempdb, can be as high as 10000 locks. when I run sp_l...more >>

Does anyone know of any differences between queries in SQL 7 and SQL 2000? I have a collegue that says there could be "subtle" differences in the output, though I cannot find any documentation of this. Any link to prove him wrong would be very helpful. Thanks. ...more >>

I've only ever used built in SQL Server tools but have been looking at log tools. I looked at Log Explorer 4.02 from lumigent.com and was very impressed with what I saw. The prices tag per instance was a bit hefty I felt though. I've been over to LogPI.com and am reading some of the "features" I...more >>

I am formating text by adding a hyperlink that looks like this: "https://IQuoteSummary.asp?vid=" & {v_L.VersionId}&"&lid=" & {Locations.LocationId} Both database fields are type number The url enters the address: "https://IQuoteSummary.asp?vid=1,521.00&lid=12.00" It works when I run the ...more >>

I'm looking to find out how I'd go about setting up a database where a visitor to my site could punch in their postal code, and find out how far they are from another postal code. For example, AutoTrader has this feature I believe to tell you how far the vehicle is from you. Dating sites hav...more >>

Hi i need a stored procedure in an sql server, that inserts a query and returns a int value. the int value is an id made of a auto increment (identity(1,1) field. i have no clue how to do that. ...more >>

Hi All I have a question about generating dynmamicly If Update() statement in a trigger.. in My db, there is a table that holds some column names of an another table. for example; Columns Table-A: Col1, Col2, Col3, Col4,Col5 Table-B: Col2, Col5 (The selected columns of Table A) Then...more >>

Gurus, Here is what I ma trying to do. I have numeric expression stored in a table column. for e.g. @a + @b + @c. I supply values to the variables at run time and want them to be computed at run time as per the expression in the column. the stored procedure works fine but it gives a silly err...more >>

Hi All, I am trying to restore a database from backup on another system [my client's] but I had to follow some weired steps. Step 1. Restore the database from EM 's GUI. [Users as on My machine get restored but their logins are not activated. So I have to follow the next steps] sp_helpus...more >>

Hi, I have 2 instances of databases in 2 different cities.Data entry / updations are going on at both the sides.At any point of time data in both the databases should be same.Currently every alternate day, i am performing update / insert / deletes on incremental basis on both the sides. C...more >>

Ei guys I’m Ryan from Philippines, I need your advice and recommendations about Windows 2003 Server and SQL Server 2000 installation. Well, here’s the scenario. First is for windows 2003 server: We have a windows 2003 server that is a Logon Server and we have at least 90 workstation here in o...more >>

Hi I have a Platinum database which stores dates in integer form e.g the date is formatted as below: Column_name Type Length Precision ------------------------------ ------------------------------ from_date int 4 ...more >>

I get this error when I try to create the following tables: ERROR 1005 (HY000): Can't create table '.\haps\transcript.frm' (errno: 150) create table teaching ( ProfId int, CrsCode varchar(10), Semester varchar(10), primary key(ProfId, CrsCode, Semester) ) engine=InnoDB; create ...more >>

Hi all, I have an issue on querying against UTF-16 encoded characters in SQL2000 database: For example the "L=F3pez" is saved into database as "L=C3=B3pez" (due to the UTF-16 encoding); somehow, when I query data with conditions of "like 'lop%'" or "like 'L=F3p%'", the row of L=F3pez would no...more >>

Hi, i have a table like this CREATE TABLE dbo.test ( num int NOT NULL, ename char(80), eadress char(200), archived char(1) PRIMARY KEY CLUSTERED (num) ) create index i_archived on dbo.test(archived) the are 500000 rows in this table,...more >>

I have a SqlServer 2000 running 7 databases. One in particular is slower than the rest specificaly on certain tables. It seems the problem comes from the volume of data, but I don't think 3000 lines can be considered a big table. I have indexes on the principal keys, but it did not help. ...more >>

Hello, We have an ADO.NET application using .NET version 1.1.4322 SP1. It is calling stored procedures in a database that it never written to. The only thing the stored procedures do is a select statement on a few tables that are joined. In the last few weeks we have experienced issues wher...more >>

I'm updating my local database installation using my netowrk login as the id under which SQLServer is being update (Administrator on the operating system and SA role on the database server itself). Has anyone encountered any problems and what is the work-around? ...more >>

Assistance Please I have a couple of scheduled jobs that are failing to e-mail notifications on completion (via SQL Agent Mail). I can send a Test e-mail without any problems. I can run the jobs manually, and the e-mail notification works effectively. Left to run according to the schedule...more >>

I have a Users table which contains a nvarchar column LastName. The first query below returns me all the records with LastName containing "opez" or "=C3=B3pez" in them. But the second query returns only records with "lopez" in the lastname and not records that contain "l=C3=B3pez". Can anyone pl...more >>

Hi, I have a sql 2000 server with 8 processors, server settings are as default. I read on Technet that it is good practise to remove the highest no. processors from being used for parallelism, corresponding to the no. of NICs in the server. One of our 3rd party developers has recommended only...more >>

Hi I'm currently having to design a database for a recruitment agency that's just started up and have one area where I'm a little unsure where to go. Basically I've implemented the 'standard' Customer, Contacts tables linked on CustomerID, and also have CallRecords (for phone calls etc mad...more >>

I have a table with structure below : Table_A { PGradeID int IDENTITY(1,1), PName varchar(20) } If I delete all records on Table_A, and then fill some records, PGradeID will no longer start from 1 anymore, everybody knows that. How to change it so PGradeID will start from 1 after all rec...more >>

Hello, It might be a basic question, but there it goes: I have a store procedure that first checks if the record exists and, if it doesn't, inserts the new record. Is the scenario below possible? (thread1) USER1 -> check if record "abc" exists (thread2) USER2 -> check if record "abc" e...more >>

Hey, Another 'must be simple' question.. I'm wishing to convert a binary field into a textual representation of that binary data. i.e. come out with what the query analyser would display, and not try to convert the binary into ascii/whatever encoded text. Cheers for any clues, Chris...more >>

If I on a remote hosting server have db_owner rights, do I then also have db_securityadmin and db_dlladmin rights? BRGS, TCHillII...more >>

Hi Group! I am struggling with a problem of giving a date range given the start date. Here is my example, I would need to get all the accounts opened between each month end and the first 5 days of the next month. For example, in the table created below, I would need accounts opened between ...more >>

I have not used the copy objects wizard that much. I used it today to copy 4 views from my dev box to production. It copied the views, but also wiped out my data in all the tables that the views are built around!!! In production!!!! Can someone provide me some insight into why this happened...more >>

What does "conditional" mean as a command? sp_who2 reports this for some sessions, along with "insert" etc. Thanks, Jim Geissman ...more >>

Sorry for the piece-by-piece nature of this post, I moved it from a dormant group to this one and it was 3 separate posts in the other group. Anyway... I'm trying to bulk insert a text file of 10 columns into a table with 12. How can I specify which columns to insert to? I think format file...more >>

Hi everyone, I just need a bit of advice as to where to start tackling a problem, if thats possible - thanks very much. I need a single stored procedure to make several inserts into my msde database. There will be two arguments to the stored proc. The first is a title argument which nee...more >>

SQL Server 2000 Just curious - was wondering why some SPIDS are left hanging out there for up to several weeks. There are no errors or anything. It looks like normal processing. Thanks, Craig ...more >>

I have an identical SQL database on two machines (my machine and a web server) that links to a database on a third server (S3). When I execute a stored procedure on my machine that accesses a database on S3, it always runs without a problem. However, when I run the same stored procedure on the w...more >>

Hi in T-SQL, (how) is it possible to concatenate 3 (varchar) fields into one; either in a SQL query or through a calculated field (or using a view, if anybody can explain to me how to use views), according to the following rules: { first 30 chars of Trim(AttributeVal1) if resulting s...more >>

Env: SQL Server 2000 It actually waited for 50 seconds instead of 5, tried WAITFOR DELAY '000:00:005' and WAITFOR DELAY '000:00:5' respectively, and got same behavior. Bug or ? TIA ...more >>