i develope a web projects of horoscope or astrology(http://demo.reallianzbussimart.com/allzodiac.aspx), there is an 12 Zodiaz sign and all the data call on this page through the Database , in this page i there is an one sql query ---- (Select Top 1 col from colour order by newid(),Select Top 1 num from number order by newid() ,Select Top 1 days from day order by newid() )
then all data call one by one change ..
when the refresh the page the value of all zodiac is change that is wrong ,,,
so what is the quary that one time in day the value off all zodiac is same next day the value has been change............................................
so please help me /...........
tell what is the process to not data change into the page refresh
I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks
hi,im just a newbie in asp.net.i have my sqldatasource and a label. i want to get the data from sqldatasource to the label. how can i do that.need codes
Hi, I have stored procedure with cursor I have to use following step 1 fetch RateValidDates into @Id_RateCode, @TheValidDate .... get data
step 2 fetch prior from RateValidDates ... condition
step 3 fetch NEXT FROM RateValidDates into @Id_RateCode, @TheValidDate ... go to next record
Sql query analyzer output result of fetch NEXT FROM on the screen What seeting should i use to avoid output on screen (The reason My cursor takes about 8000 - 10000 rows when it's open)
My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.
------T-SQL for fetching data from XML datatype------ WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my) SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet FROM MinaDekl CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1) -----------------
Hi,I have a table with two columns Task and Employee. It lists all thetasks and the assigned employees. The same task will have multipleroles with an employeename or 'manager' as the data.If I have the following data'sales', 'john''sales', 'manager''dev', 'manager''make_coffee', 'manager''browse', 'jane''browse', 'manager'I need to get the rows wherever an employee is named (sales and browsefor example) and get manager for the rest.I can make it in two queries. Look for not manager in one and then formanager. Is there anyway to get them in a single query?If I need to look for 'sales', I need to get 'john' and not 'manager'.How to do that in a single query?Another need is to list all tasks with assigned. So for the above, Ishould get the following list'sales', 'john''dev', 'manager''make_coffee', 'manager''browse', 'jane'with two queries, I will get'sales', 'john''browse', 'jane''dev', 'manager''make_coffee', 'manager'which is ok. Order/sequence is not important.TIA,Sreelatha
I am getting problem in fetching data from SQL using C++ program. How to Fetch SQL Unicode characters? I am using ODBC driver calls in C++ to fetch SQL data. It is working fine with character data. But for Unicode data it displays €œ????€? instead of data.
I am using SQL Server SQLSRV32.DLL for creating datasource.
I do an insert of the xml into the table and that works fine, but how do I split the tags to different tables. I have tried SSIS and a XML Source to an OLEDB Source, but since the xml file contains different groups that do not work.
The xml is created by Infopath and it seems like the groups are created if the components belongs to different sections.
Table1 id int, xmltag xml
I am starting to be desperate, I really need some help solving this one way or another.
I am using SQL Server 2005 Mobile Edition & Merge Replication
in this I want to insert a record into table, in that table I have taken UserID coloumn as auto incrementing Identity type.
In a book I read that, If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.
Now I want to ask here that, how should I ressed that value before inserting?????
any help in a form of CODe will be appriciated.....
I was working on a logic which I am not able to code after many attempts. I have an Excel sheet(Base_Data.xlsx) with two sheets as "Mapping" & "Data" with the below data:
I need the rows updated today in catalogue table irrelevant of timing. I tried all the below queries.
select * from CATALOGUE where CAT_DATE=CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) select * from CATALOGUE where CAT_DATE= CONVERT(date, getdate()) select * from CATALOGUE where CAT_DATE= cast(GETDATE() as date)
I am getting output only if the information updated on 04/21/2015 00.00.00.000 but not for other timings, For example 04/21/2015 03.30.00.000, 04/21/2015 07.17.00.000 and all.
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
I have an excel sheet containing one column (ID_NO) with 400K rows. I have a database from where I have to fetch some other columns from a Netezza database. Initially I tried hardcoding all the 400K rows in the query that I wrote using filter WHERE ID IN ('1212','2334'). But after pasting all the 400K rows the query is running indefinitely.
I have imported all the ID in a SQL table (MY_LIST table). I used a DFT, and selected ODBC source, and selected my netezza server. Then in the 'Data access mode' I selected the SQL command from the dropdown.I pasted the same query that I wrote in Netezza. Is there any way to pull only for those records that I have pulled in my SQL table (MY_LIST) ?
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?
CREATE PROCEDURE TrigSendPreNewIMAlertP2 @REID int
AS
Declare @RRID int Declare @ITID int
Declare @intIMEmail varchar(300)
Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null open crReqRec fetch next from crReqRec into @RRID
Declare crImpGrp cursor for select ITID from RequestRecords where RRID = @RRID open crImpGrp fetch next from crImgGrp into @ITID while @@fetch_status = 0
I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...
Any help is highly appreciated....
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2 @REID int, @LOID int, @RetValintoutput
AS
Declare @RRID int Declare @APID int Declare @intREID varchar(20) Declare @intIMID varchar(20)
Declare crReqRec cursor for select RRID from RequestRecords where REID = @REID and RRSTatus = 'AC' and APID is not null open crReqRec fetch next from crReqRec into @RRID
set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID)
set @intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @APID and ImplementationGroup_Location.LOID = @LOID )
insert into ImplementationTasks ( IMID, ITStatus, ITStatusDate ) VALUES ( @intIMID, '2', GetDate() ) SET @RetVal = @@Identity while @@fetch_status = 0 Begin
Update RequestRecords set ITID = @RETVal, RRStatus = 'IA' where REID = @REID and RRID = @RRID
FETCH NEXT FROM crReqRec into @RRID end
close crReqRec deallocate crReqRec
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
i am having an endless loop after the first record.... anything i missed?
ALTER PROCEDURE IMPGrpEscalationX
AS
Declare @IMID int Declare @IMID2 int Declare @FS1 int Declare @FS2 int
Declare crFirst cursor local for select IMID from ImplementationGroup where IMSTatus = 'Y' open crFirst fetch next from crFirst into @IMID
begin Declare crSecond cursor local for select IMID from Employees_ImplementationGroup where Employees_ImplementationGroup.IMID = @IMID open crSecond fetch next from crSecond into @IMID2 set @FS2 = @@fetch_status if not exists(select IMID from Employees_ImplementationGroup where IMID = @IMID2 and IMType = 'T') Begin DECLARE @MsgText varchar(700) DECLARE @IMGRPNAME varchar(50) Set @IMGRPNAME = (select IMGrpname from ImplementationGroup where IMID = @IMID2) --SET @MsgText = 'This implementation group has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://xxxx.com/admin/implementationgrp_emps.aspx?imid=' + @IMID2 + '&imgrpname=' + @IMGrpName
I need to see the records from two tables where the mobilenumbers are not same. I have two tables named as messages and subscribers with Id, MobileNumber fields.
Now here I need to see the records where the mobile number exist in one table but not in other table. Please give me the sql query for this.
Select Division,ChasNo,BillDt Into #CMBills from Service_Bills Where Month(BillDt)=@Mh and Year(BillDt)=@Yr and Status Is Null
Select SB.Division,SB.ChasNo,CMB.BillDt as CurBillDt,Max(SB.BillDt) as PreBillDt,Datediff(day,Max(SB.BillDt),Max(CMB.BillDt)) as DiffDays from #CMBills CMB Left Join Service_Bills SB On SB.Division=CMB.Division and SB.ChasNo=CMB.ChasNo and SB.Status Is Null and SB.BillDt<CMB.BillDt Group By SB.Division,SB.ChasNo,CMB.BillDt
But I'm not getting details for all the bills I fetched.
How do I right queries to fetch first 10 records, then next 10 and so on..
My table structure is as follows: Columns -------- MPAD_ID (IDENTITY INCREMENT YES ) MPAD_EmpCode (Employee Code) MPAD_Date (Date) MPAD_Status (Attendance Status i.e. P, A, etc)
Sample Records for the same is given below
MPAD_ID MPAD_EmpCode MPAD_Date MPAD_Status ------- ------------ ---------- ----------- 1 1001 11/01/2007 P 2 1001 11/02/2007 P 3 1001 11/03/2007 A ... .. .. .. 14 1002 11/01/2007 P 15 1002 11/02/2007 A 16 1002 11/03/2007 P
I have a stored procedure that inserts one row into a table. From aSELECT statement I would like to call the SP on each row in theresults. Is setting up a cursor and using fetch statements the bestway (or even the only way) to do this?
Hello everyone, hope someone can help me with this.I have a SQL stored procedure that inserts a record into a table,creates a cursor to fetch the last record that was added to get theunique key that was created and then writes that and other info to aseparate table. This procedure was working fine at our ISP under NT 4and SQL 7.We recently moved to another ISP on servers that are windows 2000 andSQL 2000. Now this code is going kerplooey. It actually worked finein the staging area but now that it was moved into production, it isnot working. also wanted to mention that the production database wasrestored from a backup. below is the code.the first time this is run it is ok, for example the transactionnumber is 1. the next time it is run, a new record is created in thesweep results with a transaction number of 2. but for some reason,when i declare the cursor to fetch the last record, it goes back tothe transaction number 1 record. so the counts from transaction 1don't match counts from transaction 2 and the next step has an errorcondition and doesn't work.thanks in advance for any help you can provideAnn Williams-- update the sweep results tableINSERT tbl_sweepresults (del_wrkfeedback_count,updnull_feedback_count, swp_feedback_count,swp_count_error, del_error, updnull_error, swp_error, init_error,sweep_date)VALUES (@var_del_wrkfeedback_count, @var_updnull_feedback_count,@var_swp_feedback_count,@var_swp_count_error, @var_del_error, @var_updnull_error,@var_swp_error, @var_init_error, GETDATE())-- create cursorDECLARE tbl_sweepresults_cursor SCROLL CURSOR FORSELECT transaction_no, sweep_date, init_error, updnull_feedback_countFROM tbl_sweepresultsOPEN tbl_sweepresults_cursor-- get transaction number, sweep date, init error, feedback sweepcount and pass to tbl_currentTrans for OPAL comparisonFETCH LAST FROM tbl_sweepresults_cursor INTO @var_transaction_no,@var_sweep_date, @var_init_error, @var_swp_countzeroDELETE tbl_currentTransINSERT tbl_currentTrans (current_transaction_no, current_sweep_date,current_init_error, current_swp_countzero)VALUES (@var_transaction_no, @var_sweep_date, @var_init_error,@var_swp_countzero)-- close the cursorCLOSE tbl_sweepresults_cursorDEALLOCATE tbl_sweepresults_cursor
i want to be able to get the columns from my table except for its pk. the long method is to selecting all columns by listing them one by one right? but what if i got 100 columns..
so.. i wanted to use the information_schema..
i have:
use dbase; select column_name from information_schema.columns where table_name = 'table1' and column_name != 'pkid'
so there i'm listing the columns, but how am i suppose to select those columns from my real table?
I'm trying this code but nothing is being displayedSqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CON"]); SqlDataAdapter da = new SqlDataAdapter("my_sproc", conn);DataTable dt = new DataTable(); DataRow dr; //Adding the columns to the datatabledt.Columns.Add("CategoryIdIn"); dt.Columns.Add("CategoryNameVc");foreach (DataGridItem item in gd_freq.Items) { dr = dt.NewRow(); dr[0] = item.Cells[0].Text; dr[1] = item.Cells[1].Text; dt.Rows.Add(dr); }//ForEach da.Fill(dt); gd_freq.DataSource = dt; gd_freq.DataBind();
Hi, I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks ---- DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50); DECLARE @MyVariable CURSOR; SET @MyVariable = CURSOR FOR SELECT isnull(Filters.WorkflowIDs, ''), isnull(FilterElements.CreatedByUser, '')
FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID WHERE FiltersItemID = @FilterID; OPEN @MyVariable;FETCH NEXT FROM @MyVariable INTO @WorkflowIDs, @CreatedByUser;
Hi, I want ot get time just like this : 8:40:15 AMI am using select convert(varchar(12),getdate(),8) to get this but this function return 24 Hrs format. How to achive this by using small coding. Jasim...
I'm having trouble obtaining errors raised in a stored procedure via the ADO Errors collection after the second FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server database:
This is a very simple table with one column, and three rows containing the values 1, 2 and 3.
Consider this stored procedure: CREATE PROCEDURE TestStoredProc as BEGIN set rowcount 0 Set NoCount ON
declare @TestInt int declare @ErrMsg char(7) declare TestCursor cursor forward_only for select * from TestTable
open TestCursor Fetch next from TestCursor into @TestInt
While @@fetch_status<>-1 Begin select @ErrMsg = 'Error ' + convert(char, @testint) raiserror(@ErrMsg, 16, 1) raiserror(@ErrMsg, 16, 1) Fetch next from TestCursor into @TestInt end
Close TestCursor DeAllocate TestCursor return END
This stored procedure simply defines a cursor on all rows in TestTable. For each row fetched from the cursor, the error message 'Error n' is raised twice, where n is the integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the above stored procedure. After the stored procedure is executed, the code loops through the errors collection, and creates a message box for each error in the collection:
Private Sub Form_Load() Dim cn As Connection Dim cm As Command Dim oErr As Error
