Big Queries Return Empty Result Set
Apr 22, 2004
Hello guys,
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.
View 5 Replies
ADVERTISEMENT
Mar 13, 2008
I'm querying my database with a datetime constraint, only picking up records newer than the last ETL. Although I doubt it would ever happen I was going to put a check in the data flow to trap the case when NO rows were read due to inactivity in the OLAP. I set up a row count and tried to trap it. Apparently if you use an OLE DB source to get a result set and that result set is empty, the program flow bypasses everything and keeps running until it hits something causing an error, like an SQL Insert or Update command.
Is there any way to trap this condition? Currently it is going to cause the program to fail which will cause a snapshot restore (after kicking everyone out of the database).
Thanks.
View 8 Replies
View Related
Mar 23, 2007
Enviroment
O.S. = Windows Small Business 2003
DB = SQL 2000 Server with SP3a
No windows or third party firewalls that are set up.
Problem:
When performing a Link Table in MS Access 2003 it runs into this error message "The Microsoft Jet Database engine could not find the object 'zstblSetup' (the name of our table). Make sure the object exists and that you spell its name and path name correctly.
But when we try to another server but at this time the SQL 2000 is patch with SP4, the link table works fine in Ms Access 2003. So we decided to use SQL Profiler to trace the cause of the problem. It so happen when sp_columns N'zstblSetup' is called no result is returned. Going through the code in sp_columns, it calls spt_datatype_info. When calling spt_datatype_info, again no record is returned. We suspect that spt_datatype_info is the culprit of the problem that's why Link Table in MS Access 2003 doesn't work.
Calling the sp_columns and spt_datatype_info into the working server that I mentioned aboved can return results. The way I understand it spt_datatype_info holds the SQL data type info.
My question is if we apply SP4 in the server that is problematic will this problem be resolved? Or are there any possible reason to solve this problem?
View 4 Replies
View Related
Apr 7, 2015
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
View 6 Replies
View Related
Aug 15, 2006
Hi,
I'm trying check the records which satisfies the following condn.
select @p1 = su1 from RER where (no=10 and atp ='N')
It returns empty list . bcos no match found.that's ok.
But i need to write the logic if record is not there.
i tried to do as follows
if ((@p1 =null) or (p1=' '))
...some logic
else
.. other logic
It's always executing else case. How do i catch empty value using sql query.
Is there any command there like null?
I tried in SQL query analyzer. Using SQL 2000.
Thank You
View 6 Replies
View Related
Mar 19, 2003
I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried:
select left(s2.name,32) as TableName, max(s1.rows) as Records
from sysindexes s1
inner join sysobjects s2 on s1.id=s2.id
where type = 'U' and s2.name like 'New%'
group by s2.name
HAVING max(rows) = 0
ORDER BY TableName
but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like:
TableNameRows
NewARTxn0
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
Thanks for the suggestions.
Al
View 3 Replies
View Related
Jun 11, 2008
Hi,
I have some sql and i expect a row back with some information even if there is nothing. What i want to happen is when it returns an empty row then give me a 0 so at least i get something back I am filling a dataset here and it isnt populating the fields when an empty row is returned.
SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT count(*) FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ........
So ive tried variations of the below in a CASE statement WHEN NUll or WHEN 0 but i still get nothing back. I would like the information to return 3 2003 Mar 0 if its empty.
SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT CASE count(*) WHEN NULL THEN 0 ELSE count(*) END FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ............
Can anyone help me please??
View 8 Replies
View Related
Feb 20, 2015
I have a query that returns contacts connected to a client, but the problem is that it's only returning a result when a contact is associated with a client, even if the client does exist in the db. i want it to still return the client if the client exists.
SELECT * FROM clients, addressbook where clients.clientid = addressbook.clientid AND (clients.clientname LIKE '" . strtoupper($_GET['txtfname'])."%')
View 1 Replies
View Related
Aug 1, 2007
hi,
i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case.
How to catch the resultset of SQLDataSource?
-nero
View 1 Replies
View Related
Mar 13, 2014
I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.
select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]
View 1 Replies
View Related
Oct 7, 2014
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.
FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.
View 4 Replies
View Related
Sep 26, 2006
I wrote 2 sql queries in two different ways.
The only difference in both queries are One using '=' and other one using 'in'.
All the other data is same.In my sense '=' and 'in' same
Both queries should give same result.
But one query giving different result and other query giving different result.
Is this 2 queries different??
SELECT sum(SALES_ITEM.NET_PRICE)
FROM PRODUCT_GROUP_LINK,
PRODUCT,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND (SALES_ITEM.PRICE_LIST='1FAC'
OR SALES_ITEM.PRICE_LIST='1FAW' )
AND CLASS_CODE='TYPE'
group by STYLE_CODE
SELECT sum(sales_item.net_price)
from PRODUCT,
product_group_link,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND sales_item.PRICE_LIST IN ('1FAC','1FAW')
AND CLASS_CODE='TYPE'
group by style_code
Any idea?
Thanks
View 5 Replies
View Related
May 22, 2001
Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server.
but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.
Ali
/*Truncate from all tables*/
select 'Truncate table ' + name from sysobjects where type ='u'
order by name
/*Count all table rows from all tables*/
select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u'
order by name
/*View all sp*/
select * from sysobjects where type ='p' and name
not like 'dt%'
order by name
/*View all triggers*/
select * from sysobjects where type ='tr'
order by name
/*View all Views*/
use master
select * from sysobjects where type ='v'
order by name
View 1 Replies
View Related
Jun 17, 2015
I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.
View 4 Replies
View Related
Jun 18, 2008
I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions
View 3 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 4 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 1 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 4 Replies
View Related
May 4, 2015
Required Output
Country Category
Internet Sales Amount
Internet Order Count
[code]....
I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.
View 2 Replies
View Related
Mar 5, 2012
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
View 6 Replies
View Related
Jan 9, 2014
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date
Can they be combined into one query to produce one result table??
View 3 Replies
View Related
May 29, 2008
Hi,
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
----------------------------------------------------------------------------------------------------------------------------------------
create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
View 8 Replies
View Related
Mar 7, 2006
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies
View Related
Feb 23, 2007
the following is the code,....When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...
Connection is open and the query is simple select statement
SqlCommand myCommand = new SqlCommand(query,myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
Thanks
Niranch
View 7 Replies
View Related
Sep 6, 2000
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
View 4 Replies
View Related
Oct 4, 1999
Dear all,
We are doing a query to SQL database through ODBC, to return a field named 'description'. The field has a maximum length of 1024 characters, ie, it is defined as varchar(1024). However, ODBC only return the first 255 characters. To rub more salt to the wound, when we are trying to insert a row of data into the field, ODBC actually truncated all the data after the first 255 characters, resulted in a little disaster.
I don't think we have a problem in the C programs, since the whole thing works in a little test database. (also in SQL7) I suspect there is a setting somewhere, only that the documentation doesn't give me much chance to find it. Any hints is desperately needed!!
Thanks in advance!
View 1 Replies
View Related
Oct 28, 2007
I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)
Application Server: JBoss v 4.0.4
Driver : sql 2005 driver.
Hi,
I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?
used by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63)
... 88 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 97 more
View 2 Replies
View Related
Feb 29, 2008
Hi, I'm trying to return 2 different result sets using the below query mapped to 2 different variables in my execute sql task. I've tried this with one task and 2 seperate tasks but can't get it to work. Is this possible using only one task? It keeps giving me a result set error.
SELECT COUNT(*) AS DeceasedCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 53
SELECT COUNT(*) AS LostCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 469
View 5 Replies
View Related
Nov 4, 2015
My desired output is:
abc - 2
def - 2
ghi - 2
jkl - 2
As you can see my query returns all the values from both tables instead of combining them. This is SQL Server 2008
Create Table #1 (blah varchar(100), cnt int)
Insert Into #1 Values
('abc', 1)
,('def', 1)
,('ghi', 1)
,('jkl', 1)
[Code] ....
View 3 Replies
View Related
Oct 25, 2005
hi all,
can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.
1.number of imported imported animal type:
select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p
where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'
2. number of imported animal type that are licensed:
select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id
3.percentage: 2/1*100
select percentage=
((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100
4.local animal type:total pet records - 1
select number=
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)
5.local animal type that are licenced:total pet records-2
select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)
6.percentage: 5/4*100
select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100
thx,
vani
View 1 Replies
View Related
Dec 8, 2011
I know how to write a stored procedure that does various things to a database. I know that a stored procedure returns a value of 0 by default if it executes successfully, and a non-zero value otherwise. I know you can use output variables to return other values from a stored procedure. I am moderately familiar with these things.
But... how do I fashion it if I'm calling the stored procedure from VB.NET in a web application, and I don't just want a couple of variable values, I want the whole result set?
I know there's #temporaryTables that ...exist within the scope of the stored procedures... ##Globaltemptables... regular_tables... and @tableVariables. I'm reading furiously to figure out what these things...all...do... and I'm leaning in the direction of ... a variable table as an output variable, but I just don't know... how and what I can stuff that into in the front end so I can shove it into the nice and neat grid view thing.
(fyi, I'm trying to return a consolidated table of available rooms fitting the user's specified reservation dates and amenity preferences - that part, I've gotten done like a boss. It's...getting it back to the front end I'm struggling with.)
View 3 Replies
View Related
Feb 28, 2008
I have a form that has many checkboxes (more than 40) that provide information about companies.
For example. Company ABC
Checkbox 1 (Windows XP)
Checkbox 2 (Windows Vista)
Checkbox 3 (Windows NT4)
etc.
I save these in a SQL table like the following
Row 1 - Col 1 (CompanyID), col2 (checkbox value)
Row 2 - Col 1 (CompanyID), col2 (checkbox value)
etc.. so a comapany can have multiple checkbox selected..
I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.
This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.
I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx)
INTERSECT
(SELECT CompanyID from xx WHERE CheckboxVal = xx)
etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.
Any suggestions on this please...this is my 4th day working on this..
View 8 Replies
View Related
Oct 31, 2007
I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah
View 1 Replies
View Related