Help I am getting the following error and I cannot figure out why - Maybe I have gone blind? "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" Any help would be appreciated Mark Moss /* ---------------------------------------------...more >>

Is it possible to create a stored proc which references a non-existent field without getting the "invalid column name" error? The field will only exist in one or two production databases (not in all) but we still want the stored proc to exist in all databases so that users in the future may ad...more >>

Gentle / Ladies I am converting over a computer system from HQL to SQL and it has some statements that I need to convert. If you look at the below line of HQL code you will see the 'greatest' function which as used prevents the divide by zero error under HQL. It works on the absolute valu...more >>

I've tried to schedule my job, but sometimes completed and sometimes it doesn't work or failed. Is there any probabilities cause my job failed? I've schedule my job at 00.00 AM every day. But when it failed, I will execute the dts manually and it is always succesfull. Thanks before. ( I am st...more >>

I tried to delete all record in Table X but sometimes it takes very long time and sometimes just like hang, the status is "Executing Query Batch" why could this happen? ...more >>

I have a Names Table. Multiple applications can SELECT query the table in many ways. This can include a direct query (ie SELECT FROM myNames WHERE ID=@id) OR though an INNER JOIN where the SP returns the name in the SELECT statement merely though the relationship. ID Name NewI...more >>

Hi there, I need a sql query to get the next or previous sequential id where status >= 1 or status <= 3 given a current id. current id = 145 note: the gaps between where status is = (1, 2, 3) could be tens, hundreds, or thousands of record. example below: table structure and data: ...more >>

Hi, I am using VB and ADO to connect to a MS SQL db. I am wondering what is faster: using SQL in vb or calling a SP on the db? I do understand why it is better to use SP because I can change the SP and have minimal affect on the client (less chance of rebuilding client). I am just curiou...more >>

I have read and understand the advantages of using Windows Authentication with SQL Server. My question is this I understand and have used Windows Authentication on a standalone computer and on a domain. However, how do you setup and use windows authentication on a peer-to-peer network or on...more >>

Hi, I have created a new database with a new table and when I try to access it via ADO I get the InValid Object Name Error. It exists because I see in Enterprise manager and I verfied the table name. Any ideas? Thanks ...more >>

I have a table (tblstudent) with four fields, Fname, Lname, City, Zip. I want to run a DTS export nightly that export the records out to a file. The kick is that I would like the text file to list the fields on their own line like: Fname Lname City Zip Instead of across like: Fname, ...more >>

Not sure if I have the right term in my Subject, but I have written an application using VB6, which stores employee transactions in a SQL database. I had originally designed the app for independent use in a standalone PC in our remote offices but was then asked to expand its use to our corporate ...more >>

Hi, if anybody can reslove this sql query : Fld1 Fld2 === ===== 1 100 1 200 2 400 2 401 2 402 ================= I tried but cud not suceed to bring this output : fld1 fld2 =============== 1 100 2 ...more >>

Hi guys, IF transaction A contains transaction B, and I have a while loop outside of transaction B, inserting row by row. but if Transaction B failed for one of the row, it would rollback Transaction B but just that specific row, correct? Or will everything roll-back? ...more >>

I need to do an aggregate query against an integer data type, and want to return three columns based on the value: 1, 2, or 3. If the field name is "Action", I should be able to sum (action=1), (action=2), and (action=3). First I tried the following: SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(...more >>

Or perhaps at least an independant generator?!? WTF?!? Some fool drove a car through a phone pole last night, about 4 blocks away from work, and it killed the whole town. Now I have to mop up all the records from the 6 or 7 batches that ran last night -- oh, and since SQL Server is sche...more >>

Hi All, I have a batch job making groups of inserts and I need to reset identity value to create a compsite unique key to prevent dublicate records. Column are YEAR(like 2005), MONTH (like 08) and data columns from a text file. Data columns are almost identical. So I want to add an ID (identi...more >>

Hi, Situation: In joining the two tables below, I need to find out which country(s) represent all three Products. In this case, the answer would be 'FR' because it is associated with product 1, 2 and 3-- just not sure how to get it. Thanks! Tables: Products: ProductID, Product SKU, ....more >>

Environment: SQL Server 2000 on Windows Server 2003 How can the resultant output be explained? CREATE TABLE Target(SomeNumber int) INSERT INTO Target VALUES (1) INSERT INTO Target VALUES (2) INSERT INTO Target VALUES (3) INSERT INTO Target VALUES (4) UPDATE Target SET SomeNumber = De...more >>

Hi there, I have got this problem. I want to know which concrete combination of 7 variables gives the maximum value of some non-linear function. I have created 7 tables (per 1 column) with all possible values of each variable (e.g. for the first variable: 0.00,0.01,0.02,0.03,...,1.00) and the...more >>

Greetings all, We're in the process of migrating from a Unix based DB to Sql Server so I'm on a learning curve. In our Unix database, for various reasons, it was worth the effort to create Delete Handler stored procedures that would be called from a table delete trigger. I'm trying to du...more >>

Is there a way to capture the results of sp_helpsrvrolemember into a table? TIA, Joe...more >>

I'm trying to discover why the Query Analyzer doesn't have SQL statement generation facilities similar to creating a New View via the Enterprise Manager? Am I missing something in the query analyzer? And, if I'm not missing something, does SQL 2005 bring these together into one smart tool?...more >>

I have a script that is looking at IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Security_Code_DE' AND COLUMN_NAME = 'ActiveProductBitMap') I can see in enterprise manager that the db I restored does not have a column ActiveProductBitMap, nor sho...more >>

I have a job that has 4 steps, all of which have to run. They are all T-SQL scripts. How can I get the job to report failure if step 2 fails but step 4 doesn't?...more >>

Just a general note. I'm a .NET developer and enjoy the setup that visual studio gives me when editing and working with my projects. I have a dual screen setup and like to build my environment around it to increase my productivity. I noticed that in the release of the new SQL 2005 Managemen...more >>

Hi, I have just finished a BCP ,with 35 columns, it extracts data from a SQL SERVER Database to a text file, using a format file. The next step I will add more 58 in it, but for my surprise when I run this BCP (with 35 columns) it appears the message "Query hints exceed maximum c...more >>

I'm looking for a way to check to see if a database actually exists before trying to open it. Can someone tell me a programmatical way that I can determine the file name. I have the path, but some files are .mdf, some are ..SQLDataFile extensions and so there might be other extensions. I wa...more >>

i would like to create a Store procedure that can put the following information into a database. 1.) Version of SQL on that instance 2.) Version of Windows including SPs (can i get this from SQL???) 3.) Size of each log file on the server instance I would like to out this information to a...more >>

does anyone know why this statement isnt working INSERT INTO RDKCALLHIST (CusId, CallId, CallDate, Completed, Comments, DateCreated, DateModified) SELECT CusId, '12345' AS callid, CAST(CusMisc2 AS datetime) AS calldate, 0 AS completed, 'N/A' AS comments, GETD...more >>

Is there a way within Enterprise Manager to print the contents of a Table??? I could of sworn there was a way...I think I just forget. Thanks!...more >>

Hi all, We perform log shipping on a few databases, and recently we've had some periodic restore errors. These errors seem to last for a little while, and then they are cleared up and the log shipping continues along on its merry way. Note: we are not using the log shipping solution built...more >>

Hi, I have the following DateDiff syntax SELECT DATEDIFF (Month, tblPDT.pdtDate, tblPatient.pntDOD) as 'Survival' The idea is to calculate the length of time in months between the procedure date 'pdtDate' and when the patient died 'pntDOD'. And the above works ok in doing this as long as...more >>

I have written an application in Microsoft Access. I'm currently using SQL Server 2000 as my datatabase, and use SQLDMO to perform various functions like Backup, Restore and copy. Now I want to create a new database from a template database which I have backed up to a template.mdf file. Now I ...more >>

I think it is possible to read text file using OpenRowset What is the provider name that I have to give? Madhivanan ...more >>

Is there a way to programatically detect (in SQL) the IP the SQL server is using or the port number it is broadcasting on? Thanks, Dean ...more >>

Dear all, I would like to know how could I suggest to Microsoft the possibility to open/create a new community in one language (real world, I mean, no programming language) which now doesn't exists. I don't know which are the main steps or if my request is simply ridiculous due to, in ano...more >>

Dear all, It had be allocated at c:\program files\...\Sql Server\tools\80\bin but I haven't there, in fact it's out of my workstation... I've tried to copy it from another workstation but when I launch that .EXE appear a empty dialog and nothing else. Any advice or comment would be great...more >>

how or what function can we use in sql server 2005 or 2000 to return the corresponding row number of a record in select statement for example SELECT name, xxxxx as Number FROM TableX xxxxx - is the function or keyword that returns the corresponding row number of the select statement....more >>

Hello, I'm making an SQL script that has to drop some columns. The problem is that the column has a default value and therefore I can't use the DROP COLUMN directly. I've got to drop the 'default' constraint, but the problem is that we have to use this script on different database and then ...more >>

Hi Friends, In my SP i have 3 insert statements that inserts record into 3 different tables. If any of the inserts fail, I want to roll back any other inserts that is executed. how to do this. please give me an example. example: insert into abc values('ert','ert') insert into xyz value...more >>

Hi, I have a basic query question and hope that someone can clarify for me. 2 queries... #1 \\\ SELECT * FROM Table1 AS A INNER JOIN Table2 AS B ON B.Column1 = A.Column1 AND B.Column2 = A.Column2 /// #2 \\\ SELECT * FROM Table1 AS A, Table2 AS B WHERE B.Column...more >>

Hello, i need to query a database and get the list of all primary keys wrt all the user tables in the database. I dont know as to what property field of syscolumns mark a field as a primary key. How do i know that the field in the table is a primary key ?? thanks ...more >>

Hi, I'm looking for a way to turn csv like 4,5,6,3 into rows without using UDF, so it can be used in pure T-SQL only. I want to use it in a code generated sql string, that will use these values with an IN statement. for example: if column VAL contains 1,2,3 I would like to write somet...more >>

Hi MyTable: panusers userid varchar(10) department varchar(100) pwd varbinary(50) ucreatedate datetime Code in ASP (gives error): insert into panusers (userid,department,pwd,ucreatedate) values ('dnkmca','dnkmca','sales',convert(varbinary(50),'sp123X'),c...more >>

Hi, I need to delete data from child table when I delete data from parent table. I think trigger can be used in this scenario. I want to know your views on using trigger for this purpose. And can someone give me a sample on how to do it using triggers. Thanks Kiran...more >>

hi, My log file is of more than 2gb, i want to shrink the size of this , without taking my server offline. i've tried DBCC SHRINKFILE ( 2, EMPTYFILE ) still no use. This is the info of my database Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Vers...more >>

Hi, I have a little problem with respect to converting UTC time to local time. What I am seeking is a function that can convert a UTC time to local time taking day light savings into account. I have a client program which communicates with a database server (MS SQL Server 2000 SP3) - the cl...more >>

Hi, I need to connect SQL server to a remote machine. We got 2 options either to connect it thru a firewall ( allowing only our server ip and the specific PORT) or VPN. Can anyone tell me which is the better one ? What are the adv of each of these ? I am very new to this area? and How to conne...more >>

I have an application that needs to check users' log on credentials from a web front end. The web front end passes the user name and password to a stored procedure and, if the stored procedure finds someone with those credentials then it returns the user's ID. Trouble is that SQLServer h...more >>

I have a DOMAIN\group account in sql server, is it possible for me to query what users are in the group? ...more >>

I've tried to install MDAC only at Win 98 (MDAC_TYP.EXE). But my program is still not able to work. My Program use ADO to connect SQL Server 2000. So what do i need to install again to my program can work/run? ...more >>

Hi, I want to read the .LDF for a database without making that database = offline. ..LDF file contains all the transactions in that database. Is there any documented/undocumented approach? Any help is appreciated. Thanks Pushkar...more >>

We are having problems when sending email through sql mail from sql agent when a job fails. The email address in the send to list is someperson@someplace.com but when the other people on the distribution list gets the email that person's email address reads someperson@someplace.comm or somepers...more >>

Hi all, I know that I can create a script with only SP, View etc information. But what I want to do, is create one file that has Tables, views, triggers, and stored procedures, but on the items starting with a specific set of characters. Is it possible to do this? Thanks ...more >>