Wildcard Syntax NO RESULTS WHEN Using Preview Data

Feb 28, 2007

Hello

 I have come across a problem today when working with data sets/table adapter in vs05 for use in ASP.net.

SELECT  ws_Agent.Telephone, ws_Agent.Email, ws_Agent.Name, ws_Agent.AgentId, ws_Agent.forename, ws_Agent.surname, ws_Agent.Acc_PcentreID, ws_ProfitCentre.ShortCode, ws_Branch.Name AS branchname, ws_Branch.ManagerId,
            ws_Branch.Fax AS branchFax, ws_Branch.Telephone AS branchtelephone, ws_Branch.Address1, ws_Branch.Address4, ws_Branch.Postcode, ws_ProfitCentre.Telephone AS PCentreTelephone,
            ws_ProfitCentre.Status AS PCentreStatus, ws_ProfitCentre.Fax AS PCentreFax, ws_ProfitCentre.Email AS PCentreEmail, ws_Agent.Status, ws_Agent.DisableAccount, ws_ProfitCentre.PCentreID,
            ws_Branch.Status AS BranchStatus
FROM    ws_Agent INNER JOIN
            ws_ProfitCentre ON ws_Agent.Acc_PcentreID = ws_ProfitCentre.PCentreID INNER JOIN
            ws_Branch ON ws_ProfitCentre.BranchID = ws_Branch.BranchId
WHERE  (ws_Agent.Status <> 3) AND (ws_ProfitCentre.Status = 0) AND (ws_Agent.forename LIKE  @forename) AND (ws_Agent.surname LIKE  @surname ) AND
            (ws_ProfitCentre.ShortCode LIKE  @profitcentreshortcode) AND (ws_Branch.Name LIKE @branchname )

The wildcard syntax works fine when tested in the query builder when passing in % to the parameters

 , but does not work correctly NO RESULTS WHEN using preview data and in the subsequent use of data set in code.

Can anyone please comment on this or is there a setting I have missed?

Regards,

Matthew

View 2 Replies


ADVERTISEMENT

Preview Data, It Gives You Incorrect Results

Apr 25, 2007

I am reposting this to a different forum, becasue I got not response...
Using VS05 SP1 Pro SQL Express€¦
Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....
SELECT fld_IX_UserID
FROM UserIDs
WHERE (fld_UserID_Table_ID = @USERID_TABLE_ID)
It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.
Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.
In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.
Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.
Thanks,
Bob

View 3 Replies View Related

SQL Syntax Assistance Needed For A Wildcard

Dec 20, 2007

I have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter".  The Branch Parameter is a numeric field.
I need (Branch = %) wildcard to select all the records.  Can someone tell me the proper syntax for this?
SELECT     EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM         dbo.vw_Compliance_Percentage_EmployeeWHERE     (Branch = @branch)ORDER BY Branch
 I receive the message failed to convert parameter value from string to an int32

View 7 Replies View Related

Question About Different Query Results With Wildcard

Dec 22, 2006

Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.

There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.

Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results


Could someone explain or point me to more documentation on the difference results we get?
Thanks

View 5 Replies View Related

Import Export Wizard - Syntax Error In Preview &&amp; Execution

Jan 3, 2008

Source: MS SQL Server 6.5
Destination: MS SQL Server 2005 (SP2 installed)
Suppl Ver Info: Microsoft.SqlServer.MaintenancePlanTasks.dll (ver 9.0.3043.0)


Desc:
When copying one table from source to destination (using import export wizard), the following error shows. However, when using query select * from tbl_1 can shows preview correctly. (but still need to save as SSIS, and change codepage property before it works)


===================================

The preview data could not be retrieved. (SQL Server Import and Export Wizard)

===================================

Line 1: Incorrect syntax near '['. (Microsoft OLE DB Provider for SQL Server)

------------------------------
Program Location:

at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
at System.Data.OleDb.OleDbDataReader.NextResult()
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
at Microsoft.SqlServer.Dts.DtsWizard.Preview.LoadPreview()

When executing the wizard, the following error shows (the table exists, of course)


===================================

Could not connect source component.

Error 0xc0202009: Source - tbl_1[1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.


Error 0xc02020e8: Source - tbl_1[1]: Opening a rowset for "[db_1].[sch_1].[tbl_1]" failed. Check that the object exists in the database.
(SQL Server Import and Export Wizard)

===================================

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.SqlServer.Dts.DtsWizard.Execute.SetSourceConnection(Exception& ex, TransformInfo ti)




Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)


