I have big problem. In database MyDB.mdb I have table for example: ORDERS with colums: - Id int - Name varchar(20) Also I create same table in SQL Server 2k. I can manualy export this data from mdb database to SQL Server. But I want to make this more automatic. Is is possible? To import...more >>

Hello Experts, I make a connection with .Net Sqlclient Data Provider to my server and I adjust the min pool size = 0, but when I check the sysprocesses I see that 2 connections have been made. Why is that? am I missing something here? Conn. string "server=MYSERVER;user id=sa;database=MY...more >>

SQLLY challenged be gentle -- Trying to create code that will drop a table using a variable as the Table Name. DECLARE @testname as char(50) SELECT @testname = 'CO_Line_of_Business_' + SUBSTRING(CAST(CD_LAST_EOM_DATE AS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar), 4)+ ...more >>

I am a developer who works with MS SQL Server a lot, but I'm far from an expert. I am revamping an appointment scheduling system that allows for appointments to reoccur daily, weekly, monthly and yearly. Currently, I am saving the appointment date as the initial appointment date. Then when I...more >>

Hi, I am trying to locate documentation on using views across databases. We have serveral databases broken down by application, some shared databases and some location specific databases supporting multiple facilities using the applications. The DBA for our client is the one that set up the t...more >>

When we deploy applications, we have been using DSN's set up on the users systems. Then in the sqlconnection string, we go "dsn = xyz". I tried this with our new application, which is the first to be done with ADO.net and I get the following run time problem: "An exception 'System.ArgumentExce...more >>

I need a tool that creates a script to recreate a db, including tables' data. Does it exists freeware ? thanks!...more >>

Hi all, I have to find free disc space in a drive from SQL Query and i am using MSSQL200. Does any body know the command for the same. Also if i want to find hard disk space of a different machine on network, it is possible to get the data?? Also can i get the CPU/Memory usage data of our m...more >>

Hello, Dean Try this: select distinct c1, c2 into #tmp_1 from t1 select count(*) as cnt from #tmp_1 drop table #tmp_1 With best regards....more >>

How do you setup SQL Mail? I tried following the procedures on the online manual but I do not understand what "On the instance of SQL Server, start the mail client using the newly created mail profile." means. What is the mail client? I have already setup the mail profile but when I go to S...more >>

I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values. DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2) Error msg: The table 'Ta...more >>

Is there a way to create a text file (such as a Windows Notepad file) by using a trigger on a table? What I want to do is to send a row of information to a table where the table: tblFileData has only one column: txtOutput I want to use the DB front end (MS Access) to send the text string to the...more >>

I want to build query to return how many rows are in this query: select distinct c1, c2 from t1 But SQL won't accept this syntax: select count (distinct c1, c2) from t1 Does someone know how to count multiple distinct columns? Thanks. -- Disclaimer: This post is solely an individual...more >>

I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and another column as Virtual_Pack. I've to write a query to update Virtual_Pack column. The logic is as follows 1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6 Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virt...more >>

I need to append text to the end of my sql query results. For instance, my query returns a list of user names in firstname.lastname. I need to add @yahoo.com to each record. So the end result should be firstname.lastname@yahoo.com. What should by select statement look like? Any help?...more >>

Hi all I had some info on setting db owners and chaining so I can execute a dtsrun (many thanks to Dan for that) I have taken the comments on board and done some more research on the internet, but I'm still struggling. I am testing this on my personal edition, but when I run EXEC sp_db...more >>

We have a brand new sql 2k server. Its all up to date on service packs (both win2k and SQL). I went in to enterprise manager and added a new db. I can run sprocs and raw sql against the new db just fine from Query Analyzer. When I try to make a dts package that refers to my new db I get the ...more >>

Hi Everyone, I tried to bcp in the data file to a table in sqlserver 2000. The data file has got few blanks in certain columns. My requirement is all those blanks has to be converted null while bcp in the data. 1. I have tried giving the option -k - Didn't work 2. I have tried creating a ...more >>

