I'm working on a SQL report and am returning a list of values for a column/field, "STATUS", to be used as one of the search parameter.
SELECT DISTINCT STATUS FROM [table]
ORDER BY STATUS
Values returned are [blank], approved, onhold, etc.How can I get the [blank] value to be set as "All" within the report parameter for STATUS?
hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ??? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
???? Any suggestions for measuring the actual running value of this pool -
memtoleave ??
best regards /ti
I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.
So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.
SELECT measures.turnover ON COLUMNS,
nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS
FROM [Itdev1 Hk]
I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.
Now, how do I get it to default to the last valid member in the list?
Hello guys I hope everyone is doing well I have a question. I have a database thats an ADP. on one of the forms I have a command button that has a stored procedure in it that will allow them to search by TM# or name or whatever, (I have the code below) I would like them to be able to do the samething but by date rather then TM# or Name, how would I go about doing that with a datetime datatype??
thank you
ALTER PROCEDURE dbo.Search_ActiveConditionals
(@Enter_TM# int)
AS SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, INVESTIGATOR_COND, REASONFORCOND
FROM dbo.ACTIVE_CONDITIONALS
WHERE (TM# = @Enter_TM#)
I have a Full Text query that works fine when structured as follows:
SELECT First_Name, Middle_Name, Last_Name, Hire_Date, City, Department, Phone_Ext, title, Fax, Secretary_Name, Attorney_Name, Secy_Ext, Home_Phone, Desk_Location, Law_School, Undergraduate_School, Languages, E_mail, CMS_ID, WEB_ID, Notary
FROM dbo.PhotoDir
WHERE CONTAINS (*, 'Jones')
ORDER BY Last_Name, First_Name
However, I would like to replace "Jones" with a parameter, @LName for example. However, I can't figure out the syntax. I tried the following:
WHERE CONTAINS (*, @LName)
but received the following error:
The @LName SQL construct or statement is not supported.
Is there a way to put a parameter in this type of query?
Thanks in advance.
How can I use a parameter in a free text search. I would expect it to look like this:
-CREATE PROCEDURE searchProducts
-
-@worlist varchar(40)
-
-AS
-
-SELECT PRProductID, PRDescription FROM tbProducts WHERE CONTAINS
- (PRDescription, @wordlist)
but I get an error checking the syntax. I've tried all sorts of combinations of ' and ".
Say I have a query like
DECLARE @ID UNIQUEIDENTIFIER, @SOMEDATE DATE
SELECT * FROM myTable WHERE ID = @ID AND DATEFIELD=@SOMEDATE
I want to pass values to @ID and @SOMEDATE, such that it meets the WHERE criteria for all values in the respective fields.
What parameter value should I pass such that all values are selected? In the actual SP, I have uniqueidentifier, varchar and date parameters.
I have an issue trying to pass a search text parameter to FREETEXTTABLE via Dataset.
The following code works fine if you hardcode the search word/text as shown:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, 'cool') AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
Now, I want to do this:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, @Search) AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
The error I'm getting is @Search is not declared. How am I suppose to pass in a value?
I have searched almost everywhere and nobody seemed to ask this precise question. I'm sure this is a huge problem.
Can anyone help me please?
Hi guys, first post here. It's been a few years since I've worked with ODBC, so I'm a bit rusty. I'm having trouble figuring out how to use a char* string as a parameter for a SELECT statement. I'm trying to compare the contents of a varchar column (with size=20) against the string.
Here's the code that I'm using:
char* arg;
SQLINTEGER stringSize = 32;
retcode = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_VARCHAR,
20,
0,
arg,
stringSize,
&stringSize);
retcode = SQLPrepare(hstmt,
(SQLCHAR*) "SELECT * "
"FROM myTable "
"WHERE (stringColumn = '?')",
SQL_NTS);
retcode = SQLExecute(hstmt);
retcode = SQLFetch(hstmt);
When SQLFetch() runs, it returns 100 (SQL_NO_DATA). If I type the query manually into my Enterprise Manager console, I get several results, so I know the query is correct. Any idea what I might be doing wrong with the ODBC calls?
Thanks!
I am new for SQL SP, I have three table,
Table 1 - Merchant
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
Table 2 - Region
RegionID RegionName
1 Region1
2 Region2
3 Region3
Table 3 - Offer
OfferID RegionID MerchantID
1 1 3
2 1 2
3 1 1
4 2 2
5 2 4
I have two input parameter for this SP, @MerchantName = NULL and @RegionName = NULL
Expected Result Table
If input parameter @MerchantName = NULL and @RegionName = NULL
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
If we Search by RegionName="Region1" Ex: @MerchantName = NULL and @RegionName = 'Region1'
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
If we Search by RegionName="Region2" Ex: @MerchantName = NULL and @RegionName = 'Region2'
MerchantID MerchantName Zip
2 Merchant2 1002
4 Merchant4 1004
If we search by MerchantName='Merchant1' Ex: @MerchantName = 'Merchant1' and @RegionName = ''
MerchantID MerchantName Zip
1 Merchant1 1001
How to get the expected result for the above scenario.
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
expected results.....:
check
keywords
Hi there,
I have a problem with multi-value parameter in ReportingServices2005, hope to find a solution here.
if I use ordinary parameter in a report(multi-value checkbox is not selected), after deployment I can click on drop down list and type possible value of that parameter using keyboard(it will be selected from what i typed).
if I use multi-value parameter somewhy it doesn't respond to anything i type from my keyboard.
Why is that?
What should i do to make that multi-value parameter select possible result from what i type?
I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle
(@ProductTitle varchar(255))
AS
SELECT ProductTitle
FROM tbl_Product
WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle)
My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
I have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions.
Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this.
with CustomGroup( id)
as
(
Select id from Groups g
Inner join GroupStatus s on s.Id = g.Id
Where s.Status = €˜Active€™
)
IF @DisplyStatus=€?UserDetail€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.* from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserSimple€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.FirstName,u.LastName,u.DOB from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserWithAddress€?
begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
View 9 Replies View RelatedHi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
View 6 Replies View RelatedGreeting SQLers,
I'm attempting to define a Default value for a new user-defined-datatype in SQL Server 6.5, but can't as the dropdown list only allow (none) value!
Anyone can help? TIA.
How to define schemaname & how to qualify schemaname in SQL
statement.
Hello, everyone:
I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD
Thanks a lot.
ZYT
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets()
On Error Resume Next
'Dim pt As PivotTable
Dim Sh As Worksheet
Dim refresh As Boolean
Dim tempMode As Boolean
Dim startTime As Date
[code]...
Is there a way to define a key that puts the text 'SELECT * FROM 'into the Query Analyzer window?I must type this about 50 times a day but cannot see a simple way ofdefining a key to write it for me..(tools/customize) seems to execute everything you put in there ratherthan leave it on the screen for me to add table names etc to.thanks for your time...
View 2 Replies View Relatedhallo
i am on the very beginning with sql.s,
how can i define a default value for the table field like its possible in access,
for example: the if the user not gonna enter a val so there is a default val predefined in the table field,
for example i would like to predefine the date now on the smalldate field....
thanks
Hi, all experts here,
I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?
Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi
I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId
The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Art %>"
SelectCommand="Select * from Option Where SourceId=@SourceId And CatId=@CatId">
<SelectParameters>
<asp:ControlParameter ControlID="Drop1" Name="SourceId" />
<asp:ControlParameter ControlID="Drop1" Name="CatId" />
</SelectParameters>
</asp:SqlDataSource>
Can anyone help me to define the parameters?
thanks
I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
View 1 Replies View RelatedWhy is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
thank you
Hi, I am trying to connect to my local SQL Server through a webform ( on VS.NET ). My web.config shows something like this -
sqlConnectionString="data source=IP address;Trusted_Connection=yes"and the tutorial I am following says something this - SqlConnection myConnection = new SqlConnection( "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI");If I just replace the NetSDK either with my IP address or the servername (my machine name), I get a SQLException that server doesn't exist or access denied. I have specified "windows authentication". How do I correctly define my connection string ? Please guide me.Thanks,sbs.
I noticed in SQL ODBC API reference that SQLGetTypeInfo would return true for AUTO_INCREMENT if a smallint field is defined as autoincrement. Is there a simple way to set a field autoincrement thru the SQL Server`s front end?
T.G.
One more silly question,
What is an identity column? how can I define it?
table abc
=========
no (primary key) name
================ ====
1 jung
2 love
4 tom
In this example, I want to define 'no' as an identify column so that I can update it as I wish.
Thanks in advance,
-----------------------------------------------------------------------------
Subject:
From:
Date: sorry one more question (reply)
Kurt ()
6/23/01 2:48:32 AM
Jung,
Take a look at your table. It looks like you don't have an identity column
defined for "no" (your column name). Set Indentity_Insert doesn't work for tables that don't have one defined. PS A primary key is not the same as an Idendtity column.
------------
jung at 6/22/01 9:49:34 PM
table : abc
no (primary key) name
================ ====
1 jung
2 love
4 tom
In this record, I wanted to update no 4 to 3 which is primary key.
So, I used the query as below.
=============================
Set Identity_insert abc on
UPDATE abc
SET no = 3
WHERE no = 4
Set Identity_insert abc off
go
=============================
But I got an error;
Server: Msg 8106, Level 16, State 1, Line 1
Table 'abc' does not have the identity property. Cannot perform SET operation.
Why this happens? I really want to update the primary key.
Fazlul Haq writes "i m new to sql server2000.my question is
" I CREATE A TABLE IN WHICH THREE COLUMS EXIST i.e SNO(NUMERIC),NAME(CHAR),FNAME(CHAR).I WANT TO ENTER VALUE IN SNO FIELD STARTING FROM ZERO(0)e.g 011,021,022 etc and when enter value without starting from zero an error messag is appered on the screen.I WANT THAT THIS CAN BE DONE BY CREATING A "FUNCTION" OR "RULE" OR "STORED PROCEDURE". PLZ HELP ME ""
Hi
I have a question about user define function in sql.
How can I use Exec in UDF?
What I mean is that I made a string in a UDF, And I need to
execute the string in function. but I think it's illegal.
so tell me how can I use it?
P.S : forget about useing stored procedure instead of function.
thanx.
I am designing a new table with a few columns that may or may not havea value on each row that is inserted.What issues determine whether to allow a NULL value to be inserted forthat column or define a default value to be used?I want to think through the repercussions of this decision before I getinto production.
View 3 Replies View Related