Could anybody please kindly help me on that?

View 2 Replies View Related

No Data On The Preview Tab

Mar 6, 2007

Hi,

I have a problem. I have developed a report. When i see the preview of the report there is no data coming up. On the other hand, when I deploy the report and execute the report from the report manager, I am able to see data for the report. It is pointing to the same database.

I feel i am missing something. Any help in this regard is welcome.

Thanks in Advance.

Viva

View 2 Replies View Related

Can Not Preview Data From Oracle

Apr 28, 2008

I am using Reporting Services 2005, Visual Studio 2005, and Oracle 8.x client.
I have an issue when previewing the report in Visual Studio. I do not receive any errors€¦.. I just don€™t see any data. The strange thing is when I deploy the report and run the report in Report Manager there is data.
I added the TNS and I set the credentials.

View 1 Replies View Related

Preview Has Data But No Columns Returned

Oct 4, 2006

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

View 6 Replies View Related

No Layout, Data, Preview Tabs In VS 2005

Apr 8, 2008



I've inherited a SSRS project from a former employee. I've gotten the reports moved to the Report Server on my local machine (which runs Sql Server 2005 with SSRS), and other users are having no problems viewing reports.

However, when I try to create a new Report Project using the Business Intelligence Project type in VS 2005, I can't view the RDL files in any way except plain-old XML. I have no Data | Layout | Preview tabs. If I create the RDL using the Report Wizard, I can still take the rdl files and deploy them successfully. But I can never edit them in Layout mode. Is something misconfigured or not installed properly? Thanks for your help.

View 5 Replies View Related

Integration Services :: Error When Preview Data On SSIS?

Jun 3, 2015

error[42000][mysql][odbc 5.3 (a) driver] mysqld-5.6.21 -log] where preview a data

View 2 Replies View Related

Print Preview Pagination For Multipage Data In Single Text Box

Dec 19, 2005

I have a tried two different reports one with table and the other without The last textbox in both reports contains enough text which should fill half of first page and half of second page. In both reports the print preview and export to PDF keeps the first page blank and puts all the data in the second page. It appears that the textbox control doesn't know when to place a pagebreak when in print preview and export to PDF. The rdl file has no pagebreaks defined anywhere. Thanks in advance for your help!

 

View 14 Replies View Related

Integration Services :: Can't Preview Data In Excel Source Editor Unless Sheet Is Open?

Nov 10, 2010

I have a package which has an Excel source with the 'Data access mode' set to SQL command and then a sql select statement.  When I try and hit the 'Preview...' button below the 'SQL command text' window I get the following error:

 "Error at Standard Data Flow Tasks [source tasks name]: No column information was returned by the SQL command"
 
Ordinarily this would be down to the fact that my SQL is shocking, I hit the 'Preview...' button whilst the workbook the source is pointing at was open and it works fine??
 
I can't figure this out, but needless to say the package errors with a NEEDSNEWMETADATA when I try and run it.

View 17 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

Reporting Services :: Report Exported To Excel - In Print Preview Footer Text Box Data Truncated

May 6, 2015

I am exporting SSRS report to Excel I am aware that excel doesn't show footer , It shows in print preview but my footer has text box which have text disclaimer more than 255 characters, the data getting truncated.

View 3 Replies View Related

Alter Column Results In Incorrect Syntax Near &#39;column&#39;

Oct 29, 2001

I ran this query against the pubs database and it runs successfully

ALTER TABLE publishers ALTER COLUMN state CHAR(25)

I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)

and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.

My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.

View 1 Replies View Related

System.Data.SqlClient.SqlException: Syntax Error Converting The Varchar Value 'V' To A Column Of Data Type Int

Aug 31, 2006

 I am using  a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure;  SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse

View 3 Replies View Related

Wildcard Use

Jun 30, 2006

Hello everyone and thanks for your help in advance.  I am working on an application that does a property search off of a database that contains approximately 40000 records.  The search criteria allows the use to specify a minimum and maximum price, subdivision name, number of bedrooms, etc.  I set up a stored procedure to query the databse.  if one of the parameters is not specified, i simply pass it a "%".  However, when i execute this sproc in Query Analyzer, it takes in excess of 10 seconds to return the records, even  if only one or two are returned.  I am assuming this is due to the use of the wildcard character when the user does not have a preference, but I am not sure of any other way to do this.  Any help on this topic would be grealy appreciated.

