Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label. while (reader.Read()) { string usr = reader["UserName"].ToString(); usr = usr.TrimEnd(); string pss = reader["Password"].ToString(); pss = pss.TrimEnd();
if (usrNmeLbl.Text == usr) { if (psswrdLbl.Text == pss) { //read the column from the reader and cast it to String as some may contain null values usrNmeLbl.Text = reader["FirstName"].ToString() + " "; psswrdLbl.Text = reader["LastName"].ToString() + "<br />"; psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />"; psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />"; Label4.Text = "Your relatives: " + "<br />"; Label4.Text += reader["Relation"].ToString() + ": "; Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString(); Label4.Text += reader["Relation"].ToString() + ": "; Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString(); } If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set. Label4.Text += reader["Relation"].ToString() + ": "; Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Quick question (I hope) regarding the cycling of the sql server error logs.
I am looking at implementing a daily / weekly job to run the stored procedure "sp_cycle_errorlog", but was wondering if there was any way of stopping the sql server process cycling the logs automatically when it is started? Therefore the logs will only be cycled when the job runs.
I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:
Code: WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c BEGIN SELECT COUNT(DepartmentID) AS DepartmentCount FROM dbo.tblTask WHERE (DepartmentID = @Variable) END
how could I build this query or what better way of doing the job there are...
Can someone help me with a script for the following?
7am tuesday, week 1 of the year: full backup, Trans log backups from between 8am and 10pm and nightly diff @ 10.15pm. both TL backups and Diff backups append to the full file. the next day - weds - exactly the same only without the initialising full backup @ 7am. each day appends to the same weeks backup. this goes all the way through to 10.15pm Monday night which is the last activity on the particular backup file.
7am Tuesday, week 2 of the year: exactly the same process but writing to a new backup file. backup file is named something useful, like with the date of the Tuesday for example..
ill manually delete things as I need to (once that months data has been written to End-Of-Month tape), but otherwise i'd ideally end up with 52 backup files, each containing a weeks worth of data.
help me obi-wan SQLee. cos I cant bloody do it....
Thanks
Alastair Methodology
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
Scenario: SQL Agent job calls "EXEC msdb.dbo.sp_cycle_agent_errorlog" once a week to cycle the SQL Server Agent log. Job is owned by "sa".
Most servers run this with no problem, but one active node of an active/active SQL Servers cluster fails with the message:
Executed as user: DOMAINSQL_Service. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022). The step failed.
The SQLAgent job actually appears to be doing its job... a new SQLAgent.OUT is generated with the event:
(Date/Time)+ [412] Errorlog has been reinitialized. See previous log for older entries.
If I try renaming the file SQLAGENT.OUT, I get the message "It is being used by another person or program," which I expect. If I stop the SQLAgent service, I can rename the file with no problem. Failing over has no effect.
I just don't understand why this job fails for this server. (It should be mentioned the job could be fixed to "Succeed on Failure," but I'd rather not.)
Code Snippet SELECT empID, unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, unit_date, GETDATE())) % 4, 0), 4) AS new_unit FROM dbo.empList
i have this code this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4 like this evry 4 month it return to 1 >2.......... ------------------------------------------
if i put unit_date = 01/05/2008 and unit=1 than new_unit=1 IT OK
but
if i put unit_date = 01/04/2008 and unit=1 than i get new_unit=2
but it should be 3 it dont go backward ok
if i put unit_date = 01/06/2008 and unit=1 than i get new_unit=4
I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table and a sales table joined by custno. SELECT Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo The output I'd like is CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount Getting the first inv date seems straight forward SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head. Can anyone point me in the right direction?
I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,
1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.
I have weekly billing cycle. And the billing cycle can start on any day of the week.
Assumption: Start day of week is MONDAY
Hence, the following
DayNumber Day 1 Monday if start day is 1, then billing cycle is Monday - Sunday 2 Tuesday if start day is 2, then billing cycle is Tuesday - Monday 3 Wednesday if start day is 3, then billing cycle is Wednesday - Tuesday 4 Thursday if start day is 4, then billing cycle is Thursday - Wednesday 5 Friday if start day is 5, then billing cycle is Friday - Thursday 6 Saturday if start day is 6, then billing cycle is Saturday - Friday 7 Sunday if start day is 7, then billing cycle is Sunday - Saturday
For a given date, i need find start date and end date of current billing cycle.
For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015
Hi All I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new record the id will be 21plese help me in this
I have a snapshot replication is running and now I want to stop the replication for a while. Is it possible to do that? If it is then where I can set to stop it? Please help.
I use EM to handle 2 SQL servers. One I can `stop`; the other I can`t. (except I think I used to be able to do so).
When I select the `stop` I get the following message from EM:
"An error 1051 - (A stop control has bee sent to a service which other running services are dependent on) occurred while performing the service operation on the MSSQLServer service."
How do I track down what this other running service is? How do I stop SQL?
I wanted to remove my Northwind database. But that database is currently used for replication. I'll have to stop the replication first before I can remove it.
Hi, Can anybody tell me how to stop the execution of a T-SQL statement at once? I have tried Alt+Break but its taking a long time to stop.Whats the reason?Plz suggest....I am dealing with a database containing 24343000 data. Joydeep
I have a statement that has been running great for the past hour but now it will not pull the info any longer and just gives me a null
DECLARE @Text VARCHAR(2000) SELECT @Text = COALESCE(@Text + '', '') + x.memotext FROM (SELECT TOP 100 PERCENT memotext FROM customermemoheader WHERE memonumber = 'TERMS' and customernumber = '0009' ORDER BY seqnumber) AS x
SELECT @Text AS MemoText
I have verified in the tables that the info is there by running the select statement from within the (). It has worked for 8000 records and now it no longer works. Any help would be much appreciated.
I have a performance issue with a Cognos report against SQL Server 2005. The total running time of the report is 1 minute 15, and using SQL Profiler I found out that 1m13 is spent preparing SQL. Execution and generating the report takes up 2 seconds; no problem there. The SQL is the same every time I run the report, yet SQL Server spends 1m13 preparing it every single time! I'm no DBA, but as far as I understand that's not what's supposed to happen; once prepared, the SQL should execute quickly every time.
Is there a way to stop SQL from preparing the statement every time?
(Cognos 8 against SQL Server 2005 through OLEDB. Oh, and this query takes about a second when run in EM.)
Hi,I am doing some resource hungry tasks (some extraction and loadingthrough DTS), for which each time the SQL Server Log files gets filledup!Is there any way to stop the logging (like as during restore)?Thanks in advance.-surajit
i'm desperate! I have a application in my SQL Express and only one database for it. But right now it have 3,97gb, without log file. i'm already buying a SQL Enterprise to upgrade this, take some days, but my question is: when this size came to 4,00gb my application will be stop working ? my business stop ???
Error: 0xC0047039 at Load work$$MyDataFile from flat file, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Load work$$MyDataFile from flat file, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
I did not request a shutdown! Someone please give me a global switch to tell SSIS to keep going. Why should I not load 20,00 records because of one bad record?
I have tediously changed every field on every tool to "Ignore Error". Why should I still have to do this? I mean why does SSIS default to failure? Is there some switch I could change in the XML that would change this default behaviour?
The problem with the data in this case: a NewLine character was missing so it looked to the data flow as one very long line.
In one of my packages, I have a script component to do a transformation and I am inserting the output to table. Within that script component, if the data does not match my requirement, I have to stop the job. For that, I am using 'componentmetadata.fireError' to raise an error. The problem is, now even when an error is raised, the job completes by loading the remaining records and then aborts. But I want to abort the job as soon it raises an error. How to do this?
I create a publication with 1 article with a parameterized filter. Empty table without records. Then i went to create a snapshot through snapshot agent. After snapshot was created, data are inserted with "Insert Into..Select" SQL statement. A total of 20 millions records (10GB) are inserted. When i try to sync with a SQL compact edition, the synchronization % stop at 0 for more than 1 hour. Does that mean it is not working?
Hi, The following stored procedure (SP) is using a dynamic sql to build the query. How can this be written using a standard sql. i.e. NOT dynamically being built. Initially I thought I can have something like the following query but it does not seem to be working for when the where caluse parameters are not passed. So I ended up using the dynamic sql as it returns the correct data. Can you see how the SP can be altered please? Thanks
------------------------this query does not return the correct data where the where parameters are not passed.------------ select * from tbl_Management where
([Year] is null or [Year] = @Year) AND (YearPeriod is null or YearPeriod = @YearPeriod) AND (AreaCode is null or AreaCode = @AreaCode)
@Year int = null, @YearPeriod int = null, @AreaCode varchar(3) = null
as
declare @sql varchar(1000)
set @sql = 'select' set @sql = @sql + ' ID' set @sql = @sql + ' ,AreaCode' set @sql = @sql + ' ,Year' set @sql = @sql + ' ,YearPeriod' set @sql = @sql + ' ,A1=A2+A3' set @sql = @sql + ' ,A2,A3' set @sql = @sql + ' ,B1=B2+B3' set @sql = @sql + ' ,X1=convert(int, ((B2+B3)*1.0/(A2+A3))*100)' set @sql = @sql + ' from' set @sql = @sql + ' tbl_Management' set @sql = @sql + ' where'
if (@Year > 0) begin set @sql = @sql + ' [Year] = ' + convert(varchar(4), @Year) set @sql = @sql + ' AND' end if (@YearPeriod > 0) begin set @sql = @sql + ' YearPeriod = ' + convert(varchar(2), @YearPeriod) set @sql = @sql + ' AND' end if (@ProgrammeAreaCode is not null) begin set @sql = @sql + ' AreaCode = ''' + convert(varchar(3), @AreaCode) + '''' set @sql = @sql + ' AND' end
--trim off the last AND... set @sql = left(@sql, len(@sql) - 3)
We have automated the process of backing up and restoring the databases. But the problem is One database is restoring from several hrs ..though it is supposed to take only a few mins. So there is some issue with it and I want to find out how I can stop the restoring process. Its not a command am running in query analyser or Enterprise manager, but its automated so i cant see the command to stop it. Any ideas?