.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

new query - braces, case sensitivity, database names now required? TURN IT OFF PLEASE?

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

call me stupid or whatever, i don't care.

i have found the new query editor in 2008 have the most restrictive and pain in the a$$ feature i have ever seen.
every query now requires brackets, fully qualified dabase name + table + column for reference.

WHY am i required to type the name of the database i'm querying in a stored procedure?  COMPLETELY POINTLESS!
PLUS on top of all of that, the junk is CASE SENSITIVE.  if i wanted to waste time worrying about caps, braces and meaningless syntactical characters,  i would write code in C#.

the reason why this is SUCH AN INCONVENIENCE is that the database i am writing against has HUNDREDS of tables.
additionally, i've used 2005 for years and i really find it highly inconvenient to use the same tool as before but now i need to write queries in proper english and case.

here's a quick example: 
previously i could type 'select top 5 zipcode from shipment
NOW with the new and improved pain in the a$$ feature i have to type:
'select top 5 '[databasename].[dbo].[shipment].[zipcode] from [databasename].[dbo].[shipment]'
that might not be an exact example but you get the idea.

maybe i just don't know what the new intellisense feature is that controls this?
somebody please help me figure out how to turn off this restriction, it is

View Complete Post

More Related Resource Links

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

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' }

Change SSP and Search database names

While creating SSP, besides the Admin site and MySite content databases, two more databases get created. They are usually named MySSP_DB and MySSP_Search_DB. If later we have a need to change these database names, what is the procedure? Thanks

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

using a database field as a hyperlink query?

I have three tables in my sql database contactssurvey answersrespondent_initialContacts holds all customer data with custid. survey answers has assigned everyone how took the survey a number in a field called respondent_no.respondent_initial holds input from a webform where I populated it with the custid from contacts and respondent_no from survey answers before the webform input is inserted. I want to send an email to them that had there respondent_no in a hyperlink to the webform so that when they entered their data into the webform it would use the repondent_no to determine where to insert the form data.Example joe clicks http://www.myplace.com/order.aspx?23             Mike clicks http://www.myplace.com/order.aspx?125it would look up respondent_no (23) from the respondent_initial table and insert the data in that row in the database for joe's webform entry and it would look up respondent_no (125) from the respondent_initial table and insert the data in that row in the database for mike's webform entry.my code behind looks like this// Create command                comm = new SqlCommand("INSERT INTO respondent_inital (Practice_Name, Address1, Address2, City, State, Zipcode, Phone, Email, Contact, Dealer, item_9223, item_9129, item

calculating the size required for backup of database

Hi, How to calculate the size required to take backup of a database in mssql 2000.the results returned by sp_spaceused differ too much from the actual size taken on the disk. Thanks in advance

Query help to write case statement in Where clause?

Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

How to extend this query to write the results back to a database?

FOr this query I have four tables I would like to access. SELECT StockA, SUM(Score) AS Total FROM dbo.ScoresNew_LongPrice WHERE [When] >= @StartTime AND [When] < @EndTime GROUP BY StockA ORDER BY SUM(Score) DESC _LongPrice, _ShortPrice, _LongVolume, _ShortVolume I would like to improve the above query to return not just the results from LongPrice, but four columns, of sum(score), from each table, if possible. And, if that is possible, I would also like to have the query then update another table with its results.  Not a new table, but just updated to an existing table. Anyway, I can do some stuff in T-SQL but this seems over my head.  Any help would be appreciated. (The four tables all have identical structures)

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

how to count transactions done on your database by T-SQL query?


Dear All,

I have multiple database and I want to know how many transaction that are happening per database using T-SQL, can anyone help me?





Karim Alaa

query all tables in the database with slightly different query


Declare @Table_Name varchar(50)

FROM sys.Tables

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @Table_Name

While @@FETCH_STATUS = 0
  SELECT * FROM @Table_Name

 FETCH NEXT FROM table_cursor
  INTO @Table_Name

CLOSE table_cursor
DEALLOCATE table_cursor

The above code gives error, maybe I am mistaken about the way cursors work.


But how can I acheive the same pirpose


I want to query all tables in the database with the same query in which the where condition will slightly vary.

The above gives this error


Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@Table_Name".
Msg 1087, Level 15, State 2, Line 13
Must declare the table variable "@Table_Name".
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@Table_Name".



Query help required - before I pull out the rest of my hair!!


I have a query that I am running from a Table Function....

FROM dbo.FN(@p1, @p2)
WHERE cValue IN (1)

And I get the following error message..

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '= 31' to data type int.

Yet if I run the following query with the only change being in the WHERE clause.....

FROM dbo.FN(@p1, @p2)
WHERE cValue IN (1, 9)

It runs OK. Now please can somebody explain the logic of that to me?!?! Basically if I put in a single value into the brackets it fails. it only runs when I put in more than 1 value. I will be bald before the day is over!



retrieving column names using sql query


hi All
 I have A database in access2007

I want retrieve column names with sql query in my project
I write below code:
"select column_name from information_schema.columns where table_name ='table 1' ORDER BY ORDINAL_POSITION"

but display error :
Could not find file 'D:\Projects\Tapco_ECM\Tapco_ECM\Tapco_ECM\bin\Debug\information_schema.mdb'.

plz help me

Using CASE statement in "IN" clause of query


I have a sql datasource with the ControlParameter called ddlDropDownList.

2 questions:

1. Is my syntax below ok, I am getting a runttime error that the syntax is incorrect.

2. I have the select statement set up the control parameter to get the selected value from the dropdownlist as shown below, is that syntax correct?  especially the "selectedItem.value" for the propertyname property. thx

select fieldOne, fieldTwo from someTable where fieldOne IN 
CASE WHEN @ddlDropDownList='valueABC' THEN ('ABC') WHEN @ddlDropDownList='valueDEF' THEN ('DEF') 

<asp:ControlParameter ControlID="ddlDropDownList" Name="typeSlctd" PropertyName="SelectedItem.Value" Type="String" />

Calculate database query value by using Math expression ?


I'm currently develop a monitoring and evaluation database with many indicators

For example: I have two input  indicator :  car , truck

Input_IndicatorID        quantity

Car                               3

Truck                            2

I'd like to create an output indicator have field: formular. The result of vehicle = car + truck = 5

Output_indicatorID      formular

Vehicle                         Car + truck

I don't now how to parse this string, detect parameter: car, truck  to get the answer

I search on Google and find some Math parse like:  muParser, bcnet  but it just for a string


Please help me solve this

database query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     Marks

Math       First Terminal    CT              2010    18
Math       First Terminal    Hall Exam   2010    67.2

Science   First Terminal    CT              2010    20
Science   First Terminal    Hall Exam   2010    78

Now how can I get the result followningly

                                First Terminal

                        CT       Hall Exam      Total


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