Hi Pros, I'd like to ask help on this small query of mine. Im doing a query and a sub query on my sub query i want to have it parameterized. Is there a way to do it? Please see my query script. select sum(issue) as [Issue], sum(nonissue) as [NonIssue] from ( AS select case when isissue = 1 then 1 else 0 end as 'issue', ...
I have something like the following data structure: Category StartDateTime EndDateTime =============================================== 1 12/1/2009 12:00 12/1/2009 12:12 1 12/1/2009 04:00 12/1/2009 04:20 2 12/2/2009 10:15 12/2/2009 10:22 2 12/2/2009 11:00 12/2/2009 11:01 ...
Hi , How can we find a particular stored procedure. I need to find a stored procedure which I don't know is in which database. Can somebody please, help with a script. Thanks ...
2 Questions actually: I know i must use Stored Procedures as much as Possible, but i would like to know the following please. A: Can i get a SQL Injection attack from a SELECT statement such as (Select * from MyTable) ? B: Also, can i get a SQL Injection attack when I use the SQLDataSource in ASP.NET? ...
I have a table with a column stored as string, but it is really a number like this: 17 - Doe 2 - Mike 3 - James I need to sort them and create a output like this: 2 - Mike 3 - James 17 - Doe How to write the SQL? Thanks in advance! ...
I'm dealing with an annoying database where one field contains what really should be stored two separate fields. So the column is stored something like "The first string~@~The second string", where "~@~" is the delimiter. (Again, I didn't design this, I'm just trying to fix it.) I want a query to move this into two columns, that would...
Hi, We have a database table that we pre-populate with data as part of our deployment procedure. Since one of the columns is binary (it's a binary serialized object) we use BCP to copy the data into the table. So far this has worked very well, however, today we tried this technique on a Windows Server 2008 machine for the first time...
I have a table of suppliers, and tables Computers, Cameras, Displays all containing the SupplierID field. I am trying to do a T-SQL that will list all the suppliers, with a count of all rows. I can do them one at a time with: SELECT SupplierID, COUNT(dbo.Computers.ComputerID) as Computers FROM Supplier INNER JOIN Computers ON S...
Warning: This is a long question The database that I am pulling data from has the table structure like this Table: ClientSales ClientSalesId int identity (1, 1) (PK) ClientId int (FK) TermId int (FK) StudentType1Population int StudentType1Adjustment int StudentType1Sales int StudentType1SalesAdjustment int Student...
Hi, I am using this query: SELECT DISTINCT pat.PublicationID FROM dbo.PubAdvTransData AS pat INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID WHERE (pa.AdvertiserID = 31331) AND (pat.LastAdDate > 7 / 1 / 2009) ORDER BY pat.PublicationID And it returns 0 results. What I am trying to d...
So I have these tables: Topics id, title, date Posts id, title, text, date, user, topic_id How should I structure it so that the first post, the topics text, is on top of all other posts in the topic? Sort them by date? Or is there a smarter way? ...
I'm using SQLite ADO.NET in my project so that I can unit test using an in-memory database. I want a copy of my real database but it is across the server. From what I've read, it looks like I have to specify ":memory" for the data source for the SQLite connection string. My problem is that I don't even know if it's possible to load a ...
Hi, Why is this wrong and how can I put it right? SELECT PublicationID FROM (SELECT DISTINCT pat.PublicationID FROM dbo.PubAdvTransData AS pat INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID WHERE (pa...
Thus far all I have done with SQL are simple select statements, but now I've also started learning about INNER JOINs, GROUP BY clauses and inner queries. I learned the concepts, but now I’d like to do some exercises using the Northwind database, so that such queries would become second nature. I tried to come up with good examples tha...
I am struggling yet again with SQL with my search function. Basically I want a stored proc that splits up a string into words then for each word I want to check using Full Text Search if it is in any of 2 tables. If results are found in these tables it should add them to any results it found for the other words and return a set where t...
I have an entity A who has entity B. Class Entity A { public EntityB; } Class Entity B { public Entity A; } Entity B has one to one relationship with A. I am trying to use cascade save,delete when entity A is saved so that I don't have to manually save entity B. It shpould be done automatically. my mapping for entity B looks...
I'm after a simple stored procedure to drop tables. Here's my first attempt: CREATE PROC bsp_susf_DeleteTable (@TableName char) AS IF EXISTS (SELECT name FROM sysobjects WHERE name = @TableName) BEGIN DROP TABLE @TableName END When I parse this in MS Query Analyser I get the following error: Server: Msg 170, Level 15, State 1, Proced...
Is an outer join only used for analysis by the developer? I'm having trouble finding a use case for why you would want to include data in two or more tables that is unrelated or does not "match" your select criteria. ...
I have this sql statement: CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_A What i want is regex code which i can use to get all fields and data type. So will return something li...
I have city database with country,state,city,postalcode,areacode. The city is not distinct ..for eg one city is repeated for multiple postalcode. I just want the list of distinct city and the first postal code and areacode... if is use distinct it would let me select distinct country state city ...i cannot get other details like areacode...