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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Not identifying Sql Quote when executing query

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :ASP.Net
 

Im using SQL Server 2008 R2, Server version 9.0 SP3

Im trying to do a replace on a comma seperated values before submitting to the "IN" condition.

Declare @inputrecords nvarchar(max)
Declare @inputrecordscsv nvarchar(max)
set @inputrecords = N'a,b,c'
set @inputrecordscsv = ''''+ replace(@inputrecords, ',', ''',''') + ''''

select * from MyTable
where Mytable.Record IN (@inputrecordscsv)
My intension is to get query work like this:
select * from MyTable
where Mytable.Record IN ('a','b','c')
But its not working, and its considering the @inputrecordscsv as a single string.
Any suggestions how to make it work?
 
Thanks
Sunny


 

 




View Complete Post


More Related Resource Links

SQLDataAdapter/SQLDataReader takes lot of time for executing a query

  
All, I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.  1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown. 2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored). The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader: SqlConnection conn = new SqlConnection(CONNECTION); SqlCommand ObjCmd = new SqlCommand(query, conn); ObjCmd.CommandTimeout = 600; conn.Open(); SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time ddl.Items.Clear(); // Call Read before accessing data. while (daSelTools.Read()) { ddl.Items.Add(new ListItem(String.Format("{0}",daSelTools[0])));

find situation about a query that it is executing now

  
Hi all. I am a c# programmer. I have a update query that it need 2 minutes for executing (for each reason). I don't want discuss about its performance. My question is: How can I find situation about a query that it is executing now and it has not executed completely yet. for example How can find my c# code how many records are affected by my query every 10 seconds while that query is executing? Is sql profiler says that to us?

situation about a query that it is executing

  

Hi all. I am a c# programmer. I have a update query that it need 2 minutes for executing (for each reason). I don't want discuss about its performance.

My question is: How can I find about situation about a query that it is executing now and it has not executed completely yet. for example How can find my c# code how many records are affected by my query every 10 seconds while that query is executing? Is sql profiler says that to us?


situation about a query that it is executing now

  
Hi all. I am a c# programmer. I have a update query that it need 2 minutes for executing (for each reason). I don't want discuss about its performance. My question is: How can I find situation about a query that it is executing now and it has not executed completely yet. for example How can find my c# code how many records are affected by my query every 10 seconds while that query is executing? Is sql profiler says that to us?

Help Identifying What Wrong W/ Query Syntax - sp_send_dbmail

  

Hello,

I know that I have probably done more than a few incorrect things in this statement but it works when I run it in the studio so I am not sure why it will not work when trying to use it from within sp_send_dbmail. I am pretty much new and learning as I go but this one has me lost.

My Error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is what I am trying to run from Studio to validate that the syntax is correct before attempting to set it as a scheduled task.

DECLARE @like1 varchar(100);
SET @like1 = '%-LOST-%'; 
DECLARE @like2 varchar(100);
SET @like2 = 'LOST%-'; 
DECLARE @sql NVARCHAR(MAX);
SET @sql = (SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10e.ocx' 
	  WHERE ProfileDate > GetDate()-1 
	  and (ProfileName not like @like1 and ProfileName not like @like2)
	  and version <> '10,1,82,76'
	  UNION ALL
	  SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10i.ocx' 
	  WHERE ProfileDate > GetDate()-1

executing query in send mail task

  

Hello,

I need to execute a select statement in a send mail task in SSIS. I can send regular emails using this task, but is is possible to send a select statement through? I have tried xp_sendmail, but we are not allowed to turn this feature on, and  I have tried reporting services, but the service is not installed. Lots of road blocks... LOL

 

If this is not possible, please recommend an alternative...

Any help would be appreciated

Dave


Dave SQL Developer

Executing XML query from SQL 2005 pointing to SQL 2000 server

  
Occurs with root command.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= ADODB.Command  Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.    Error on Line 18.  The step failed.
 
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= Microsoft OLE DB Provider for SQL Server  Error Description: Line 1: Incorrect syntax near 'ROOT'.    Error on Line 20.  The step failed.
CODE:
Dim oCmd, sSQL, oDom
   
   Set oDom = CreateObject("Microsoft.XMLDOM")
  
   Set oCmd = CreateObject("ADODB.Command")
  oCmd.ActiveConnection ="Driver={SQL Native Client};Database=Validation;Server=server1;Uid=sa;Pwd=;"
  
   sSQL = "<?xml version=""1.0"" ?>"
   sSQL = sSQL &

Start Executing second query before completing the first one

  

Hi,

Is it possible to have a Query(say query2) start executing before another query(say query1) completes its execution.

Example: Consider a Stored Procedure having Three Queries(query1, query2, query3), is it possible to achieve the following:

start: query1

start: query2

complete:query1

start:query3

complete:query2

complete:query1

 

Thanks in Advance

greenAppDev


[Entity framework] Strange error executing a sql query

  

Hello to all,

I have a strange error, I've tried to search for it before writing this post but I wasn't able to find out a solution yet.

I'm executing a query using ExecuteStoreQuery against a MySQL database, but it throws an exception reporting that there's a syntax error in my SQL.

I've tried to copy&paste the sql query into the MySQL tool and it works nicely, giving the correct results.

I've tried to open manually the connection and using CreateCommand to use it the "old way".

My query is a bit long, 4000 chars more or less, could it be the problem?

Thank you very much!

Max


DataSet field missing while executing the query

  

Hi ... I have a dataset name distributorTotal in that there is a field name 'Current' which is Calculated field ... when i give ryt click on dataset -->query  then i executed query .. it is not showing any data and not showing field name as well..when i come out from that query designer ...field is missing under 'DistributorTotal' dataset..

now  there is no field under DistributorTotal...before i have 'current' field ...one more thing that 'Current' field doesnt have any data only null it had..that was null field actually

but i need that field to run the report.... getting tension...

please help me thank you...




MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

  
Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

  
If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

sql query that don't run

  

Hello....

I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query

  

I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:


Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5
&n

How to send an email with information in query string

  

First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.


MDX Query parameter from SSRS

  

Hello,
I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )

 

 


Categories: 
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