i have a table with about 40 columns in access 97( terrible i knw, but its not mine and im not allowed normalize or change it) . i have added a column , which is a number , called year. it will contain 1997, 1998, etc. there are about 1600 rows in the table. is there anything like a correlated s...more >>

Hi, I have to determine the "standing" (WIN - TIE - LOSS) from confrontations between two teams on a contest. The table matchResults has fields cont_id, team_id and contest_result (int). TABLE matchResults cont_id team_id contest_result 1 1 3 1 2 5 2 2 4 2 3 4 3 3 3 3 1 4 What ...more >>

I have a couple of tables that look like this (not excactly but close enough): [Contact] id int fname varchar(50) lname varchar(50) [ContactPhoneNumber] id int number varchar(15) ext varchar(6) contact_id int priority int (indicates primary, secondary... numbers) type int (indicates ...more >>

Hello, I am trying to insert some date values into a table. I am using ODBC prepared statements to (potentially) improve performance. The statement syntax I am using is this: INSERT INTO date_tests (date_col) VALUES ( CONVERT(DATETIME, ?, 121) ) The table is defined as "CREATE TABLE dat...more >>

Hi I have a question regarding the SQL Server(SQL Server 7) port 1433. Some body is trying to hack into our Windows 2000 server through port 1433. Is there a way i can close this port? I tried using a tool called Ipsecpol.exe ( Internet Protocol Security Policies Tool). But when we run netsta...more >>

Why, in the process of creating a unique index, does SQL Server allow me to select the "Ignore duplicate keys" option? Wouldn't I just create a non-unique index if I wanted to ignore duplicate keys? I came across this fact while preparing for the SQL Server design exam....more >>

Hej There. We have a big problem. We have now for 4th years had a SQL Server without problems. But sutnely some of the primary keys are deleted. The subdata to the primary keys are not deleted. This is a big problem because This is a billing system. Recently there was over 300 primay keys del...more >>

Hi, I have the following sql statement. I originally had the statement with two INNER JOINS but in some situations was getting an error so changed the last INNER JOIN to a LEFT OUTER JOIN (as is seem below). This seemed to work but i am unsure why and would like to know in case it falls ove...more >>

Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to access them and automatically they can not continue their works. I have tried using the follo...more >>

Hello, I'm working with a number of databases. I need a way to determine if a foreign key with given name exists and determine what tables and fields are linked to it. Any ideas? I know I can issue the following statement to get a list of the fields that are constrained by a foreign key const...more >>

Hi, I have installed MQ SQL Server 2000 on my laptop and trying to use SPRINTA 2000 JDBC Driver (inetmssql.jar) to connect to the local db. I wrote a sample Java app to test the connection and I get the following error trace, DriverManager.initialize: jdbc.drivers = null JDBC DriverManager i...more >>

Hello! Does anybody know whether mssql2000 and emc mirrorvew _certified_ for joint work? (Mirrorview is a fc-based remote mirroring solution) I mean is it supported from the MS point of view to put mssql datafiles on emc mirrorview volumes? For example Oracle corp. has "Oracle Compatib...more >>

Hi I need some design info. Say that I need to create an educational lab for databases with SQL Server. I need all the users to be able to execute common SQL commands ( create - alter – drop table , insert , update , delete ) and each user have his own space ( users not able to see others da...more >>

Hi- I've got some issues with the ordering of search results. I've got 4 fields, (1, 2, 3, 4). Most often there is data in field 1. Sometimes there isn't and so then I'd like to look at field 2. I can accomplish most of what I want with this: select 1 as orderField, ISNULL(1,...more >>

Can someone help on this: I am just learning, and I'm connecting to the the northwindcs.mdf tables / open file is northwindcs.adp. This is the sample installed using msde, which is supposed to be mini sql server to learn. Please don't refer me elsewhere, here is what I'm trying to learn: If I ...more >>

Is it possible to use NEWID() as a parameter to a stored procedure in SQL Server 2000. I keep getting a "Line 1: Incorrect syntax near ')'" error. ALTER PROCEDURE dbo.StoredProcedure1 ( @x uniqueidentifier ) AS /* SET NOCOUNT ON */ RETURN StoredProcedure1 NEWID() go "Line 1...more >>

for some reason i dont get any errors, but i believe that my server is not running on port 1433. the tcpip protocol is selected in the network configuration utility as well as name piper. the port selected is 1433. but when i telnet to 1433 i get the same errors as i would if i telnet to a non ...more >>

I have a database with 44 user tables that had some data deleted. I have a 2 month old backup of the .mdf and .ldf files I can reatach to the Server (not the proper sql backup wizard method). Is there a way I can compare the data that is in the old database to the new and pull in the record...more >>

Take a table, where not all the columns are populated: CREATE TABLE #T (A int, B int, C int, D int) INSERT #T (A,B) VALUES (1,2) INSERT #T (A,B) VALUES (3,4) INSERT #T (A,B) VALUES (5,6) INSERT #T (A,B) VALUES (7,8) INSERT #T (A,B) VALUES (9,10) The values for C and D can be computed as f...more >>

I apologize if this is redundant. How would one fill an empty column with a sequence of numbers? The column exists in a table with aproximately 1000000 rows of data. I believe in oracle the following would work: update foo set bar = rownum; ....but 'rownum' does not seem to exist in mss...more >>

To support an application, I am integrating it with with Sqlserver 2000. The task is simple: get the data at regular intervals, and write it into a table dedicated for that application. One of the fields I receive is a date, and it is formatted as an ISO date. A sample value looks like this: ...more >>

I have a table; CREATE TABLE theLiterals ( theKey varchar (255) NOT NULL , theValue varchar (255) NULL ) INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\') INSERT INTO theLiterals VALUES('defaultShare','MyShare\') INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\') ...more >>

1. I'm trying to get the notifications working on the scheduled jobs. I want to email me when the job fails. I created an operator (dmalhotr) and given me as the email address. Here is the error what I'm getting when I'm pressing the test button to email myself in properties dmalhotr operato...more >>

Ecco il mio problema:devo caricare in un database di sql delle tabelle presenti in file txt la query per caricarle potrebbe essere la seguente: create table prova( codice varchar(22), rowid varchar(10), data_open date, in_attivo int, in_nota varchar(40), co_atc varchar(10)); load...more >>

I have written a utility that checks our stored procedures for potentially incorrect code (eg = null as opposed to is null, unused variables/parameters etc). It also ensures that parameters/variables etc match our naming conventions and data types/sizes are the sames as the corresponding type in...more >>

Hello For my client, I need to generate reports from the information stored in the database. The client has fixed format forms (on paper e.g. US Customs forms etc). Will I need to redesign the forms in the application and then show the information? Another approach is to scan the forms a...more >>

hi, I would like to store windows passwords and usernames in database. Please tell me where to start? What database can I use? Can I use free microsoft database? Thanks Bart...more >>

Hi, I have a problem. I have two computers (clients), one with Windows 2000 and second with Windows XP. And a third computer (server) with a Windows 2000 and MS Sql Server 2000. I have a TABLE with two rows: 'row' and 'row ' (space). And SQL: 'select * from TABLE where row = 'row' If I...more >>

Hi guys, I have hit this bug more than once and was wondering if anyone else has ever seen it? SELECT A.nId ,B.nId FROM Server1.myDB_1.dbo.TableA A LEFT OUTER JOIN Server2.myDB_2.dbo.TableB B ON B.nId = A.nId WHERE A.nId IN (SELECT cId FROM Server1.myDB_3.dbo.LogTable WHER...more >>

I'm on the IS team of a medium-sized non-profit with international reach. We're trying to make some decisions regarding our Web server and database server as we expand our web site to have more dynamic content. Currently the database server houses all data pertinent to the organization (membersh...more >>

Hi! guys, I have a SQL agent job fails because it gets 10 warnings when it runs a stored procedure. These warnings are trivial and can be ignored. Can I make it ignore these warnings and proceed? I think there is some setting I can do to change the default behavour of an agent job regarding w...more >>