.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Need help to merge all the code into only one query

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
Hi guys,   Can anyone help me, please to merge all the queries below into one query? Thanks in advance, Aldo.   DECLARE @i smalldatetime, @FirstDate smalldatetime, @LastDate smalldatetime, @SortGroupStart INT, @SortGroupEnd INT, @TargetSales REAL; SET @FirstDate = '2008-10-01T00:00:00' SET @LastDate = '2008-10-31T00:00:00' SET @SortGroupStart = 3000; SET @SortGroupEnd = 3999; SET @TargetSales = 300000;   if object_id('MonthlySalesIncrement_20081002000_Calendar') is not null exec(' DROP TABLE MonthlySalesIncrement_20081002000_Calendar'); CREATE TABLE MonthlySalesIncrement_20081002000_Calendar (CalendarDate smalldatetime PRIMARY KEY) WHILE @FirstDate <= @LastDate BEGIN INSERT INTO MonthlySalesIncrement_20081002000_Calendar (CalendarDate) VALUES (convert(smalldatetime, @FirstDate, 121)) SET @FirstDate = DateAdd(dd, 1, @FirstDate); END   if object_id('MonthlySalesIncrement_20081002000') is not null exec(' DROP TABLE MonthlySalesIncrement_20081002000'); SELECT convert(smalldatetime, Stock.VALUEDATE, 120) AS 'Stock.VALUEDATE', StockMoves.DOCUMENTID AS 'StockMoves.DOCUMENTID', StockMoves.SUPPLYQUANTITY AS 'StockMoves.SUPPLYQUANTITY', StockMoves.PRICE AS 'StockMoves.PRICE', StockMoves.RATE AS 'StockMoves.RATE', StockMoves.DISCOUNTPRC AS 'StockMoves.DISCOUNTPRC', Stock.DISCOUNTPRC AS 'Stock.DISCOUNTPRC', RATES.RATE AS 'RATES.RATE' INTO MonthlySalesIn

View Complete Post

More Related Resource Links

Use DB query result to generate Jquery required lines of code



I 'm gonna query database for photo records. each record include "photo path" where imagesURL  stored in string format

 I Choses my favorite Juqery plugin to show images. Plugin needs some lines of code like below.

How could I  generate these lines

$(function() {
sleep: 2,
fade: 1
}, [
{ src: 'picture1.jpg' },
{ src: 'picture2.jpg' },
{ src: 'picture4.jpg' }

Linq query in code behind

Hii would like to figure out this LinqDataSource in code-behind in vb.net<asp:LinqDataSource                 ID="lds" runat="server"                ContextTypeName="dbDataContext"                TableName="vKommentarUnion"                  Select="new(Key, Count() as Count, It as Items)" GroupBy="themen"                 where="isdrin=0"                AutoPage="true" AutoSort="true"            /> I found only samples in C#.How can i figure it out in vb?this does not run, what is wrong? Dim db As New dbDataContext Dim kommentare = From c In db.kommentar _ Group c By c.kursid Into Group _ Select key = c.kursid, count = Group.count, _ Items = From item In Group Select item lv.DataSource = k

problem with merge statement query and parallelism plan


I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

how to merge existing code with web services.


Hello, I am new to ASP.NET.  I am just now learning about all the benefits.  I currently have some C# code that receives sensor signals such as distances from a robot and I wish to put that code into web services and into a website, so that if I want to gather some information from the robot i can through the web, as well as send signals back.  I haven't found any information yet on how to do this. 

I am just looking on how to intergrate the existing C# code I have to a web service

Any help would be great and any example would be great. 

Thanks in advance!!

SQL Server 2008 query execution time is different if i execute it in management studio or from code

When I execute query from software which i wrote in c# it required lots of time to complete but if I execute exactly the same query from management studio it will be done in seconds. In both cases execution plan is the same. 

Merge Replication - Error - Query processor ran out of stack space


Hello everyone,

I have a merge publication configured that has been running fine with no issues for many weeks.  Here is my setup... Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2, Subscriber - SQL 2008 SP1.

On Wednesday and Thursday of last week, I created a few hundred foreign keys referencing a table (user information for auditing).  Then, on Friday evening, I started to receive this message:



Msg 8621, Level 17, State 1, Procedure MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D, Line 85
The query processor ran out of stack space during query optimization. Please simplify the query.



I have traced it down, this command gets executed:


exec MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D @maxschemaguidforarticle = 'F990B1EE-D52F-4DCA-8EEF-C1783DA47A0F', @type = 6


Which executes this:



update query in ms access using c#---- pronlem with code



i am using MS Access as my database and visual studio as front end... and am using c# as middle ware....

i am trying to use update query, to update data into a table. 

insert  and delet query had been successfully runned for me.

the error showed to me is   --- "Data type mismatch in criteria expression."


name of table: people

connection name: connmod

coloum name are: name1, contact, address1, address2, address3, groupname

OleDbCommand commod = new OleDbCommand(("update people set name1 ='" + companyname.Text.ToString().Trim() + "' ,contact='" + name.Text.ToString().Trim() + "',address1='" + address1.Text.ToString().Trim() + "',address2='" + address2.Text.ToString().Trim() + "',address3='" + address3.Text.ToString().Trim() + "',groupname='" + comboBox1.Text.ToString().Trim() + "'  where id ='" + textBox1.Text + "' "), connmod);


List Item Update Exception from HRESULT: 0x80040E2F Unexpected query execution failure, error code 3


A thread somewhat simliar to this has been posted before as well, but i just wanted to detail out the issue and circumstances

We have a list with 53 columns and 2600 items(and growing. I know its rubbish, but thats the way it is at the moment). The list was created using SharePoint GUI  on a test server, saved as a template with data, and was then imported to the production environment. I can post the list columns and type if it helps.

Every one in a while, on updating an item, SharePoint throws up the very not so helpful error page with "Exception from HRESULT: 0x80040E2F".

We looked into the logs and found that for each suc error, there was a Database level error logged as well.

The error starts with Unexpected query execution failure, error code 3621. Additional error information from SQL Server is included below. "The statement has been terminated." Query text (if available): "BEGIN TRAN;DECLARE... and runs for many lines so i wont post the entire blurb here.

Each time, the item update is successful, despite this error. The list also has an Item Updated event handler attached to it. Every time the update error occurs, the event handler does not seem to be firing.

Anybody can shed light on what is happening. We have tried exporting the list template to another site and everything seemed to work

Reference query values from custom code


How can I reference query values from custom code?

Normally in the expression I can use Fields!QueryValueX.Value

In custom code I can reference textboxes, for example - if ReportItems are passed as the parameter

But this doesnt include the query items

Javier Guillen

Changing column Data Type when using merge replication: RFC on my SMO code.


I maintain an application for that uses SQL 2005 / 2008 for data persistence.
Some clients use Merge Replication (pull or push) to replicate data.

Some of the data the application stores should be "versioned". All versions of a row containing medical information should be stored, somewhere in a database. This means all updates and deletes to those rows should result in in 2 affected rows:

  • a copy of the row before the change
  • a row containing the change (or the deletion of the row in question)

I plan to achieve this using triggers on all the tables for wich "versioning" is required.
The old versions of a row may be stored in the same database, an other database (or a database on a linked server).

Unfortunalty, the AFTER UPDATE, DELETE triggers do not support TEXT, NTEXt an IMAGE data type columns.
Thus I am required to convert all text, ntext and image columns to their respective "new" data types, varchar(max), nvarchar(max) and varbinary(max).

This is not a big issue when the database is not published, but when it is... most articles (and posts) i've read suggest:

  1. Add a column of the target data type to the table, copy data from source column. rename columns and drop the source column.

  2. Create a temp table (with the s

Merge Xml nodes from linq query


Trying to merge nodes with the same username together and combine the Link node.

For example:

I've got the following Linq to XML query:

var SelectedUsers = (from u in db.Users
                                    join t in db.Details on u.ID equals t.User_ID
                                    join a in db.Optional on t.ID equals a.Details_ID
                                    where u.UserName != ""

read a overlength string in code from a DMX query


hello , when i using  the DMX obtain the rules of mining model J26, then i want to show the rules on interface, but when i excuting code:

               string dmx = "SELECT FLATTENED NODE_DESCRIPTION as rule FROM J26.CONTENT WHERE   NODE_TYPE = 4            and MARGINAL_PROBABILITY>0.5 and node_support>0 ";

                AdomdDataAdapter ada2 = new AdomdDataAdapter(dmx, conn);
                DataTable temTable2 = new DataTable();
                int MaxRow = 8000;
                temTable2.MinimumCapacity = MaxRow;
                 string RTRule = temTable2.Rows[0][0].ToString();

it throws out an error: there is no rows in temTable2.

and RTRule is null .

so ,could

how to create a query in gridview selectcommand without the code behind for getting the system date.


how to query the system date in selectcommand in gridview without using the codebehind. using c# .net


my code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT * FROM tablename where date_today = <SYSTEMDATE>" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"  ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"

SQL code works elsewhere, not in VS2010 Query Builder

SELECT caseid, recid, trandate, description, amount, SUM(amount) AS billtotal
FROM billtran GROUP BY caseid

The above SQL executes and gives me what I want under PHP/MySQL, but it won't even execute under VS2010 Express. When I put the above SQL code in the Dataset Designer Query Builder, I get an error: "In aggregate and grouping expressions, the SELECT clause can contain only aggregate and grouping expressions. [ Select clause = ,recid ]"

I don't understand what the problem is. What is the thing I must know about SQL that is different under Microsoft?

Code Optimized Web Development Profile (VS 2010 and .NET 4.0 Series)

When you first run VS 2010 it prompts you to select an IDE profile to use. The profile you select will configure how tool windows are displayed/docked in the IDE by default and set the default keyboard shortcuts. You can then customize any of these settings by using the Tools->Options menu within the IDE and then override/change them. You can also later reset your profile and pick a different one by choosing the Tools->Import and Export Settings menu command.

Free Code Snippets - ASP.NET

You Can Find Useful ASP.NET Related Posts on the above link.

Free Code Snippets - C#

Free C# Code Snippets
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend