I have a datagrid with populated by this query: SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')
I have paging, sorting and selection enabled.
Now I am looking for a way to use a wild card as a placeholder for the table name in my select statements so I can use the valued selected from the datagrid.
Example : SELECT * FROM %TABLENAME%
Hi .. i am SqL beginner. i having trouble output what i want from table. table contain 3 columns ________________________________ |(names)|(item)|(location)| 1.| Jimmy | pizza| TX | 2.| Joe | ball | CA | 3.| Joe | ball | WA | 4.| Jim | shoes| AZ | ________________________________
i try to select all records out from this table. but column 2 and 3 contain same information in names and item only different is location. how can distinct one of them?? and display like the below, please advise.
|(names)|(item)| 1.| Jimmy | pizza| 2.| Joe | ball | 3.| Jim | shoes| ________________________________
As a burgeoning SQL developer I have never really understood the need for SQL Server / Enterprise Manager to show us the whole dbo.Table name thing. What is dbo, and why do we need to know deal with it?With that said, in my SQL 2005 Express database all of my project tables for my project management demo were named guard.pgUsers for example and not dbo.pgUsers.How come? Why did they get named different on their own?
SELECT row_number() over (order by MAX(HeadlineDate)) as Number, COUNT(ArticleID) AS [Count], MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate FROM dbo.ZMArticle WHERE PortalID=0
GROUP BY MONTH(HeadlineDate), Year(HeadlineDate) ORDER BY MAX(HeadlineDate) desC
SELECT row_number() over (order by MAX(HeadlineDate)) as Number, COUNT(ArticleID) AS [Count], MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate FROM dbo.ZMArticle WHERE PortalID=0 AND Expiredate <> Null GROUP BY MONTH(HeadlineDate), Year(HeadlineDate) ORDER BY MAX(HeadlineDate) desC
it doesn't return anything....
How can I change that
The secret to creativity is knowing how to hide your sources. (Einstein)
Hi,I have a corporate database with about 60 different tables that spansmanufacturing, accounting, marketing, etc.It is possible, but unwieldy, to establish a relationship for eachtable in the entire database through critical fields like customer_idor product_id.But should I do that?My question is: Is there such a thing as too many relationships? CanI establish referential integrity via relationships with criticaltables like Accounting, but leave the rest unconnected and simply useJOINS in my business code?Thanks,HC
i got this stored procedure. i tried to modify it and now its giving me this scaler error.
Msg 137, Level 15, State 2, Procedure insertuser, Line 4 Must declare the scalar variable "@seller_id".
USE [DBCars] GO /****** Object: StoredProcedure [dbo].[insertuser] Script Date: 05/23/2008 20:44:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[insertuser] (@Make nchar(10),@Model nchar(10),@City nchar(10),@SellerID varchar(50),@MileAge nchar(10),@Year_Model int) as insert into tbcar values(@make,@model,@city,@seller_id,@mileage,@year_model);
I'm have a stored procedure that iterates through a list of numbers and adds an item for each number (user id) some of these ids are duplicates which is fine even necessary for the first part of my query but for the last I need to ensure that no duplicates id's are passed to the stored procedure, in this case called 'spInsertForBackupNote'. My thoughts here was to do something like this:
SET @Note_Buffer = @UserID -- @Note_Buffer being some kind of array?
IF @Note_Buffer = @UserID -- If its been added to the buffer we dont execute sp BEGIN Do Nothing here END
ELSE
BEGIN EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID END
I know this would never work because it would always be false since I just added the same userid to the buffer that I want to add. But I think you see my problem. I know it should be an easy one but my TSQL is limited. I've posted the whole sp. Hope someone can help.
Hello all, I'm working on an ASP.NET with a SQL server for database. Some of the tables, for example, contain information such as different types of Fabrics (silk, cotton, etc..) . I'd like to have this table localizable (English and French for instance). Is this possible ? Is there an equivalent of resource files in SQL server ? Or do I have to do this manually ? (have 2 separate fields in the table for those 2 locales)
I have a DTS Package that I am running from a command line via .bat file. Does anyone know if there is a command to have the command window minimized or running in the background? I used the /Rep N command but that still leaves the window open until the package has executed.
HelloI am trying to search 2 columns on a databsae table using a string put into a box, the code i have at the moment is SqlConnection conn = new SqlConnection(SqlDSFindPost.ConnectionString); SqlCommand cmd = new SqlCommand ("SELECT * FROM tblBlog WHERE UserName LIKE @UserName OR Title LIKE @Title; ", conn); cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%'; cmd.Parameters.Add("@Title",SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%'; conn.Open(); cmd.ExecuteNonQuery(); GridView1.DataBind(); I have tried all sorts of strings and even typeed the string directly into the parameter but never get any results, yet when i type the wildcards directly into the textbox i get the correct rows returned. Can anybody see anything wrong with my code and tell me where i am going wrong, or alternativly point me in the direction of some c# code for searching a database similar to the search box abovei dont do a lot in asp or c# so this is driving me crazy Thanks for looking
I have a need to use wildcards in a sql statement. e.g. select * from tbl where field='%computer%'. How can I substitute the string "computer" for a variable declared in the stored procedure. Procedure Sample @Str varchar(50) AS select * from tbl where field = '%' & @Str & '%' (How do incorporate the wildcard variable @Str?
I want to replace the NNNYYYYMMDDHHMM with a wildcard (for example *), so that import will pull ANY .SDF files in, but it will not run. i get the following:
output ------------------------------------------------------------------------------ DB-LIBRARY error: Bcp: Unable to open host data-file.
I need to replace the use of wild cards in my query with something else which achieves the same thing. The problem is the web application which uses the query does throws an error when using '%' characters. Any ideas?
The following statement appears in the where clause:
AccType.Value like '@Opened_By[%DIST%APP% as Distance and Business Provider, DIST% as Distance, APP% as Business Provider]'
In a previous post, someone helped me with creating stored procedures, and I am grateful because I am transitioning from the Access World. Anyway, I get an error at .ExecuteNonQuery in visual studio 2005 when I run the following code: Dim strsql As String Dim strconn As String strsql = "sp_Roster" strconn = "server=xxxx; user=xxxx; pwd=xxxx; database=xxxx;" With comm .Connection = New SqlConnection(strconn) .CommandText = strsql .CommandType = CommandType.StoredProcedure With .Parameters.Add("TeacherID", SqlDbType.Char) .Value = "DawsMark@aol.com" End With With .Parameters.Add("ClassID", SqlDbType.Int) .Value = classid End With With .Parameters.Add("sID", SqlDbType.Int) .Value = ssID End With With .Parameters.Add("sLastName", SqlDbType.Char) .Value = lastname End With With .Parameters.Add("sFirstName", SqlDbType.Char) .Value = firstname End With With .Parameters.Add("sMiddleName", SqlDbType.Char) .Value = middlename End With With .Parameters.Add("Student", SqlDbType.Char) .Value = fullname End With With .Parameters.Add("Password", SqlDbType.Char) .Value = password End With .Connection.Open() .ExecuteNonQuery() With comm.Connection If .State = ConnectionState.Open Then .Close() End If End With End With The error was: Error converting data type char to int. The stored procedure in sql server was as follows CREATE PROCEDURE sp_Roster -- Add the parameters for the stored procedure here@TeacherID varchar(50),@ClassID int,@sID int,@sLastName varchar(50),@sFirstName varchar(50),@sMiddleName varchar(50),@Student varchar(50),@Password varchar(50)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@TeacherID, @ClassID, @sID, @sLastName, @sFirstName, @sMiddleName, @Student, @Password)ENDGO The error also says "sqlexception was unhandled by user code. This is strange because this code worked perfectly when connecting to Access and when I used oledb. So how is it the code's problem? Is the stored procedure causing the error or the code. Can someone please help. thanks. <Edited by Dinakar Nethi> Please mask your useird/pwd info in the connection string when posting to a public forum like this </Edit>
I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1 to Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx
Every morning our sql server runs very slowly which means our log on page times out on a simple query. If we stop and start the sql server everything runs fine for the rest of the day until the follwing morning. the server is not used out of business hours excpet for a few very small and simple jobs to delete records, These all run to completion. Any help with this would be much aprreciated! Thanks. Rob
tblDocumentApprovals userID INT documentID INT approvalDate DATETIME
If I want to get a list of documents, and the users who've signed them off (if any), I'd do something like:
SELECT [tblDocuments].[documentName], [tblUsers].[userName ], [tblDocumentApprovals].[approvalDate ] FROM [tblDocuments] LEFT JOIN [tblDocumentApprovals] ON [tblDocumentApprovals].[documentID] = [tblDocuments.id] INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblDocumentApprovals].[userID]
...which is lovely. Except - I don't want a row returned for each user that's signed it off. I want one row for each document, with a field containing a list of the people who've signed it off.
I know that it's bad design. I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.
Hi, I have seen many examples at MSDN library related to SQL Querries in all queries one thing is same the way they use tables in there queries. BUT wht is this really i am not getting this, can anyone tell me.. the code and the problem is as follows:
Code Snippet FROM Purchasing.ProductVendor JOIN Purchasing.Vendor ON (ProductVendor.VendorID = Vendor.VendorID)In the above code u see "Purchasing.ProductVendor", I want to ask that wht is this Purchasing Stands for, If we suppose that purxchasing is the database name then also normally we use database name as Purchasing.dbo.ProductVendor BUT I am not getting that what is this, Please if someone knows then explain it to me, Thanks.
I’m trying to use case statement in my view with wildcards for '%Tradies%', instead of listing all items
WHEN 'Tradies Rebate1' THEN 'Test' WHEN 'Tradies Rebate2' THEN 'Test' WHEN 'Tradies Rebate3' THEN 'Test' WHEN 'Tradies Rebate4' THEN 'Test'
At this moment '%Tradies%' does not work and gives me null values in EventGroup column.
Here’s my statemnt ------------------------------------------ CASE [dbo].[Event].[EventName] --WHEN 'Tradies Rebate1' THEN 'Test' --WHEN 'Tradies Rebate2' THEN 'Test' --WHEN 'Tradies Rebate3' THEN 'Test' --WHEN 'Tradies Rebate4' THEN 'Test' WHEN '%Tradies%' THEN 'Test' WHEN 'Install Products' THEN 'All Installed' WHEN 'Installation Product Conversion' THEN 'All Installed' WHEN 'Installation Products' THEN 'All Installed' WHEN 'BK 3' THEN 'All Bright Kids' END AS [EventGroup], ------------------------------------------
I have a SQL statement which is generated dynamically. I need to know what is the correct syntax for this
WHERE status = 'open' AND salesman = * AND dat = * AND customername = *
i.e. fetch everything WHERE status = 'open'
I know that simply WHERE status = 'open' would do the trick but I need it like the first example because of the way the statement is being generated i.e. this salesmen bit is like this.
If Salesman <> "*" Then sql2 &= " AND salesman = '" & Salesman & "'" Else sql2 &= " AND salesman = *" End If
Hi I'm using the full-text indexing on a table and I'm trying to implement a search where users can search for words and use wildcards themselves. However I'm working on a method so that can enter a wildcard in the middle of a word to get records where they are unsure of the spelling etc. For instance, a search of 'Ste*en' should return results like 'Steven' and 'Stephen' etc. So if they are searching for word 'establishment' they can search for 'estab*ment' and it should return all the records using this query: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*ment"') If I do a wildcard at the end e.g: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*"') I get the results I am looking for. But the middle wildcard does not seem to work as expected even though it is the syntax used on MSDN and other SQL info sites. Is there something I am not doing properly?
I have a stored procdure in SQL Sever that accepts paramteres. I am trying to return rows where parameter that is passed is somewhere in the cuustomer's name. Without the variable the SQL would look like this:
SELECT * FROM tbl WHERE CustomerName LIKE '%Smith%'
I can't figure out how to replace LIKE '%Smith%' with a varible. I tried '%@CustomerName%', ('%' + @CustomerName + '%') and neither works. Any ideas? Thanks
ps my column's type is char(50) and so is the variable so trailing spaces don't matter.
Hi all,I am creating an ASP.NET site, and I'm having lots of issues trying to get wildcards to work with the following query:DECLARE @Status varcharDECLARE @AssignedTo intDECLARE @AppID intSELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority, dbo.Application.ApplicationFROM dbo.Issue INNER JOIN dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationIDWHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (dbo.Application.ApplicationID LIKE '%' + @AppID)ORDER BY dbo.Priority.PriorityCode When running this through query analyser I get the error:Server: Msg 245, Level 16, State 1, Line 5Syntax error converting the varchar value '%' to a column of data type int. Could someone help me understand this please?Thanks
How do Iput wildcards around a number in an sp ? If my user leaves BoxNo blank it will list all boxes
SELECT * FROM tblFiles WHERE ConNo =@strRMUConsignmentNo and FileRef like '%'+@strtxtFileRef+'%' and Subject like '%'+@strtxtSubject+'%' and FileDescription like '%'+@strtxtDescription+'%' and BoxNo like %+@strBoxNo%
My use of wildcards thus far has been limited to matching a givenstring anywhere in a column as follows:SELECT * FROM Table WHERE Column LIKE '%string%'However, I'm wondering if there's a way to do this in reverse. Thatis, is there a way to match the column anywhere in the string?Pseudo-coding it as:SELECT * FROM Table WHERE 'string' LIKE %Column%What I'm trying to match is network addresses. Most of the storedaddresses in this table are exact (i.e. ip-1-2-3-4.location.isp.com)but sometimes they encompass an entire group (i.e. location.isp.com).When an exact address is given in the code I'm writing, it needs tomatch any rows that contain its exact self or contain a shortenedversion of which it is part.Any ideas?-cyber0nehttp://www.cyber0ne.com