View 3 Replies View Related

Wildcard

Sep 9, 2006

hi,i have an sqldatasource a gridview and dropdownlistthe gridview is updated on the selectedIndexChanged Event of the dropdownlistmy goal is to add an item in the dropdownlist with the name ALLwhich should matches all the records in the databasei tried to put the value of the all item = * and % but neither seems to workany help on what could be going wrong would be appreciated

View 7 Replies View Related

Looking For A Wildcard

May 23, 2003

hi,

how do i contsruct a SQL query to search for the % character in a data field?

thanks

View 1 Replies View Related

CONTAINS And Wildcard

Oct 29, 2007

Is it a good idea to have multiple contains? I have this query:Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')Also, I tried this, didn't return anything:Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')Both Col1 and Col2 has the string 'Africa' and 'African' in it.--sharif

View 2 Replies View Related

Using Wildcard

Aug 22, 2006



alter procedure sp_ADsearch @tbname varchar(1000),@searchproduct nvarchar(500)

as

declare @sqlstr nvarchar(1000)

set @sqlstr='select * from '

set @sqlstr= @sqlstr + @tbname

set @sqlstr= @sqlstr + ' where name like '

set @sqlstr= @sqlstr + '%' + @searchproduct + '%'

exec sp_executesql @sqlstr

--

exec sp_ADsearch product,ee

when executing the procedure i am getting an error "Incorrect syntax near 'ee'.".

whats wrong with the syntax? how to use % along with variables

View 5 Replies View Related

Wildcard Used For SQL IN Clause

Dec 30, 2006

I am trying to control how users view records.  I want to create a solution that would, for instance permit:user A to view Store 1user B to view store 2 and store 3user C to view store 5 and store 6and User D to view all stores even if more stores were added in the future (in other words user D would have access to all records)
I want to create an 'authorization table' so that different users can see different records.  I think the easiest way to do this is to pass a parameter to the where clause, but the problem that I face is the how can I use a wildcard in a SQL 'IN' clause?  Does anyone have any suggestions.  Perhaps I am taking the wrong approach.  I would appreciate any guidance.

View 2 Replies View Related

Wildcard In The WHERE Clause

Jan 28, 2007

Hello,
I'm trying to pull some data from a table with the option to filter on 2 columns.
I've set up my sql statement to accept 2 parameters and I'd like to be able to send 1 or 2 wildcards if needed. My statement looks like this:SELECT *
FROM City WHERE CityName= @CityName AND State= @State
ORDER BY CityName
For example, if you wanted all of the cities in all of the states I would pass (*,*) as parameters.
Or if I wanted to see all of the states that have a city named Richmond, I'd pass (Richmond,*) as the parameters.
The wildcards are not returning anything and I don't know why. It works fine if I pass something like (Indianapolis,Indiana) as parameters so I think it's in my use of the wildcards that is wrong.
 Thanks.

View 4 Replies View Related

Wildcard For Integers Or Something?

Dec 10, 2001

I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.

Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.

Does anyone have any ideas here?

Thanks,
Al

View 2 Replies View Related

Wildcard Misuse?

Oct 5, 2006

I've got a text field with a list of dates in this format:

10/31/2006
11/2/2006
11/4/2006
11/5/2006

The field can contain multiple dates (as listed above). I need to query the db and retrieve dates by month and year. For example, all fields containing 10/%/06 or 11/%/%06.

My query:

SELECT * FROM tblCalData WHERE visible='1' and approved='1' and eventDates LIKE '11%06%' ORDER BY eventDates DESC

so even though a field contains dates in October and November, selecting all November dates, in this example, don't appear. The query seems to be looking only at the first date.

Is this possible? Is there another/better way to do this?

Thanks!

:grimey

View 14 Replies View Related

Is There A Way To Use Wildcard Characters...

Feb 1, 2006

to reference columns.
i have a table that has a column that is repeated 25 times, sort of.

table_1
tbl_ID (PK)
tbl_c_1 char 5
tbl_c_2 char 5
tbl_c_3 char 5
tbl_c_4 char 5
tbl_c_5 char 5
....
tbl_c_25 char 5

everytime i want to query for any of tbl_c_? that contain a specific value i have to reference all 25 in my query. is there a better way?
I cannot change the table.

View 6 Replies View Related

WildCard If Not Value Selected

May 7, 2008

HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,
Trudye

View 11 Replies View Related

Wildcard In Query?

