I am a .net developer.I am not that strong in SQL.I need a simple query.This is from Northwind Database.
select 'Select All' as CustomerID,'Select All' as City union(select CustomerID, City from Customers order by City)
i need to get output like 1st row should be Select ALl Select All and next rows should be output of this stat..(select CustomerID, City from Customers order by City)
i am not getting this...i can do by keeping the second query result in temp table and then using union... but i am looking without temp tables or simple query.
I am importing images names from a file into a database by using the code below. When I import the image names, I aslo want to add a date of "June 12, 2009" in the DATE column. Finally, I do not want to import any duplicates. Is there something I can change in this code to make that happen?
declare @cmd varchar(1000) set @cmd = 'dir "D:imagesREVIVE" /b' if (object_id('ImageTable..tblimages') is not null) begin insert into tblimages (imgname) execute xp_cmdshell @cmd delete e from tblimages e where ISNULL(e.imgname, '') = '' select * from tblimages end else create table tblimages ([id] int identity(1,1), imgname varchar(1000)) insert into tblimages (imgname) execute xp_cmdshell @cmd delete e from tblimages e where ISNULL(e.imgname, '') = '' select * from tblimages
I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different database that contains information regarding the group of the data.
I so far have the following:
SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx
I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.
I am writing code to insert values from an xml file. I check for a duplicate before inserting and delete if there is a match. Is there a more effecient way to prevent duplicates before insert a record? my code: private void LoadFData() { SqlConnection oConn = new SqlConnection(ConnectionString); SqlCommand oCommand = new SqlCommand(); oCommand.Connection = oConn; oCommand.CommandType = CommandType.Text; oConn.Open(); // Load committee master files string[] strFiles = System.IO.Directory.GetFiles("fff", "ffff.dta", System.IO.SearchOption.AllDirectories); for (int i=0; i
I want to find the sum of each tables fileld. how can do this. i tried the following.but could nt get the correct sum. "SELECT SUM(gb) FROM data20 union all SELECT SUM(gb) FROM data10 union all SELECT SUM(gb) FROM data5 union all SELECT SUM(gb) FROM data6"
I have a menu table which has a (mostly) unlimited number of levels/tiers:
[Code]....
I also have a working function which will give the up and down tiers based on a given MainMenuId:
[Code]....
My question is, is there a better way to do this such as combining the two querries? Every attempt I have made results in a "The Statement terminated. The maximum recursion 100 has been exhausted before statement completion"
how to run multiple queries on a single button click. Actually I want whatever a user write in text box and click the button the text would get inserted in table category and also a table name project should get updated. I am using Sql server 2005 and c#
In my stored procedure there are 8 queries for 8 tables.Each query has joins and sub queries. I am passing parameters & stored procedure name from front end(designed in asp.net 3.5) can we execute that 8 queries at the same time i.e. parallel execution so that I can minimize stored procedure execution time?
Alright, we have two database servers, one is owned by us, one owned by a partner. Our partners have been having issues lately that have been causing us numerous sql timeout errors, which takes down our whole system. We'd prefer to limit the time those queries can take to say 20 seconds max, otherwise quit trying and throw the error (which we can catch with a try/catch block). Couple of questions: Is there a way to set a particular db/connection string to timeout after X seconds? Is this the best way to quarantine that other system? or are there better ways to go about this?
I'm completely new to both VB.NET and JSON, and I'm trying to figure out how to do SQL queries towards an SQL 2005 Express server and format the return value to JSON. I got the queries working using this (perhaps very newbie-like) code;
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.UI Imports System.Web.UI.WebControls Partial Public Class SQLConnect Inherits System.Web.UI.Page 'Defines SQL variables Dim con As New SqlConnection Dim cmd As New SqlCommand Dim dr As SqlDataReader Dim ReadData As String Dim i As Integer Sub Click(ByVal s As Object, ByVal e As EventArgs) 'Define SQL address, database, username and password con.ConnectionString = "Data Source=localhostSQLEXPRESS;Initial Catalog=tempdb;User ID=tesst;Password=test" Try 'Initialize connection con.Open() 'Specify SQL query cmd = New SqlCommand("SELECT * FROM Member", con) 'Execute query, dump result array into variable dr = cmd.ExecuteReader() messageLabel.Text = "<br />Connection established<br />" While (dr.Read) i = 0 For i = 0 To dr.FieldCount - 1 'Dump query results into a variable ReadData += dr.Item(i).ToString Next End While 'Print query results messageLabel2.Text = ReadData 'Close connection con.Close() Catch ex As Exception messageLabel.Text = "<br />Connection failed<br />" End Try End Sub End Class
I have been looking at this, and I would love to see some code examples using this class or any other good method.
We moved to a new SQL server and I started getting an error on one of my sps only when I ran it from a website. If I manually ran the sp it worked fine. The error I got was this System.Data.SqlClient.SqlException: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. I can tell you that this sp does some things, then calls another sp which writes some sql to open an excel file and insert the data. Here is the pseudo
SET @sql = 'INSERT INTO dbo.TempParts (partKey, partKeyNumeric, partKeyAlpha, PartNumber, [Description], Comment, notAvailable ,BatchID)
We have an ASP.NET / Silveright web application. The silverlight client displays user specific data in a graphical form - it requests the data from the server: Problem: Getting this data is expensive, due to the underlying database queries that the server has to perform - so the client has to wait... Optimisation Idea: We run the database queries at regular intervals on the server, writing the results to a 'userdata' table in a database 'close' to where the ASP.NET server runs. The process of running the queries and writing the data to the tables is performed by a 'data collection' service, which is separated from the ASP.NET server.
When the client requests data the server retrieves it from a 'userdata' table. This should be nice and quick - we probably have the 'userdata' tables on the same machine as the ASP.NET server. We also have the added benefit that the client sees data even if the underlying database is offline. Of course the data is not live - but all data is potentially old as soon as it reaches the client. So now my Problem: The 'data collection' service needs the user credentials in order to perform these database queries (because each user gets different results for the same query).
How can I store user credentials in a database, in an acceptable 'secure' way? Such that the 'data collection' can impersonate a user to perform the database queries. Our initial scenario is based upon using windows integrated login to the database.
I need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using union
1) we have only one select query to select 5 column form the table(i.e. select col1,col2,col3,col4,col5). so in that case should we go for stored procedure Or we can directly write the select query in the .Cs file only ( means in Database layer). when we should go for the SQL server stored procedure and when we should go for in line query in Visual Studio itself.
2)how to decide when to use Clustered and when to use Non Clustered index?
