i have a table called tblpictures which look something like this..
filename|ID
----------
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3
is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..
hi gurus,
how to get " using bcp
e.g
select '"'+name+'"' from sysobjects
results
"name"
"name1"
I want to get the same using bcp ,so that i can populate into a file
something like
set @cmd='BCP "select '"' +name +'"' ,uid from sysobjects" QUERYOUT "' + 'vrs.txt' + '" -w -T -S -t , '
-- Executing the BCP Command
EXEC MASTER..XP_CMDSHELL @Cmd
Basically i make use of FormView in Web Developer Express Edition to create and store information to the database .... what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament .....this value would be stored within a session string in global condition that allowed any page able to access it .....
View 3 Replies View Relatedhi all
i deleted 8 fields from a table and i need to retrieve them back, is there a way to do that.
please help it is very urgent
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.
Please help me to start sql server.
Thanks.
Michel
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
Hi everybody,
I have a batch which needs to be run every day night at 2:00 am.I Using At command but it is not working. if u have idea, please let me know
I AM USING THE FOLLOWING COMMAND :
AT 2:00AM C:SCHED.BAT
Thanks in advance
Krishna
hi all
its urgent really urgent
please reply soon
I am getting error in sysindexes when i run dbcc checkdb on a production db.
the error is Server: Msg 8928, Level 16, State 1, Line 1
please help me to remove this
all the options of dbcc checkdb as well as table are not helping me
thanks in advance
HI Chaps
very simple question this time
Is there any method availabe to retrieve AM or PM value from date, IN TSQL
hoping will get answer shortly
regards,
Anas
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Thanks
Steve
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedI am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
Hi all,
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Thanks,
SQL Novice
Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.
assume i have a query like this.
Select A, B, C from Table1 where ID=xyz
whats the easiest way to get A, B and C in to string variables?
I know i can use the sqldatasource control but i just feel there is too much overhead there.
whats your suggestions?
Thanks
Justin
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
View 3 Replies View RelatedCould you help me out?
I am interested in retrieving certain record among outputs.
For example, if I use the following sql,
select * from info order by name asc
====================================
then I can retrieve 25 rows from info table.
What I want to do is that I want to retrieve 15th record only among possible 25 records.
How can I solve this problem?
Thanks in advance.
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
Hi all,
I want to retrieve the second maximum value of a column data present in SQL table.Please help....
A
----
10
25
23
15
here I want 23 as the result.
Hi folks,
Does anyone know how to retrieve the date when data has been inserted
thnx in advance
I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.
Thanks.
Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanks
Hi, all.
I tried to get result of sp.
Dim rst As Recordset
Set rst = New ADODB.Recordset
rst.Open sp, cnn
doesn't get result.
when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex.
But, still I think it should work!
My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update
--- list is one string separated by '&' delimeter
--- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&...
CREATE procedure spGetListOfTableToDownLoad
@listOfUpdateTime varchar(500)
as
SET NOCOUNT ON
Declare @listOfTable varchar(300), @item varchar(300)
Declare @tbl varchar(50), @uptime datetime
Declare @list varchar(500)
Declare @sep varchar(1)
SET @list = ''
SET @sep = '&'
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT
FROM cur
INTO @item
Declare @re bit, @tp varchar(50)
WHILE @@FETCH_STATUS = 0
BEGIN
-- get tablename, update time
Declare cur2 CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@item, '=')
OPEN cur2
FETCH NEXT FROM cur2 INTO @tbl
Print @tbl
FETCH NEXT FROM cur2 INTO @tp
print 'tp:' + @tp
SET @uptime = CAST(@tp as datetime)
print @uptime
-- @re =1: true, 0: false
EXEC spIsUpdate @tbl, @uptime, @re output
IF @re = 1
SET @list = @list + @tbl + @sep
CLOSE cur2
DEALLOCATE cur2
FETCH NEXT
FROM cur
INTO @item
END
if LEN(@list) > 0
SET @list = LEFT(@list, Len(@list)-Len(@sep))
CLOSE cur
DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO
__________________
--- PARAM:: @tbl: table name,
--- @uptime : update time (passed from local db) that will be compared on HQ table
--- return 1 if Max(UpdateTime) of @table > @uptime
--- otherwise return 0
CREATE Procedure spIsUpdate
@tbl varchar(50), @uptime datetime, @result bit output
as
BEGIN
Declare @bit bit
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
declare @uptimeHQ datetime
/* Build the SQL string once.*/
SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
SET @ParmDefinition = N'@tp datetime OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime
SET @result = 1
ELSE
SET @result = 0
--RETURN @bit
END
GO
I am trying t get output for the following querry but I know am missing something. Can anyone help me out with it.
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity
from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
In the above query am getting the productnum and quanity and it looks like this
productnum quantity
abc 6
ttt 3
sss 1
What am tring to do to this query is that . From another table 'product' i want all the data to be retrieved with this productnum(the table 'product' has a column called prductnum). I don't know how to write a query for this.
my query is
select * from product where productnum in (
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity
from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
Thanks.
Hi all
I've two tables
community, community_related.
I need to get community names for comm_relatedID from community table
where comm_id = 21 , please see my tables below
Community table Community Related
commID, comm_name comm_id comm_relatedID
21 test1 21 24
22 test2 21 22
23 test3 21 26
24 test 5 21 27
26 test6
27 test7
result am trying to display from community related table after joining community table on COMM_ID = 21 is
comm_id comm_relatedID comm_name
21 24 test5
21 22 test2
21 26 test6
21 27 test7
can anybody please help..with a query, am stuck at this point.
I have retrieved the data using CTE . But still need the retrieve the latest row number record from my result.
;with cte as
(
Select ROW_NUMBER() over ( PARTITION by [T7S1_PRODUCT_CDE_original],[T7S1_BR_NO_original] order by [sql_updated] ) rn , [sql_updated]
,[T7S1_TYP_CDE]
,[T7S1_PRODUCT_CDE_original]
,[T7S1_BR_NO_original] from [interface_i084].[dbo].[tb_i084_ds_CeresTLStageFileDocDetail](nolock)
)
Select * from cte
My Query result:
rnsql_updatedT7S1_TYP_CDET7S1_PRODUCT_CDE_originalT7S1_BR_NO_original
12012-06-26 00:17:32.007703A0200030OOO 00066
22012-06-27 12:30:21.803703A0200030OOO 00066
32012-07-13 01:15:36.073703A0200030OOO 00066
12012-06-27 12:45:30.653703A0200030OOO 00083
12012-06-25 07:45:33.907703A0200030OOO 00090
22012-08-16 12:45:22.227703A0200030OOO 00090
Now Expecting:
rnsql_updatedT7S1_TYP_CDET7S1_PRODUCT_CDE_originalT7S1_BR_NO_original
32012-07-13 01:15:36.073703A0200030OOO 00066
12012-06-27 12:45:30.653703A0200030OOO 00083
22012-08-16 12:45:22.227703A0200030OOO 00090
I want to retrieve the MAX rn record..
declare @TempAddressParsingTable table(
id_voter int null,
id_town varchar(10) null,
full_address varchar(200) null,
ad_num int null,
ad_str1 varchar(100) null,
ad_num_suffix_a varchar (10) null,
ad_num_suffix_b varchar (10) null,
ad_unit varchar(100) null)
insert @TempAddressParsingTable (ad_str1)
select 'apple UNITY RD' union all
select 'watermelon UNITY RD unit#32' union all
select 'currency UNITY RD unit# 99' union all
select 'marrakesh UNITY RD unit #32'
select * from @TempAddressParsingTable where ad_str1 like '%unit%'
select * from @TempAddressParsingTable where ad_str1 like '%unit%'
and ad_str1 not in (select ad_str1 from @TempAddressParsingTable where ad_str1 like '% unity %')
NULL
NULLNULLNULLapple UNITY RDNULLNULLNULLNULL
NULLNULLNULLwatermelon UNITY RD unit#32NULLNULLNULLNULL
NULLNULLNULLcurrency UNITY RD unit# 99NULLNULLNULLNULL
NULLNULLNULLmarrakesh UNITY RD unit #32NULLNULLNULL
trying to write a code to display record sets where there are only "units". "apple unity rd" should not be shown.
I'm trying to compare the fields if they are equal, retrieve it.
Because null <> null.
i cant retrieve the record. Is there anyway to retrive the records by comparing all the fields?
declare @table table(
ad_num int null,
ad_str1 varchar(50) null,
ad_num_suffix_a varchar (10) null,
ad_num_suffix_b varchar (10) null,
ad_unit varchar(10) null,
ad_num_test int null,
ad_str1_test varchar(50) null,
ad_num_suffix_a_test varchar (10) null,
ad_num_suffix_b_test varchar (10) null,
ad_unit_test varchar(10) null,
passed bit)
insert @table (ad_str1, ad_str1_test)
select 'apple road orad RD' , 'apple road orad RD'
select ad_num , ad_num_test,
ad_str1 , ad_str1_test,
ad_num_suffix_a , ad_num_suffix_a_test ,
ad_num_suffix_b , ad_num_suffix_b_test ,
ad_unit , ad_unit_test,
passed from @table
select * from @table
where ad_num = ad_num_test
and ad_str1 = ad_str1_test ad_num_suffix_a = ad_num_suffix_a_test
and ad_num_suffix_b = ad_num_suffix_b_test
and ad_unit = ad_unit_test
i have a table that has 9 columns that belongs to other tables.
i need to retrieve one column from this table. In order to do this, do i have to join all the other tables that has similar columns?
Hi every one,
here is my table: MVPTABLE
columnName: Accsno JanMail JanVisit JanPhone JanComments FebMail..upto DecMail DecVisit DecPhone DecComments.
eg: table with values:
Accsno JanMail JanVisit JanPhone JanComments FebMAil FebVisit
A234 1 2 3 yes jim 0 2
A234 0 2 0 No Comments 1 2
As34 0 0 0 No Comments 1 2
A235 1 2 3 yes jim 0 2
A235 0 2 0 No Comments 1 2
As35 0 0 0 No Comments 1 2
am sending 2 parameter as
1> @param1= A234
2> @param2= 'JanMail,JanVisit,JanPhone,FebMail,FebVisit,FebPhone,MarMail,MarVisit,MarPhone,AprMail,AprVisit,AprPhone,MayMail,MayVisit,MayPhone,JunMail,JunVisit,JunPhone,JulyMail,JulyVisit,JulyPhone,AugMail,AugVisit,AugPhone,SepMail,SepVisit,SepPhone,OctMail,OctVisit,OctPhone,NovMail,NovVisit,NovPhone,DecMail,DecVisit,DecPhone
based on these 2 parameter i wanted to retrieve respective Comments
say, if @param1 Ac234
@param2= JanMail,JanVisit,JanPhone,MarMail
if the value of JanMail or FebMAil....DecMail = 1,then retieve respective comments
if the value of JanVisit,FebVisit..........DecVisit=2,then retrieve respective comments
if the value of JanPhone,or FebPhone,.....DecPhone=3 then retive respective comments
thanks in advance
love all
Here's my table:
tblOrders
----------------
OrderID | Total
----------------
I'd like to get the Total value out of the table if I'm given the OrderID, and store it in a variable. I'm using C# and ASP.NET 2.0.
It's probably really simple, and I think I need to use a stored procedure and ExecuteScalar(), but I'm not sure how to do it.