Dec 6, 2007

I screwed up my database and double added data to a text field.

For example I need made this:

'http://192.168.200.1/images/' +PATH+ '.jpg'

Look like this:

'http://192.168.200.1/images/http://192.168.200.1/images/12345.jpg'

How can I query to reset this?

update perimage
set PERIMAGE_PATH = 'http://192.168.200.1/images/' +PATH+ '.jpg'
WHERE PERIMAGE_PATH <> 'http://192.168.200.1/images/' +PATH+ '.jpg'

That just makes the problem worse

View 3 Replies View Related

Wildcard In Query?

Dec 11, 2007

I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*

Im basically trying to exclude creating duplicates, where this path already exists.

Here is my code:


INSERT INTO IMAGE (FCN, IMAGE_NAME2)
SELECT FCN, Col066
FROM GRAB where Col066 <> ' '
update IMAGE
Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*

What is the proper code to do this. I know the last line does not work. Thanks

View 7 Replies View Related

Using Wildcard With Parameters

Oct 24, 2007



The where statement on my stored proc is as follows:

Where actv.ProjID + '-' + actv.Activity = @project

@project is something the user provides in the form of "xxxx-xx-xx-xx". I would like to use a wildcard, so I changed my where statement to the following:

Where actv.ProjID + '-' + actv.Activity = @project + '%'

But this returns nothing and I am not sure why. I don't get any errors, just no results. Anyone got any ideas as to why?

Thanks in advance!

View 9 Replies View Related

Wildcard Ssl Certificates?

Apr 27, 2007

Does SQL Server support wildcard Certificates. When you install the wild cert in the certificate store, the sql configuration manager does not see it in its drop down list. Id it does, what are the steps or please point me to the right direction. Does the cert need to be specifically for that particular hostname. Thanks

View 1 Replies View Related

Query On SQL CE 3.5 With Wildcard

Jan 9, 2008

Hi,

I have a problem with SQL CE 3.5 and VS 2005. When I execute query with parameters and wildcard, I have an error : FormatException.
Here is my query with parameters :
Query: SELECT "PARAGRAPHES"."PARA_ID" AS "Numero","PARAGRAPHES"."PARA_SECT_ID" AS "Section","PARAGRAPHES"."PARA_NUMERO" AS "Rang","PARAGRAPHES"."PARA_GROUPE" AS "Groupe","PARAGRAPHES"."PARA_ENTITES" AS "Entites","PARAGRAPHES"."PARA_LBL_COURT" AS "Court","PARAGRAPHES"."PARA_LBL_LONG" AS "Long","PARAGRAPHES"."PARA_ACTIVE" AS "Active","PARAGRAPHES"."PARA_CONDITIONS" AS "Conditions","PARAGRAPHES"."PARA_PARENTS" AS "Parents","PARAGRAPHES"."PARA_QUESTIONS" AS "QuestionsConditions" FROM "PARAGRAPHES" WHERE ( ( ( ( "PARAGRAPHES"."PARA_SECT_ID" = ? AND ( ( "PARAGRAPHES"."PARA_ENTITES" LIKE ?) OR "PARAGRAPHES"."PARA_ENTITES" IS NULL))) AND "PARAGRAPHES"."PARA_ID" IN (SELECT "DOSSIER_LINKS"."DOLI_PARA_ID" AS "Paragraphe" FROM "DOSSIER_LINKS" WHERE "DOSSIER_LINKS"."DOLI_DOSS_ID" = ?) AND "PARAGRAPHES"."PARA_NUMERO" = ?))
Parameter: @Section1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 52.
Parameter: @Entites2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%D%".
Parameter: @Dossier3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @Rang4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
and my error :
[System.FormatException] = {"@Entites2 : %D% - FormatException"}

If I execute query in Query Analyzer (without parameter : values directly on query) there is no errors.

ThanksFabien

View 1 Replies View Related

Wildcard Searches

May 13, 2006

I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.

All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]

@dt_src_date = N'04/28/2006',

@chr_div = N'NE',

@vch_portfolio_no = 3,

@vch_prop_cat = N'core'

SELECT 'Return Value' = @return_value

GO

How can I set this so that it will wild card the value. For example rather than having to specify

@chr_div = N'NE', I could specify something like

@chr_div = N *, so it would show both NE and SW values in the result set.

Anybody point me in a direction here. I have tried % but that does not seem to work, I get a

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '%'.

Thank in Advance

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved