|
I have a report that I created that uses parameters. One of the parameters queries data from a table and inserts it into a combobox. My question is: How do I insert a wildcard into a queried parameter? For example if the query returns a list of teams how can I add a wildcard to that dataset so I can also have the option to pull of all the teams? Any help would be appreciated.
-Garrett
|
|
|
|
|
O.K. So I decided to create a stored procedure that takes a sql statement as a parameter and then adds a wildcard to the first row. This seemed to work fine. Code is below:
<br />
CREATE PROCEDURE sp_add_wildcard<br />
@sql nvarchar(1000)<br />
AS<br />
<br />
create table #paras<br />
(<br />
label nvarchar(255),<br />
value nvarchar(255)<br />
)<br />
<br />
insert into #paras (label,value) values ('(ALL)','%')<br />
execute('insert into #paras (label,value) ' + @sql)<br />
<br />
select * from #paras<br />
<br />
drop table #paras<br />
GO<br />
|
|
|
|
|
Usually in my RS datasets, I do a union
SELECT 'All' as label, '%' as value
union
SELECT label, value from myLookupTable
|
|
|
|
|
Hi everyone,
I want to learning sql2000 but Im hearing that SqlServer2000 is differ with SqlServer2005 now i want to know its right or no and if its right this difference is in statements or environment.
Thanks
|
|
|
|
|
messages wrote: Im hearing that SqlServer2000 is differ with SqlServer2005
Yes there are differences between SQL Server 2000 and SQL Server 2005
messages wrote: i want to know its right or no
Yes, what you heard is correct.
messages wrote: if its right this difference is in statements or environment
Yes, to both.
|
|
|
|
|
Hi colin,
Thanks for your help so if i try to learning sql2000 and then i want to working with SqlServer2005 i need to learning again because i hear that it has one enviroment
like vs.net and it donest Query and Enterprise.
Thanks
|
|
|
|
|
Hi All,
I have a DataTable in cache object and a local Datatable populated by calling an SP. I need to compare the whole data in both DataTables.If Cache DataTable's data is old then refresh it with latest one by calling the SP again and then use it later on.
How can I achieve this ?
Pls guide me.
Thanks
Omkar
|
|
|
|
|
Um. The point of having the cache is to save the overhead of querying the database. If you have to query the database to find out if the cache is still valid, the cache becomes pointless - it's just wasting memory.
ASP.NET 2.0 offers the SqlCacheDependency class which, on SQL Server 2005, uses a feature where the server software itself can call you back to tell you that the results of a query have changed. SQL Server 2000 doesn't offer this, but ASP.NET supplies a tool (aspnet_regsql.exe) which can be used to create (I assume) triggers on tables which will allow a simple scalar lookup to determine if the base table has changed, rather than having to perform the potentially-costly query.
In general, though, to compare two DataTables, you would simply iterate over the Rows collection, and if there was any row that was different in one table compared to the other one, or a row present in one missing in the other, they'd be different.
|
|
|
|
|
Hi,
I have one java table
How to retrieve the rows from the javatable using vbscript
|
|
|
|
|
Probably this is not the correct forum for that question.
Best Regards,
Apurva Kaushal
|
|
|
|
|
I know this is a newbie question but I am a newbie to writing stored procedures, Ok I want to right a stored procedure that copys data from one table to another table based on a certain ID then output back to the program that executes the stored procedure the ID of the next record available in the table that was just copied into. So basically i have table x and y, y is a history table of x, whenever things are about to change in x i save certain records (based on an ID) into Y, after I save this specific data to y, I want to ouput Y next record ID that is avaible(ex. if I add rows with IDs 1-10, i want to output back either 11). So how can I do this? Any Help will be greatly appreciated.
|
|
|
|
|
Insert Into TableA ( ColumnList )
Select SimiliarColumnLIst
From TableB
Where Conditional
After your insert statement, add either of the following
SELECT @@IDENTITY
OR
SELECT SCOPE_IDENTITY()
@@IDENTITY will return the ID of the last record inserted. If you have triggers on your table(s), then it would be the ID of the last record inserted by a trigger, in which case SCOPE_IDENTITY() returns the ID of the newest record, even if a trigger were to perform inserts against other tables. I personally always use SCOPE_IDENTITY() since I know what it will return now and in teh future, regardless of changes to the DB.
- Sage
LinkRenter
|
|
|
|
|
How to send HTML Form as body using SQL Mail in SQL Server?
Arghya Mahapatra
|
|
|
|
|
Use xp_sendmail with variables
EXEC xp_sendmail @recipients = 'robertk;laurac',
@message = 'The master database is full.',
@copy_recipients = 'anned;michaels',
@subject = 'Master Database Status'
Thanks & Regards
Kumar Prabhakar
abc
|
|
|
|
|
Hi friends,
Can anyone please tell me the answer for this query.
How to get the name of a person who gets 2nd maximum salary in an Organization from Employee table?
Thanks in Advance.
Prya
|
|
|
|
|
This could be one way:
select min(Salary) from(SELECT distinct TOP n Salary FROM table_name ORDER BY Salary desc)
Replace n with 2.
Best Regards,
Apurva Kaushal
|
|
|
|
|
hi
I need to get the Empname of the person who gets second salary. Can you help for that.
Prya
|
|
|
|
|
put empname in the same query in place of salary.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Hi Friends,
My problem is that , my application is using common transaction object ,
when multiple users trying to access the application resources simultaneously
it will show me the error datareader is already opened . Can anybody suggest me , how to maintain these problem? My application is using ASP.NET as front-end. Could it be maintained by maintaining isolation/concurrency in database?
Please , reply your suggestions.
Thanking you in advance,
Param
param
|
|
|
|
|
Best practice is to use a separate connection for every logical database query (if you have a lot of queries in the same function, it's fine to use the same connection for all of them, but you shouldn't use the same connection object in different functions). Ensure that you Dispose the connections when you've finished with them.
ADO.NET has connection pooling support which is enabled by default. This will avoid the overhead of connecting unless there are no free connections in the pool.
Your error is occurring because another thread, processing another request, already has a DataReader open on the connection, and that's not allowed.
|
|
|
|
|
Hi Thanks for your reply.
But,
I have closed the connection n used seperate connection foe every logical database query..but in my code i have used common transaction object for every query..will it create any problem?
becoz every time wen multi user are accessing the system random error are comings.sometime it gives internal fatal error.some time it gives ur reader is opened with this connection.I am very sure tht i have close the connection n the data reader both.
I wud be very pleased if u can give some solution to this problem
Thanking you in advance
Param
param
|
|
|
|
|
While i am use bulk insert to import the csv file into my SQL SERVER through my system develop using ASP.NET by VB script, it able to import all csv data into my database.
Unfortunately, some data such as those decimal or money data type already change to vchar for all the data type after import in my database and cause them to be close by double quote ("1,111.00"). This unable let me to total up those data while i do it with my system.
As i research, there have CONVERT and CAST function to convert the data type, can i use them while i import the csv file into my database through the BULK INSERT? Thanks.
|
|
|
|
|
Bulk load to a staging table, then clean the data when transferring to your production tables. This will allow you to leave the BulkInsert task alone, and expand the tranfer task as changes to the underlying data or destination tables changes. This is why we call it ETL. Extract the data from the source, transform it match your systems and the Load it into your systems.
- Sage
|
|
|
|
|
Excuse me, can you mention more detail and how it to be done such as give some simple example?
Thank you for you response.
|
|
|
|
|
This was thrown together, but should work if you replace the tablenames, columnnames and filenames with values that apply to your application
BULK INSERT DBName.dbo.StagingTable -- StagingTable can be a copy of your destination table
From 'C:\YourFile.txt' -- actual path to your file
With (
FieldTerminator = ',',
RowTerminator = '\n' -- for new line
)
Alter Table StagingTable
ADD SafeToTransfer BIT NOT NULL Default(1)
-- Run some integrity checks prior to attempting to tranfer
if ( select Count(*) From DBName.dbo.StagingTable Where IsNumeric( ColumnA )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where IsNumeric( ColumnA )=0
END
if (select Count(*) From DBName.dbo.StagingTable Where isDate( ColumnB )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where isDate( ColumnB )=0
END
if ( select Count(*) From DBName.dbo.StagingTable Where isNumeric( ColumnD )=0 ) >0
BEGIN
Update DBName.dbo.StagingTable
Set SafeToTransfer = 0
Where isNumeric( ColumnD )=0
END
Insert Into DBName.dbo.DestinationTable
(
ColumnA, -- an INT Column
ColumnB, -- a datetime column
ColumnC, -- a varchar(100) column
ColumnD, -- a numeric(9,5) column
ColumnE -- another varchar(100) column
)
SELECT
CAST( ColumnA as int), -- an INT Column
CAST( ColumnB as datetime), -- a datetime column
LEFT( ColumnC, 100), -- a varchar(100) column
CONVERT( numeric(9,5), ColumnD), -- a numeric(9,5) column
LEFT( ColumnE, 100) -- another varchar(100) column
From DBName.dbo.StagingTable
Where SafeToTransfer = 1
Alter Table StagingTable
DROP COLUMN SafeToTransfer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
