|
The difference is: varchar - Nvarchar
|
|
|
|
|
I m facing some problem in execution of sqlite's fail algorithm. Its definition says that it don't rollback changes made prior to encountering constraint voilation and changes to that row where constraint voilation is occured and beyond never occur. I executed it. But when I update values after the row which is encountering constraint voilation,it do that. But definition is saying different thing. What can be the problem. Please tell by an example that how it is executed. Thanks.
|
|
|
|
|
|
I have been working on this one for week and I need help. It is complicated but I will try to explain what is going on and show a little code. What I have is a Silverlight application that communicates via WCF to the database. Now, the complicated part is that a windows job sends a UDP alert packet to each of the Silverlight clients to trigger an update of a DataGrid on the main page which does it update via the same WCF service Async call. This means that if I have 50 Silverlight clients when the alert packet is fired each of the 50 clients will make the same WCF call to the database. I have checked and insured that each client receives the UDP call. The problem is that when the clients all make the same call It gives me a wcf service error on some, but not all, of the clients. The code below shows the basic concept of the code in WCF service database call and Silverlight code. Of course the code is broken up into several sections, but you should get the main idea.
(WCF CODE)
List<customer> clist = new List<customer>();
...
_sqlcommand = new SQLCommand();
_sqlcommand = new SQLCommand();
_sqlcommand.Connection = _database.connectionString;
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText=”GetCustomers”;
try
{
Customer customer;
_reader = _sqlcommand.ExecuteReader();
While (_reader.Read())
{
customer = new Customer();
customer.Name = _reader[“Name”].ToString();
…
…
…
clist.Add(customer);
}
}
catch (exception e)
{
…
}
}
return clist;
(SILVERLIGHT CODE)
observableCollection<customer> obsCollection = new observableCollection<customer>();
private getmethod()
{
current.client.GetCustomerServiceCompleted += new EventHandler(ServiceReference.GetCustomerServiceCompleteEventArgs(client_GetCustomerServiceCompleted);
current.client.GetCustomerServiceAsync();
}
void client_GetCustomerServiceCompleted (sender, ServiceReference. GetCustomerServiceCompleteEventArgs e)
{
obsCollection = e.Results;
this.CustomerGrid.ItemsSource = obsCollection;
}
Thank you very much!
Steve Holdorf
|
|
|
|
|
Just a suggestion. If you're using Windows XP and Http Binding, XP has a limitation of 10 concurrent Http connections.
|
|
|
|
|
Thanks for the quick response; however, we are using Windows 7 all the way around. Still not sure. My DBA used the NO LOCK for the stored procedure but still no luck.
Thanks,
Steve Holdorf
|
|
|
|
|
To solve the problem all I did was put a using(connection = new SQLConnection ...) statment around the code using the sql connection so the unmanaged resources were garbage collected.
Thanks,
Steve Holdorf
|
|
|
|
|
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job_Display.displayID, Jobs.title, Entity.entityName, Jobs.location
FROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID desc
Here is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
|
|
|
|
|
Just a suggestion. The call to getDate is also going to include the current time. Have the time component truncated or set to '00:00:00.0000' as well. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I would suppose....
where (getdate() < job_display.endDate)
|
|
|
|
|
How about WHERE GETDATE() BETWEEN startDate AND endDate? BETWEEN is inclusive.
Also, (if SQL Server) maybe look nito the DATE datatype.
|
|
|
|
|
Convert GetDate() to a date to get rid of the time component (assumes SQL Server 2008).
SELECT CONVERT(DATE,GETDATE())
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have one main database (for my user). now when i change the database(SP,View,Tables,....) from my side I wanna make possible the i create some scripts and when i run the scipt all the changes make for the user database without changing the data of user.
//if needed to delete SP,Create View , Delete Table,Create Column ,.... every thing that changed.
thanks!
|
|
|
|
|
Thsi may help: Database Publishing Wizard in VS 2010 Professional[^].
It's usually best to generate 2 scripts: one for the objects and one for the data, especially if you have large amounts of data (in which case you probably should look at other methods of doing this).
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
We use Red-Gate SQL Compare but it is paid fr and not particularly cheap.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi thanks for reply.
But i wanna do that my self without and additional software.
just in C# and SQL.
|
|
|
|
|
I am trying to use a join to pull all 50 states from a table and data from another table if there is anything there. Here is the code
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szState
I cannot for the life of me get the 50 states to list. I have changed the join order (using tblOutages then tblStates with RIGHT JOIN) but cannot get the join to work.
Anything stupid I am doing?
tblStates is szState nvarchar(2)
tblOutages is CAP_OFFLINE FLOAT, UNIT_STATE nvarchar(4)
Thx
Mark Jackson
UPDATE - THE WHERE clause is causing the issue. Removing it causes all 50 states to show. I tried to put it in a HAVING clause but I can't do that unless I include it in the result set.
modified 29-Nov-11 13:54pm.
|
|
|
|
|
when you use GROUP BY you need to use and aggregate_function as well.
|
|
|
|
|
It looks like there is a sum operation, so whould that not be the aggregate function? 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Not sure, but you may also have to include in your where clause something that allows TA_START and TA_END to be NULL.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Changing the WHERE clause to be part of the JOIN clause produced the desired results
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
AND NOT(O.TA_START > '11/23/2011')
AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szState
Thx
Mark Jackson
|
|
|
|
|
The WHERE clause was causing the LEFT OUTER JOIN to act as an INNER JOIN, hence you lost all of the records where there wasn't a match.
Well done on finding the right way to do it and for showing others what you did!
|
|
|
|
|
Hi,
I am getting error while configuring "Web Service Identity" in SQL Reporting Service.
ReportServicesConfigUI.WMIProvider.WMIProviderException: The account name is not valid. Specify an account in the form domain\alias.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)
Please advise
Cheers
Berba
|
|
|
|
|
Please change account name which you are using for webserviceidentity I believe you have given a wrong account name for webservice. Please check it once.... 
|
|
|
|
|
Hi guyz,
I am a .net developer having experience of one and half year. I want to go for some sQL certifications which may be helpful in boosting my professional career. Can anybody help me that which course of SQL I should go for in this regard.
Thanks in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
