I am using SQL Server 7.0 SP2.
I've got a table x that has fields say:
id1 id2 a b c d e f
(each field name is separated by a space.
id1 + id2 is the primary key combination for this table)
If there are say 100 records in this table I can write a SELECT query like:
SELECT (a + b + c + d) AS Item
FROM x
WHERE id1 = 'my_value1' AND id2 = 'my_value2'
and I could get up to 16 records (say) using this query. The no. of records I get is variable.
My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor?
I'll be grateful for any help,
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
Hi Chaps!! I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.
request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...
can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl' T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure
declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin)
I have to transfer some data from my SQL Server 7.0 database on NT Pro to a Unix server as a text file. This has to be scheduled as a weekly job. I can use DTS to transfer data into a text file , but is there a way by which i can export this file to a Unix server also ? Any suggestions would be appreciated.
Every day my database 12;00pm we will take fullbackup 1:00 O clock differntial backup 2:00 O clok again on diifferntial backup Every 15(fifteen)Min we will take Transaction log backups My database is crashed on 2:00 clock we will not take 2:00 clock Tlog backup we have taken only 1:45 Tlog backup.
Here my database is loss 1:45 to 2:00 clock data
we cannot take this 15min Tlog backup
Any body plz give suggestion on how to recover this 15min data plz help me it very urgent
Bonjour,CREATE TABLE [dbo].[MAND]([Mat] [varchar](5)[Dur] [varchar](1)) ON [PRIMARY]Mat Dur16030d16030i31217i10000d12000i10000d31217d35000d36000i35000dJe voudrais avoir le resulat suivant (i need this result) :10000 d35000 dCar ils ont tous les deux "d". J'ai beau faire un regroupement (groupby) par Mat avec un having çà ne marche pas.Comment faire ?Merci d'avance
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into. Thanks
Almost have my custom membershipprovider finsihed, but have ran into a small snag.Scenario:: 2 methods. CreateUser(blah blah) and ValidateUserName(string username). CreateUserWizard control calls CreateUser(blah blah) which in the start of the method calls ValidateUserName(string username). ValidateUserName runs a stored procedure on SQL 2k to check and see if user exists in the db. Returns a bool true or false. If true, then everything works correctly and returns a message displaying that the user exists, please enter a different name.Now the problem. If it returns false, meaning that the username doesnt exist and is available to use, it calls a stored procedure to create the record in the users db. This is where the problem lies. It does NOTHING but sit there waiting. But it never seems to time out. If I comment out the validating. It will add a record successfully.Im wondering if it has something to do with calling 2 stored procedures consecutivly????::CODE TO FOLLOW::
I have a couple of jobs set on 1 server. I want these jobs to be transferred, or rather copied to another server. Is there a method by which I can just point these jobs to another server, without having to manually create them again ??
I am having a problem replicating between 2 servers using 1 distributor. I have server X and server Y. Server X is a publisher, distributor, and supposedly a subscriber, while server Y is a subscriber and supposed publisher (using Server X as a remote distributor). My logreader task (off server X since it is the distributor) gives me the error message "Unable to connect to server Y". I was pretty sure it is possible to publish to the same server that is your distributor am I wrong? If I am not wrong and this scenario is possible any ideas on why it isn`t working and how to get it working?
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o( what maybe the problem?
I have a table w/ 17K line items. There are only 8.5k I want because each one is duplicated exactly twice. How do I get one of each of these line Items into a new Table?
I tried this:
Select DISTINCT * from DuplicateTable INTO NewTable
And it doesnt work. I am still getting the 17K transported over... any solutions?
I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?
Another question: should I explicitly write this to a table? If yes, how?
strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")
oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"
set oCommand.ActiveConnectection = oConn
strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"
i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End Function
I have a stored proc that creates a view so I can pass parameters. I need to replace the 120 with the variable @MEA. I can't get my quotes right to make this work. ALTER PROCEDURE dbo.spi_CallList (@strAgent nchar(4), @MEA int)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1') DROP VIEW qs_CallList1
SET @SQL = 'Create view qs_CallList1 as SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim) AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)) LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''') AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null) AND ((tsd_Claim.clfromdos)<GetDate()-120)) GROUP BY tsd_Claim.clinsnum ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC' EXECUTE(@sql)
Himy prob is like this..-----------------------------create table ax(i int ,j int)create table ay(i int ,j int)insert into ax values(1,100)insert into ax values(1,101)insert into ax values(2,103)insert into ay values(1,200)insert into ay values(1,201)insert into ay values(1,202)insert into ay values(2,203)insert into ay values(2,204)insert into ay values(2,205)select * from axselect * from ay--------------------I want to delete2 records(count of ax.i = 1) from ay.i = 1 and1 record(count of ax.i = 2) from ay.i = 2expected result :select * from ayi , j-----1 , any data2 , any data2 , any datanote: j is the temporary column in both table.thanksdishan
My question is i had a report with drop down list which contains nearly 200 items.when i select all items it's shows the error. I am able to get the result up to 19 items.
even i tried to filter at report leval using IN operator it is not working.can any one give some suggation.
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff
1)When i export the report the data i am getting in text format.how could i get tha data in number format by default. 2)my report has a total of 15 columns, so when user scrolls ,the first column allways visible.
Hi,I have three different tables. I want one login screen and check all table ,if any user got it,but i have written simple store procedure and i take values by parametrically,how can i know user's table name?ALTER PROCEDURE [dbo].[sp_GirisKontrol] -- Add the parameters for the stored procedure here @Username nchar(12), @Password nchar(12), @Id int output, @Rowguid UniqueIdentifier outputASBEGIN SET NOCOUNT ON; SELECT @Id=Id,@Rowguid=Rowguid FROM Admin WHERE Username =@Username AND Password =@Password UNION SELECT @Id=Id,@Rowguid=Rowguid FROM Ogretmen WHERE Username =@Username AND Password =@Password UNIONSELECT @Id=Id,@Rowguid=Rowguid FROM Ogrenci WHERE Username =@Username AND Password =@Password if(@Id IS NULL) SELECT @Id=-1END
I have defined a datetime field (called date_created). When I insert the value 01/01/1999 <insert candidate values (01/01/2000)>, it accepts the date (i.e <Select date_created from candidate> )as 1900-01-01. However, when I enter the date as ‘01/01/1999’<insert candidate values(‘01/01/2000’)>, i.e in single quotes, it accepts the date as 01/01/2000 only!
Are single quotes essential for this or is there something that I have over looked! Please enlighten me!
Hello, everyone! What are the precision & the scale values in the numeric field for?(as also in int, etc).I need to have a field that is exactly 6 digits in length. However , when I enter the value , in the length column , it defaults to either 5 or 9 , depending on the precision values. Also when I enter the data , that field accepts not only 9(defined with precision of 10), but more than that, till about 15 digits!!…I think I am not clear on the use of precision…what do I need to define the field as so it accepts only 6 digits? Please enlighten me . Thanks in advance!
