How to makw Conditional Statements in SELECT

I'm trying to code a storedprocedure which looks for a product in a table. It takes one Paramater. Value may be 1 or 2 or 3.

CREATE PROCEDURE [dbo].[procSearchProduct]

@.PromoPrm int

AS

BEGIN

SELECT *

FROM tblProduct AS P

WHERE ?

END

END

--

if @.PromoPrm = 1 then where statement will be like this one: WHERE P.Promo = 'True'

else if @.PromoPrm = 2 then where statement will be like this one: WHERE P.Promo = 'False'

else if @.PromoPrm = 3 then where statement will be like this one: WHERE P.Promo = 'False' OR P.Promo = 'True'

Hope i am clear on my problem. Let me know if you don't understand it. Waiting for a solution.

Happy Coding...

One method is to us if/else:

IF @.PromoPrm = 1

select * from tblProduct p

where p.promo = 'True'

else if @.promo = 2

select * from tblProduct p

where p.promo = 'False'

else if @.promoPrm = 3

select * from tblProduct p

where p.promo = 'False'

or p.promo = 'True'

If your PROMO column can only have the values of TRUE or FALSE you can leave out the WHERE condition on the IF @.promoPrm = 3 portion.

The next comment is that you should avoid using the SELECT * syntax in stored procedures. Give a look to this post concerning this issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1108698&SiteID=1

Use of the 'SELECT *' syntax in stored procedures can leave behind "land mines" that go off at a later date and time when changes are made to the columns of the source table.

Another approach to the problem is something like:

select * from tblProduct p

where @.promoPrm = 1

and p.promo = 'TRUE'

or @.promoPrm = 2

and p.promo = 'FALSE'

or @.promoPrm = 3

and ( p.promo = 'TRUE' OR

p.promo = 'FALSE'

)

|||

Here's a version using dynamic SQL.

declare @.sql varchar(max);

declare @.where varchar(max);

set @.where = case @.promoprm

when 1 Then'WHERE Promo = ''True'''

when 2 Then 'WHERE Promo = ''False'''

when 3 Then 'WHERE Promo = ''True'' OR Promo = ''False'''

end;

set @.sql = 'SELECT * FROM tblProducts ' + @.where;

exec(@.sql);

Jamie

|||

Yeap it is a solution but i gave little part of the code. Trying to make a seach form in asp.net . And u know this kind of page sends many parameters to sql. I can't make all combination in sql. I mean what about if this stored procedure takes 5 Parameters like @.PromoPrm . Point this problem is i have to write a code which makes decision on WHERE part. May be like this one :

SELECT *

FROM tblProduct AS P

WHERE case when P.Promo = 1 then P.Promo = 'True' else P.Promo = 'False'

This query is wrong but i hope u got the point?

|||

Beware using any dynamic SQL, especially in a web environment. You don't want to leave yourself open to code injection.

Jamie

|||

To simplyfy this,

Code Snippet

select * from tblProduct p

where @.promoPrm & 1 = 1 and p.promo = 'TRUE'

or @.promoPrm & 2 = 2 and p.promo = 'FALSE'

|||

Give a look to the "dynamic search" article by MVP Erland Sommarskog:

http://www.sommarskog.se/dyn-search.html

Also, it will help if you finish describing your problem rather giving information in portions.

|||

JHunter i think your solution is good but sql injection is important in this stiuation. If i use your code sample than i need to check the paramateres in my code but it is very very difficult because it takes it from query string. Is there any solution to this problem?

Kent thanks for post. Also thanks for link that u gave.

|||

ManiD wrote:

To simplyfy this,

Code Snippet

select * from tblProduct p

where @.promoPrm & 1 = 1 and p.promo = 'TRUE'

or @.promoPrm & 2 = 2 and p.promo = 'FALSE'

ManiD i think your solution is the best. What about the performance. Because i will use this on 5 different paramaters.

|||

If you are only passing an integer to the stored proc, leaving the proc to decide how the where clause is constructed, you'll be okay. Problems come when you start passing strings that contain SQL as parameters .

The simplest check you can do is make sure the parameter contains only "expected" values.

Erlands (as previously posted) page provides some of the best content relating to dynamic SQL.

Jamie

|||How many possible values are there for the PROMO column? If it is but a handful this will probably scan and not seek because of the cardinalities.|||

Ok here is the problem again: I'm trying to make a search form. Form runs a stored procedure call it procSearchProduct. This stored procedure takes 6 parameters.

@.CityPrm varchar(50), --takes the name of city

@.HasPromotionPrm int, -- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False

@.HasDescriptionPrm, int,-- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False

@.HasImagePrm int,-- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False

@.StateofShopPrm varchar(50), -- takes the name of the state

@.ShopClosedOpenedPrm int -- takes onl 3 value. 1,2 or 3. 1=True , 2=Fale , 3=True OR False

what i am trying to do is select values from table. You know the rest. Sory i couldn't paste the whole code because i wrting it on dofferent language so it will not help.

|||

I'd avoid passing CityPrm and StateofShopPrm as varchars. Instead, create lookup tables for the city and state with a numeric primary key.

Populate any dropdown menus from these lookups, but capture the primary key (ID) of the selected value - not the name. This numeric ID can be passed to the stored procedure.

If you've not normalised your main table, you can use the primary key to lookup the textual value for the lookup.

Jamie

|||Also, on these VARCHAR fields, are you doing = searches or LIKE searches?|||Another point , user selects the option "WHOLE WORD" or "SOME OF THEM" in drop down for search condition on City but it will mess the problem much more. Assume that i make = search. And I cant take the State in to a dropdownlist. May be a solution but not for me.

How to make this simple SELECT query perform faster

Hi,

I have the following simple SELECT query:

SELECT * FROM TRX_LAPD

But the problem that table TRX_LAPD is very big. Althoug, I am using this query in a Network envirenment.

If I implement this query inside Microsoft SQL Server, the first time is somehow slow (40 sec), but becasue of the caching cabability the next time is very fast.

The thing that I am using this query in Visual Web Developer, so to be accessed by other users in the local network, but it seems that there is no caching features; each time I execute the query the implementation remains slow (40 sec) even from the from the server PC which have the SQL Server running.

I used also a simple Stored Procedure, but nothing changed

Below my code in Visual Web Developer with VB script:

Sub getmytable()
Dim sql As String
sql = "SELECT * FROM TRX_LAPD"
'or sql = "EXEC getTRX_LAPD" if I will use the Stored Procedure

MySqlDataSource.SelectCommand = sql
'where MySQLDataSource is an SqlDataSource control
End Sub

and the Stored Procedure that I tried also:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[getTRX_LAPD]
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM TRX_LAPD
END

The SqlDatasource is bounded to a GridView control
I really appreciate any help.
Thanks

There are two basic rules of querying a database table:

1. Query only the rows that you need.
2. Query only the columns that you need.

By doing "SELECT * FROM TRX_LAPD", you are retreiving all the rows and all the columns all the time. Is that really a necessity? If you are doing this on a "very big" table (as you as say) it is bound to take time. I suggest you should revisit the design of querying the entire table every time.

How to make this join select statement works

Here is a select statement of table A:

select user01_id, user02_id from A where ...

I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?

select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)

table A

user01_id int,
user02_id int,
...
primary (user01_id, user02_id)

table user

userid int primary key,
name varchar(80),
...

Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/

Hi, Rudy,

The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.

Thanks very much for your help.

Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy

In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.

A good usage of "defensive". That is the difference between a master and a regular craftsman.

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server

Please help

thanks

You have to add you second server to linked servers (server objects-> linked servers-> right click new linked server) and next in your query use server name in table name like:

select * from [server].[database].dbo.[tablename]

or use open query

Select * from OPENQUERY([servername],'SELECT * from [database].dbo.[tablename]')

second is faster but use it only if your query will hit only data on second server.

Thanks

sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help

thanks

there are many ways to access other server.database.table

(a) Configure LinkedServer

b)OPENDATASOURCE

(c) OpenQuery

read about this in BOL

Madhu

How to make query result fast

I have around 5 items in my database. It takes me about 1-
2 minutes to show up all the result. How can I make my
query result faster? I try to select everything on my
product table.
Hi,
Did you mean 5 tables and on running a query against this 5 tables takes 2
minutes.
The speed depends up on the availability of data in each of your tables, If
you have more records in table then create indexes based on your queries
Where condition.
This will defenetely speed up the retrieval time.
Note:
Use the Query -- Execution plan option inside Query analyzer to tune the
query.
Thanks
Hari
MCDBA
"ping" <anonymous@.discussions.microsoft.com> wrote in message
news:667501c42e80$9e398d80$a001280a@.phx.gbl...
> I have around 5 items in my database. It takes me about 1-
> 2 minutes to show up all the result. How can I make my
> query result faster? I try to select everything on my
> product table.
|||I would say that this is a little open ended question. Speed of the query
is not related to just one aspect.
Lets take scenarios here. First, lets assume that you have one table with
only 5 rows in it. But each row has 150 columns (say for the sake of
discussion). So if you run a query which is like SELECT * FROM TABLEA, this
is gonna take a lot of time, as this will have to result all the columns
from the table. Therefore, a better way would be to select only those
columns that you actually need in your result set. Say if you need only
5-10 columns, then there is no need to return 150 columns.
Second, lets assume that you 2000 rows but each row has only 3 columns.
Even if you return all the columns, your query will still be fast. So you
dont have to worry.
Another way to fasten your results is to have indexes created on each
table. This will also fasten up your results. You can even analyse your
query performance by making use of the Query Execution plan.
So at the end of the day, it depends. Your query is slightly open ended so
I would recommend that you re-visit it and evaluate it further to see its
performance cost.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

how to make password field case sensitive in sql server 2005

Hi,

SELECT UserID, UserName, Password, PublisherID, Currency
FROM [User]
WHERE (Password = 'Anitha') I am using the above mentioned it is working but int the password field i had given it as anitha. Now the querry is retriving the record for anitha, it shouldnot happen. The querry should retrive the record of anitha only for where condition anitha and not for Anitha or ANITHA etc..

Thanks

Vishwanath

Convert the password field and password value to varbinary and compare if they are equal.

Here is the code:

select

*from userswhere UserName='Nilesh'Andcast(passwordasvarbinary(20))=cast(N'password'asvarbinary(20))

The usename is not case sensitive but password is.

|||

hi Vishwanath,

its good practise to store passwords in encoded/encrypted format. what i do is use a custom encryption function which uses our own logic to encrypt each character and store it.

to add what Nilesh has mentioned read this linkhttp://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Hope it helps.

regards,

Satish.

|||

Hi satish,

now its working fine, but when i choose the collation property from that password field and changed it to bin format then at that it gave some warning message saying that some of the data will be lost, but when i choose yes i couldnt experience any such loss of data. Everything is working fine..

Thnaks

Vishwanath

|||

ohk sounds cool.

please mark the post which helped you as answered, it will give credit to answerer and post will be resolved.

thanks,

satish.

How to make OrderBy by Parameters

Hello All,
I need to make a select in a table, but i need to do a order by by
parameter.
Exemple:
SELECT * FROM CUSTOMERS
ORDER BY @.COLUMN
This select is a stored procedure.
@.Column is the name of the column in my table.
@.Column i need to send by parameter.
I need to make a dynamic sort, but i don't can to use EXEC(''), because of
SqlInject.
Somebody to know how to make this task?
Thanks.
Dexter"Dexter" <projenet@.yahoo.com.br> schrieb im Newsbeitrag
news:uPgavPERFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>|||DOnt think there is prober way to do this without dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dexter" <projenet@.yahoo.com.br> schrieb im Newsbeitrag
news:uPgavPERFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>|||http://www.4guysfromrolla.com/webtech/010704-1.shtml
"Dexter" <projenet@.yahoo.com.br> wrote in message
news:uPgavPERFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Dexter" wrote:

> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>
>sql

How to make full-text search accent-insensitive?

My SQL Server 2000 does not use the accent insensitive collation setting
(collation containing _AI) in full-text serches:
While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Muse',
SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.

Setting 'default full-text language' to neutral (0) does not help.

How can I make full-text searches accent insensitive?

Thanks for any ideas!
Matthias"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:4039d459$1@.epflnews.epfl.ch...
> My SQL Server 2000 does not use the accent insensitive collation setting
> (collation containing _AI) in full-text serches:
> While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Muse',
> SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.
> Setting 'default full-text language' to neutral (0) does not help.
> How can I make full-text searches accent insensitive?
> Thanks for any ideas!
> Matthias

You'll probably get a better reply if you post this in
microsoft.public.sqlserver.fulltext, since it's a relatively specialized
area.

Simon|||The "solution" for those who are interested:

There is NO solution! This is a known bug, full-text search is ALWAYS
accent-sensitive. All you can do is wait for a future update that may
correct this.

Matthias

"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:4039d459$1@.epflnews.epfl.ch...
> My SQL Server 2000 does not use the accent insensitive collation setting
> (collation containing _AI) in full-text serches:
> While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Muse',
> SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.
> Setting 'default full-text language' to neutral (0) does not help.
> How can I make full-text searches accent insensitive?
> Thanks for any ideas!
> Matthias|||Making accent insensitive searches with Full Text Search
Installing an accent insensitive version of Microsoft Search
Service

It is really a shame that Microsoft did not provide a solution for doing case
insensitive Full Text Search (FTS) before 2005. It is reaslly a lack of
consideration for all of their customers speaking or using language
that has accents. This is why I decided to post these instructions,
because there is a way around it. It is a post by Alex Hubner that
pointed me in the right direction. His solution works, I tried it.
He did not give the details on how to do it, so I decided I would:

* Get SharePoint Portal Server 2001 Service Pack 3 (SP3): KB837017 from
http://www.microsoft.com/downloads/...=15677a92-3470-
465f-9f63-e621094103e0&DisplayLang=en. There are five files to download:

oFile Name: SPSFull1.exe
File Size: 27937 KB
oFile Name: SPSFull2.exe
File Size: 25464 KB
oFile Name: SPSFull3.exe
File Size: 25975 KB
oFile Name: SPSFull4.exe
File Size: 27206 KB
oFile Name: SPSFull5.exe
File Size: 25198 KB

You can download and unpack the five downloaded files, but in fact, you only
need the content of SPSFull3.exe. A directory called SharePointPortalInstall will
be created.

Note: Do not use SharePoint Portal Server 2003, since its directory structure is
different, and the Microsoft Search Services don't seem to be a separate and
independent module in this version.

* Only the MS-Search part of the package is needed. You can find it in
SharePointPortalInstall\Server\Search. The installation program is called
SearchStp.exe. This is the installation program we will use to re-install Microsoft
Search Service and make searches accent insensitive. This will allow, for
example, the get the same search results not matter if the user enters a keyword
with or without accents (Eg.: 'Montreal' or 'Montral');
* You can use the documentation found on
http://support.microsoft.com/?kbid=827449 to re-install the Microsoft Search
Services. However, it is not necessary to go through all that trouble. I did so the
first time. However, some registry keys used by SQL Server and MS Search
Services were missing after. Gladfully, I had made a backup of all the registry
keys that we are asked to delete in this documentation. Also, the MS Search
Services have been installed into a different directory, so I had to do a search
through the registry to replace the old path by the new one where it had not been
updated by the installation program. In fact, you can skip this paragraph. I'm just
adding this information in case you would need it;
* Here are the registry keys I backup prior to this installation, just in case:

.HKEY_LOCAL_MACHINE\Software\Microsoft\Search
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servi ces\MSSCNTRS
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servi ces\MSSEARCH
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servi ces\MSSGATHERER
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servi ces\MSSGTHRSVC
.HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servi ces\MSSINDEX

* The second time I installed the Accent Insensitive MS Search Services, I only
used section 2 of the documentation found on
http://support.microsoft.com/?kbid=827449 called Install the Microsoft Search
Service. However, I used the SearchStp.exe file downloaded earlier instead of
using the one they specify, and I stopped the Microsoft Search Service before
doing the installation even if it is not specified in the documentation. As specified
in this document, "To view the original domain name and user account, you can
use the most recent SQL Server setup log file (SqlstpN.log). In the SqlstpN.log
file, locate the line where the SQL Server Setup program ran the Ftsetup.exe
program. Additionally, make sure that the information is the same as the
information that is included in the command." On my computer, this file was
called sqlstp.log and it was located in the Windows directory.
* You can probably also follow the steps in section 3, but I haven't done so.

Once this is all done, you can use Enterprise Manager to create indexes on your database.
There is one thing though to know. When using Enterprise Manager to manage a remote
server, Tools->Full Text Indexing remains greyed out. I had to start Enterprise Manager
locally on the server in order to gain access to the Full Text Indexing tool.

Creating indexes
In order to create Full Text Search indexes on a table, the table needs to have a primary
key. So make sure all the tables you want to index have primary keys.

Here are the steps to create indexes. The first time you create an index, you will need to
create a catalog:

* Start Enterprise Manager and open the your database Table View;
* Select the table you want to index using FTS (Full Text Search) and right-click on
it.
* In the menu that just opened with the right-click, select Full-Text Index Table,
and then in the sub-menu select Define Full-Text Indexing on a Table. This will
start the Welcome to the SQL Server Full-Text Indexing Wizard.

* Click on Next;
* On the Next Screen, select the key to index, and click on Net;
* Select the field(s) to index and choose the proper language settings in the second
column. Click on Next;
* You will then have to create a Catalog (An FTS Catalog). Give it a name and
enter a location where it should be stored. You can use the default path. Click on
Next;
* You will then have to create a schedule for the indexation process. Click on New
Catalog Schedule, and define a Schedule. Let's make it a Full Population. Maybe
an Incremental Population would work, but I am not sure in that case what
happens if information is removed from the database being indexed. Once the
schedule is defined, click on Ok and then on Next;
* Click on Finish, and the catalogue will be created:

We are now ready to create an index on another table. This time, it is not necessary to
create a new catalogue. Simply reuse to one we previously created. Same thing for the
schedule; the one already created can be reused.

We now have a catalogue, but the indexes are still empty, since they are scheduled to be
generated in the future (based on the schedule you created). We should now do a full
population of the indexes. In SQL Server Enterprise Manager, right click on each table
you wish to index, select Full-Text Index Table, and then select Start Full Population.

You can now use a query similar to the one bellow in SQL Query Analyzer to test
whether the case insensitive indexing is working properly:

SELECT *
FROM table_name
WHERE CONTAINS(field_name, 'raphal')

otable_name should be replaced by the name of the table you want to search;
ofield_name should be replaced by the name of the field you want to search;

Use this query with the same word with and without accents, and you should get the same
result both times.

Enjoy!

Jean-Franois Beauchamp
IT Consultant
jackojf-fts at yahoo.com

Quote:

Originally Posted by Matthias HALDIMANN

The "solution" for those who are interested:

There is NO solution! This is a known bug, full-text search is ALWAYS
accent-sensitive. All you can do is wait for a future update that may
correct this.

Matthias

"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:4039d459$1@.epflnews.epfl.ch...
> My SQL Server 2000 does not use the accent insensitive collation setting
> (collation containing _AI) in full-text serches:
> While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Muse',
> SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.
> Setting 'default full-text language' to neutral (0) does not help.
> How can I make full-text searches accent insensitive?
> Thanks for any ideas!
> Matthias
>

How to make an insert table lock

Hi There

i Have some t-sql that basically does something like :

INSERT INTO TABLEA SELECT * FROM TABLEB

There are alot of rows involved and sp_lock tell me that a table lock is being put on TABLEA during this insert.

This is a big problem for us. I have often used locking hints on selects etc but never an insert, how can i get the insert to never use a table lock ?

Thanx

You always have a lock on insert on something and have no control over the escalation of locks. Hints are only a SUGGESTION of how the server should handle the query. The server determines the locks used based on the statement.

In your case, it is locking the table, because it has no idea how many inserts will be done or where they will be placed in the physical table.

You can set your read level to "READ UNCOMMITTED" and that will allow people to read the table during the insert process.
|||

Hi Tom

All good suggestions we have thought of but not possible.

Here is the situation, we are converting in new branch data into a central database, so we know that all rows inserted will be new data and at the end of the clustered index.

The other process doing the read is ETL which will never be interested in this new data, however the way it works combined with slowly changing dimensions we have to set it to read committed or the ETL will not work properly. The inserts are essentially locking an entire table and will never touch any of the rows that the ETL is trying to read (there are no updates) , however obviously the table lock pretty much stops all ETL.

So basically we are not sure what to do?

ALl i can do so far is commit as often as possible , but this only slightly helps the situation, bottom line is i wanted to know if there was anyway to control locking with an insert but you have answered that.

Thanx

|||With the more information you provided, I would change your INSERT into a BCP or BULK INSERT.

The INSERT...SELECT * is pretty slow. If you export the data and use BCP or BULK INSERT, you will be 100 times (or more) faster and will lock the target table for a much shorter time. Also set the batch size to a number like 10,000 or 50,000 which will insert that many records as 1 transaction and drop the lock. That might let the ETL lock the table, and delay the import, but the ETL will finish.

This might fix your problem.

|||

Hi Tom

Thanx i will try that.

However the process must be automated, so i guess i will have to write say an ssis package that first exports the data to file and then uses bcp to import it back into the other tables?

Thanx

|||

Hi, Tom.

Is it possible to perform a BULK INSERT from one table to another in the same SS05 database, where both tables have the same structure, and one has been TRUNCATED to accept the replacement data from the other?

I have been studying the T-SQL documentation and I keep noticing "glimmers of hope" that make it appear as if it is possible (without having to export the data and then import it), but I am unable to discern the syntax for the INSERT statement.

Does this look anywhere close to being correct?

INSERT INTO table_a (IGNORE_CONSTRAINTS)

SELECT *

FROM OPENROWSET(BULK

(And that's where I get stuck, since BULK seems to want a reference to an external data file, NOT a table within the same database on the same server, with only a different table name.)

Dan

P.S. Maybe it must be done outside the T-SQL environment, such as shown in VB and C# examples at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx ? I was hoping there was a T-SQL solution.

(I just noticed an interesting comment in the C# example:

// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.

So what is the "real world" solution for bulk copying data from one table to another in the same database?)

|||

Tom

Any ideas ?

To uswe bulk insert i would first require a process to export to file and then back in again the time again on bulk insert would perhaps be lost on exporting to data to file and re-importing with bulk insert using SSIS or something ?

Is there a way to bulk insert data from 1 table to another using just tsql , and no exported data to file ?

Thanx

|||It is ALWAYS faster to export the file and bulk insert again, even with the added overhead of exporting it to file. The other methods require tons of writes to tempdb and log files, etc.

You should really try it as bulk insert. You wil be much happier.

The "real world" solution, as you would see from the MS copy data options in DTS and SSIS, are exporting the data and using BCP/BULK INSERT to reinsert them, because it is much faster.

|||

Tom,

Thanks.

Dan

|||

Hi Tom

Thanx, i was hoping to be able to do it in tsql and avoid ssis.

By the way is there now ay to bulk insert between tables ? Must bulk insert have a flat file source, seems that way from BOL.

Cheers

|||Look at the "bcp out" command. You can do that from tsql using EXEC.

how to make a textbox invisible

hi
how can i make a textbox invisible ?
here is the scenario.
i have a textbox to remind user to select some parameters to view the graph.
once the user selects the paramteters - the graph shows up.
once the graph shows up -the textbox should disappear.
how can i do thatright click the text box, select Properties
click Advanced >>
in the Advanced Textbox Properties
click the Visibility tab
in the Initial visibility radio button group check the Visible radio button
and below check the Visibility can be toggled by another report item
in the Report item drop down list pick your chart
i haven't tried this, it's off the top of my head, but i hope it's a good
first step,
regards,
kowlasky
"RP" wrote:
> hi
> how can i make a textbox invisible ?
> here is the scenario.
> i have a textbox to remind user to select some parameters to view the graph.
> once the user selects the paramteters - the graph shows up.
> once the graph shows up -the textbox should disappear.
> how can i do that|||But we can only see textboxes in the dropdown of "visibility can be toggled
by another report item"
:-( any other suggestions ''''
Thanks
"kowalsky" wrote:
> right click the text box, select Properties
> click Advanced >>
> in the Advanced Textbox Properties
> click the Visibility tab
> in the Initial visibility radio button group check the Visible radio button
> and below check the Visibility can be toggled by another report item
> in the Report item drop down list pick your chart
> i haven't tried this, it's off the top of my head, but i hope it's a good
> first step,
> regards,
> kowlasky
>
> "RP" wrote:
> > hi
> > how can i make a textbox invisible ?
> > here is the scenario.
> > i have a textbox to remind user to select some parameters to view the graph.
> > once the user selects the paramteters - the graph shows up.
> > once the graph shows up -the textbox should disappear.
> > how can i do thatsql

How to make a SELECT with a field name inside a variable?

I have a table (for example PERSONS) with several fields (NIF NAME AGE).
I have a cursors than read the differents fiels tat have this table inside a trigger fron another table that contain all the fields of the tables.
This cursor save the name of the fiels inside the variable @.FIELD.
I need to read the value of the several records of the table like:

@.FIELD='NIF' <--(CURSOR)

SELECT @.FIELS FEOM INSERTED --> select nif from inserted(persons)

This sentence doesn't works. How can I do?

Thanks, Otto Martinez.Hi,
you maye use a variable to generate the whole statement then use the exec command
Declare @.sql NVarchar(200)
set @.sql= "Select " + @.Fiels +" from inserted"
exec @.sql

How to make a select to another database using sp_addlinkedserver?

Hi all,
I trying to use sp_addlinkedserver, after I added successfully a new linked
server I do not know how to use the server...
I did this to add the server:
exec sp_addlinkedserver
'Server2','sqloledb','Northwind','myComputer','',' ','Region'
Now, I want to do a simple SELECT statement to the Region table of
Northwind, how do I do this?
Obviously, I am in another database...like for example "pubs"
Thanks for help,
Marcelo Moreira
select * from linkedservername.database.owner.table
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marcelo Moreira" <marcelo.moreira@.vpconsulting.pt> wrote in message
news:e6LaETghFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I trying to use sp_addlinkedserver, after I added successfully a new
> linked server I do not know how to use the server...
> I did this to add the server:
> exec sp_addlinkedserver
> 'Server2','sqloledb','Northwind','myComputer','',' ','Region'
> Now, I want to do a simple SELECT statement to the Region table of
> Northwind, how do I do this?
> Obviously, I am in another database...like for example "pubs"
> Thanks for help,
> Marcelo Moreira
>
|||Hi,
In Link server you could also use OPEN QUERY to query the databases
Open Query
Select * from openquery('<Linked Server>','select * from
DBNAME.Table_owner.Table_Name')
Thanks
Hari
SQL Server MVP
"Marcelo Moreira" <marcelo.moreira@.vpconsulting.pt> wrote in message
news:e6LaETghFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I trying to use sp_addlinkedserver, after I added successfully a new
> linked server I do not know how to use the server...
> I did this to add the server:
> exec sp_addlinkedserver
> 'Server2','sqloledb','Northwind','myComputer','',' ','Region'
> Now, I want to do a simple SELECT statement to the Region table of
> Northwind, how do I do this?
> Obviously, I am in another database...like for example "pubs"
> Thanks for help,
> Marcelo Moreira
>

How to make a select to another database using sp_addlinkedserver?

Hi all,
I trying to use sp_addlinkedserver, after I added successfully a new linked
server I do not know how to use the server...
I did this to add the server:
exec sp_addlinkedserver
'Server2','sqloledb','Northwind','myComp
uter','','','Region'
Now, I want to do a simple SELECT statement to the Region table of
Northwind, how do I do this?
Obviously, I am in another database...like for example "pubs"
Thanks for help,
Marcelo Moreiraselect * from linkedservername.database.owner.table
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marcelo Moreira" <marcelo.moreira@.vpconsulting.pt> wrote in message
news:e6LaETghFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I trying to use sp_addlinkedserver, after I added successfully a new
> linked server I do not know how to use the server...
> I did this to add the server:
> exec sp_addlinkedserver
> 'Server2','sqloledb','Northwind','myComp
uter','','','Region'
> Now, I want to do a simple SELECT statement to the Region table of
> Northwind, how do I do this?
> Obviously, I am in another database...like for example "pubs"
> Thanks for help,
> Marcelo Moreira
>|||Hi,
In Link server you could also use OPEN QUERY to query the databases
Open Query
Select * from openquery('<Linked Server>','select * from
DBNAME.Table_owner.Table_Name')
Thanks
Hari
SQL Server MVP
"Marcelo Moreira" <marcelo.moreira@.vpconsulting.pt> wrote in message
news:e6LaETghFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I trying to use sp_addlinkedserver, after I added successfully a new
> linked server I do not know how to use the server...
> I did this to add the server:
> exec sp_addlinkedserver
> 'Server2','sqloledb','Northwind','myComp
uter','','','Region'
> Now, I want to do a simple SELECT statement to the Region table of
> Northwind, how do I do this?
> Obviously, I am in another database...like for example "pubs"
> Thanks for help,
> Marcelo Moreira
>sql

How to make a safe varchar() to int conversion

I'm selecting a big group of records for output, and I need to convert a
couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
myintfield from ...)
Problem is, some erroneous data has non-numeric characters in it, and SQL
Server kills the whole SELECT, outputting no rows (!!!).
Is there any way to get SQL to just put NULL or 0 in for erroneous data -
the way you can use SET ARITHABORT to have it ignore numeric errors and keep
processing?
Thanks!
- NevynHello Nevyn,
One of the best way's I've found to do this is to use a LIKE clause in your
select statement
SELECT * FROM myTable WHERE numCol NOT LIKE '%[a-z]%'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm selecting a big group of records for output, and I need to convert
> a couple columns from varchar to int. (SELECT CAST(mycharfield as int)
> as myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and
> SQL Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous
> data - the way you can use SET ARITHABORT to have it ignore numeric
> errors and keep processing?
> Thanks!
> - Nevyn
>|||You can just add
"where isnumeric(col)=1"
or
"where col like '%[^0-9]%'"
to your query
-oj
"Nevyn Twyll" <astian@.hotmail.com> wrote in message
news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...
> I'm selecting a big group of records for output, and I need to convert a
> couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
> myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and SQL
> Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous data -
> the way you can use SET ARITHABORT to have it ignore numeric errors and
> keep processing?
> Thanks!
> - Nevyn
>|||That would be "where col NOT like '%[^0-9]%'"
Gert-Jan
oj wrote:
> You can just add
> "where isnumeric(col)=1"
> or
> "where col like '%[^0-9]%'"
> to your query
> --
> -oj
> "Nevyn Twyll" <astian@.hotmail.com> wrote in message
> news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...|||Tks for the correction.
-oj
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42015A90.8863CB3B@.toomuchspamalready.nl...
> That would be "where col NOT like '%[^0-9]%'"
> Gert-Jan
>
> oj wrote:

How to make a random order query ?

If we use: select * from .... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?Hi,

FYI:http://www.datawebcontrols.com/faqs/Data/ReturningDataInRandomOrder.shtml

Regards,

How to make a correct select

Could anyone help med with a select statement with a join between to
tables. It is to be used in a OLAP cube.
I Havde table LedgerBudget and Table Admin. In table admin I can setup
a from and to date and also a budgetmodel.
The admin have ONE record per OLAP cube.

The statement below works fine if I have stated a budgetmodel in table
Admin.
But if no budetmodel stated in table Admin, I want the statement to
select every ledgerbudget with active = 1 and allocatemethod = 0
Could anyone help me with this.

SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,
dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE, - dbo.LEDGERBUDGET.AMOUNT AS
BUDGET
FROM dbo.LEDGERBUDGET INNER JOIN
dbo.ADMIN ON dbo.LEDGERBUDGET.STARTDATE >=
dbo.ADMIN.FROMDATE AND
dbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATE
AND
dbo.LEDGERBUDGET.MODELNUM =
dbo.ADMIN.BUDGETMODELID
WHERE (dbo.LEDGERBUDGET.ACTIVE = 1) AND
(dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0)

BR/ThanksOn 24 May 2006 06:23:58 -0700, jazpar wrote:

(snip)
>The statement below works fine if I have stated a budgetmodel in table
>Admin.
>But if no budetmodel stated in table Admin, I want the statement to
>select every ledgerbudget with active = 1 and allocatemethod = 0
>Could anyone help me with this.
(snip)

Hi jazpar,

Try changing the join from INNER JOIN to LEFT OUTER JOIN.

If that doesn't do what you need, post table structure (as CREATE TABLE
statements, including constraints, properties and indexes), sample data
(as INSERT statements) and expected results. See www.aspfaq.com/5006

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo, and thanks

Here is table ADMIN (actual name GURU_ADMIN)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GURU_ADMIN]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[GURU_ADMIN]
GO

CREATE TABLE [dbo].[GURU_ADMIN] (
[OLAPFROMDATE] [datetime] NOT NULL ,
[OLAPCUBENAME] [varchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL
,
[OLAPTODATE] [datetime] NOT NULL ,
[BUDGETMODELID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL
,
[GURUDESCRIPTION] [varchar] (250) COLLATE Danish_Norwegian_CI_AS NOT
NULL ,
[DATAAREAID] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[RECID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GURU_ADMIN] ADD
CONSTRAINT [DF__GURU_ADMI__OLAPF__5C482906] DEFAULT ('1900-01-01
00:00:00.000') FOR [OLAPFROMDATE],
CONSTRAINT [DF__GURU_ADMI__OLAPC__5D3C4D3F] DEFAULT ('') FOR
[OLAPCUBENAME],
CONSTRAINT [DF__GURU_ADMI__OLAPT__5E307178] DEFAULT ('1900-01-01
00:00:00.000') FOR [OLAPTODATE],
CONSTRAINT [DF__GURU_ADMI__BUDGE__5F2495B1] DEFAULT ('') FOR
[BUDGETMODELID],
CONSTRAINT [DF__GURU_ADMI__GURUD__6018B9EA] DEFAULT ('') FOR
[GURUDESCRIPTION],
CONSTRAINT [DF__GURU_ADMI__DATAA__610CDE23] DEFAULT ('dat') FOR
[DATAAREAID],
CHECK ([RECID] <> 0)
GO

CREATE UNIQUE INDEX [I_50001RECID] ON
[dbo].[GURU_ADMIN]([DATAAREAID], [RECID]) ON [PRIMARY]
GO

Table Ledgerbudget
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[LEDGERBUDGET]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[LEDGERBUDGET]
GO

CREATE TABLE [dbo].[LEDGERBUDGET] (
[ACCOUNTNUM] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[STARTDATE] [datetime] NOT NULL ,
[ENDDATE] [datetime] NOT NULL ,
[FREQCODE] [int] NOT NULL ,
[ACTIVE] [int] NOT NULL ,
[AMOUNT] [numeric](28, 12) NOT NULL ,
[COMMENT_] [varchar] (30) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION2_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DIMENSION3_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[AUTOTRANS] [int] NOT NULL ,
[CURRENCY] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[QTY] [numeric](28, 12) NOT NULL ,
[PRICE] [numeric](28, 12) NOT NULL ,
[STOP] [int] NOT NULL ,
[KEY_] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[EXPANDID] [int] NOT NULL ,
[REPORT] [int] NOT NULL ,
[COV] [int] NOT NULL ,
[COVSTATUS] [int] NOT NULL ,
[CREDITING] [int] NOT NULL ,
[FREQ] [int] NOT NULL ,
[TAXGROUP] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[MODELNUM] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[INVENTRECID] [int] NOT NULL ,
[INVENTTABLEID] [int] NOT NULL ,
[ALLOCATEMETHOD] [int] NOT NULL ,
[FORECASTMODELID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT
NULL ,
[ASSETID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[ASSETTRANSTYPE] [int] NOT NULL ,
[ASSETBOOKID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[MODIFIEDBY] [varchar] (5) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[DATAAREAID] [varchar] (3) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[RECID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [I_196MODELIDX] ON
[dbo].[LEDGERBUDGET]([DATAAREAID], [MODELNUM], [ACCOUNTNUM],
[STARTDATE]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LEDGERBUDGET] ADD
CONSTRAINT [DF__LEDGERBUD__ACCOU__2B9540A9] DEFAULT ('') FOR
[ACCOUNTNUM],
CONSTRAINT [DF__LEDGERBUD__START__2C8964E2] DEFAULT ('1900-01-01
00:00:00.000') FOR [STARTDATE],
CONSTRAINT [DF__LEDGERBUD__ENDDA__2D7D891B] DEFAULT ('1900-01-01
00:00:00.000') FOR [ENDDATE],
CONSTRAINT [DF__LEDGERBUD__FREQC__2E71AD54] DEFAULT (0) FOR
[FREQCODE],
CONSTRAINT [DF__LEDGERBUD__ACTIV__2F65D18D] DEFAULT (0) FOR [ACTIVE],
CONSTRAINT [DF__LEDGERBUD__AMOUN__3059F5C6] DEFAULT (0) FOR [AMOUNT],
CONSTRAINT [DF__LEDGERBUD__COMME__314E19FF] DEFAULT ('') FOR
[COMMENT_],
CONSTRAINT [DF__LEDGERBUD__DIMEN__32423E38] DEFAULT ('') FOR
[DIMENSION],
CONSTRAINT [DF__LEDGERBUD__DIMEN__33366271] DEFAULT ('') FOR
[DIMENSION2_],
CONSTRAINT [DF__LEDGERBUD__DIMEN__342A86AA] DEFAULT ('') FOR
[DIMENSION3_],
CONSTRAINT [DF__LEDGERBUD__AUTOT__351EAAE3] DEFAULT (0) FOR
[AUTOTRANS],
CONSTRAINT [DF__LEDGERBUD__CURRE__3612CF1C] DEFAULT ('') FOR
[CURRENCY],
CONSTRAINT [DF__LEDGERBUDGE__QTY__3706F355] DEFAULT (0) FOR [QTY],
CONSTRAINT [DF__LEDGERBUD__PRICE__37FB178E] DEFAULT (0) FOR [PRICE],
CONSTRAINT [DF__LEDGERBUDG__STOP__38EF3BC7] DEFAULT (0) FOR [STOP],
CONSTRAINT [DF__LEDGERBUDG__KEY___39E36000] DEFAULT ('') FOR [KEY_],
CONSTRAINT [DF__LEDGERBUD__EXPAN__3AD78439] DEFAULT (0) FOR
[EXPANDID],
CONSTRAINT [DF__LEDGERBUD__REPOR__3BCBA872] DEFAULT (0) FOR [REPORT],
CONSTRAINT [DF__LEDGERBUDGE__COV__3CBFCCAB] DEFAULT (0) FOR [COV],
CONSTRAINT [DF__LEDGERBUD__COVST__3DB3F0E4] DEFAULT (0) FOR
[COVSTATUS],
CONSTRAINT [DF__LEDGERBUD__CREDI__3EA8151D] DEFAULT (0) FOR
[CREDITING],
CONSTRAINT [DF__LEDGERBUDG__FREQ__3F9C3956] DEFAULT (0) FOR [FREQ],
CONSTRAINT [DF__LEDGERBUD__TAXGR__40905D8F] DEFAULT ('') FOR
[TAXGROUP],
CONSTRAINT [DF__LEDGERBUD__MODEL__418481C8] DEFAULT ('') FOR
[MODELNUM],
CONSTRAINT [DF__LEDGERBUD__INVEN__4278A601] DEFAULT (0) FOR
[INVENTRECID],
CONSTRAINT [DF__LEDGERBUD__INVEN__436CCA3A] DEFAULT (0) FOR
[INVENTTABLEID],
CONSTRAINT [DF__LEDGERBUD__ALLOC__4460EE73] DEFAULT (0) FOR
[ALLOCATEMETHOD],
CONSTRAINT [DF__LEDGERBUD__FOREC__455512AC] DEFAULT ('') FOR
[FORECASTMODELID],
CONSTRAINT [DF__LEDGERBUD__ASSET__464936E5] DEFAULT ('') FOR
[ASSETID],
CONSTRAINT [DF__LEDGERBUD__ASSET__473D5B1E] DEFAULT (0) FOR
[ASSETTRANSTYPE],
CONSTRAINT [DF__LEDGERBUD__ASSET__48317F57] DEFAULT ('') FOR
[ASSETBOOKID],
CONSTRAINT [DF__LEDGERBUD__MODIF__4925A390] DEFAULT ('?') FOR
[MODIFIEDBY],
CONSTRAINT [DF__LEDGERBUD__DATAA__4A19C7C9] DEFAULT ('dat') FOR
[DATAAREAID],
CHECK ([RECID] <> 0)
GO

CREATE INDEX [I_196ACCOUNTIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[ACCOUNTNUM], [MODELNUM], [STARTDATE]) ON [PRIMARY]
GO

CREATE INDEX [I_196EXPANDIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[EXPANDID]) ON [PRIMARY]
GO

CREATE INDEX [I_196REPIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[REPORT], [ACCOUNTNUM], [MODELNUM], [STARTDATE]) ON [PRIMARY]
GO

CREATE INDEX [I_196COVIDX] ON [dbo].[LEDGERBUDGET]([DATAAREAID],
[COVSTATUS]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [I_196RECID] ON
[dbo].[LEDGERBUDGET]([DATAAREAID], [RECID]) ON [PRIMARY]
GO

Sample data could be (I dont know how to make these in a file)

Sample data Ledgerbudget:
Accountnum, Startdate, Modelnum,Amount,Active,AllocateMethod
1, 01012006,Test1,100,1,0
1, 01012006,Test2,100,1,0

Sample data Guru_Admin (record with no BudgetModelId):
OLAPFromDate,OLAPToDate,BudgetModelId
01012006,31012006,''

Sample data Guru_Admin (record with BudgetModelId):
OLAPFromDate,OLAPToDate,BudgetModelId
01012006,31012006,'Test1'

So the first case should return both records from table LedgerBudget,
and the latter case should only return the first record from
LedgerBudget.

I hope you can be able to help with this matter.
Thanks /BR
Jan|||On 26 May 2006 00:34:23 -0700, jazpar wrote:

>Hi Hugo, and thanks
>Here is table ADMIN (actual name GURU_ADMIN)
(snip)

Hi Jan,

Thanks for the CREATE TABLE statements.

>Sample data could be (I dont know how to make these in a file)
>Sample data Ledgerbudget:
>Accountnum, Startdate, Modelnum,Amount,Active,AllocateMethod
>1, 01012006,Test1,100,1,0
>1, 01012006,Test2,100,1,0

Converting these rows to INSERT statements yields

INSERT INTO LEDGERBUDGET (ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT,
ACTIVE, ALLOCATEMETHOD)
SELECT 1, '20060101', 'Test1', 100, 1, 0
UNION ALL
SELECT 1, '20060101', 'Test2', 100, 1, 0

Howver, this gives me an error because several required columns are not
specified. Please either trim irrelevant columns from the CREATE TABLE
statement, or post INSERT statements that include all columns.

(snip)
>So the first case should return both records from table LedgerBudget,
>and the latter case should only return the first record from
>LedgerBudget.

Untested (for the reasons stated above), but maybe this works:

SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,
dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE,
- dbo.LEDGERBUDGET.AMOUNT AS BUDGET
FROM dbo.LEDGERBUDGET
INNER JOIN dbo.ADMIN
ON dbo.LEDGERBUDGET.STARTDATE >= dbo.ADMIN.FROMDATE
AND dbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATE
AND (dbo.LEDGERBUDGET.MODELNUM = dbo.ADMIN.BUDGETMODELID
OR dbo.ADMIN.BUDGETMODELID = '')
WHERE dbo.LEDGERBUDGET.ACTIVE = 1
AND dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0

--
Hugo Kornelis, SQL Server MVP|||jazpar (jannoergaard@.hotmail.com) writes:
> Sample data could be (I dont know how to make these in a file)

You don't know how to type INSERT statements?:

INSERT LEDGERBUDGET(ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT, ACTIVE,
ALLOCATEMETHOD, RECID)
VALUES(1, '20060101','Test1',100,1,0, 1)
INSERT LEDGERBUDGET(ACCOUNTNUM, STARTDATE, MODELNUM, AMOUNT, ACTIVE,
ALLOCATEMETHOD, RECID)
VALUES(1, '20060101', 'Test1', 100,1,0, 2)
go
INSERT GURU_ADMIN(OLAPFROMDATE, OLAPTODATE, BUDGETMODELID, RECID)
VALUES('20060101', '31010206','', 1)
INSERT GURU_ADMIN(OLAPFROMDATE, OLAPTODATE, BUDGETMODELID, RECID)
VALUES('20060101','31010206','Test1', 2)

If you had made the effort to do this, and actually tested the
script, it would have saved me the time from changing all the
column names, adding quotes, and fixing the bad dates.

I also like to remind you that part of the recommendation is that you
post the desired output from the query. This makes it possible to
test and validate the query.

Anyway, after having read your requirements, I think what you need is
to change the query to:

SELECT LTRIM(l.ACCOUNTNUM) AS ACCOUNT_ID,
l.STARTDATE AS TRANSDATE, - l.AMOUNT AS BUDGET
FROM dbo.LEDGERBUDGET l
LEFT JOIN dbo.GURU_ADMIN g ON
l.STARTDATE >= g.OLAPFROMDATE
AND l.STARTDATE <= g.OLAPTODATE
AND l.MODELNUM = coalesce(nullif(g.BUDGETMODELID, ''), l.MODELNUM)
WHERE l.ACTIVE = 1
AND l.ALLOCATEMETHOD = 0

The important line is:

AND l.MODELNUM = coalesce(nullif(g.BUDGETMODELID, ''), l.MODELNUM)

nullif says that space should be interpreted as NULL. (Your default
values appear excessive to me.) coalesce returns the first non-NULL
value of its argument.

The problem with this solution is that it may not perform well. But
without knowing sizes of the tables, I don't feel like considering
alternate solutions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Hugo

Thank you very much. This did the trick very well. I hadn't thought of
doing it this way. As you might expect I'm not an expert, but more into
ERP systems. But thanks againg.

BR/Jan|||Hi Erland

No actually I didn't know how to make insert statements. But thanks now
I do, and will remember this for the next time. I will try out your
sugestion and see how it works.
Thanks
BR/ Jan

How to make 2 columns in datagrid with random records?

Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
Well i found one way that was 2 tables the code ended like this
SqlConnection1.Open()
Dim sqlcon As NewSqlCommand("Select Firstname,spillernavn From rndnames CROSS JOINspillere ORDER BY NewID()", SqlConnection1)
Dim sqlrd As SqlDataReader
sqlrd = sqlcon.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataSource = sqlrd
DataGrid1.DataBind()
Well Well at least it works;)

How to loop through each row in table?

Hi guys,
Is there a trivial way for looping through each record in a table
that's equivalent to plsql's
for r1 in (select * from tablename) loop
v_name := r1.name;
...
end loop
?
I've tried the cursor approach but realised I'll need to define a
variable for each column inside the table. So am currently looking for
an alternative.
Thanks in advance.If you have to loop, then curosor is the only way.
--
"Opal" wrote:

> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
>|||Am 2 May 2006 21:23:29 -0700 schrieb Opal:

> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
If you have SQL2000 or above you can use TOP.
Here an example, let's say table has a unique field called id, which starts
with a value > 0:
select @.x = 0, @.y = max(id) from table
while @.x < @.y begin
select top 1 @.x = id, @.name = field1 from table where id > @.x order by id
.. do something with @.name ...
end
bye, Helmut|||Hi Opal,
You only need to declare variables for the columns you want, so if you are
only interested in [name] then just select that on the select statement in
the cursor...
declare tabcur cursor for
select name
from tablename
declare @.name nvarchar(100)
open tabcur
fetch next from tabcur into @.name
while @.@.fetch_status = 0
begin
... processing
fetch next from tabcur into @.name
end
deallocate tabcur
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Opal" <anchi.chen@.gmail.com> wrote in message
news:1146630209.489346.65260@.j73g2000cwa.googlegroups.com...
> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
>|||Hi,
Thanks Helmut. My concern with this approach (which is similar to what
I've done but with the cursor approach) is because my table contains
more than 20,000 rows, this means I'll be executing the select
statement >20,000 times.
But thanks anyway. It seems like there's not other ways around it.
Regards|||Thanks Tony,
Yes, but because I'll need to address every single columns in my table
(which is more than 20).
What I'm trying to do is actually copying a table from another database
into 2 tables. For example, say if the primary table has 5 columns,
I'll need to insert the first 2 columns into table 1 and the last 3
columns into table 2. And hence need to loop through an entire table
to access every single column.
Thanks|||I usually table variable than cursor.
DECALRE @.t TABLE (ID Identity,...,)
INSERT @.t SELECT ..,FROM Employees
SELECT @.max_cnt=COUNT(*) FROM @.t
WHILE(@.i<=@.max_cnt)
BEGIN
SELECT .., FROM @.t WHERE ID=@.i
..,
SET @.i=@.i+1
END
"Opal"?? ??? ??:

> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
>|||Oh, you don't need a cursor or owt like that, just use two insert
statements...
insert yourtable1( col1, col2 )
select col1, col2
from database1.dbo.tablename
insert yourtable2( col3, col4, col5 )
select col3, col4, col5
from database1.dbo.tablename
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Anchi" <anchi.chen@.gmail.com> wrote in message
news:1146640265.804502.181830@.g10g2000cwb.googlegroups.com...
> Thanks Tony,
> Yes, but because I'll need to address every single columns in my table
> (which is more than 20).
> What I'm trying to do is actually copying a table from another database
> into 2 tables. For example, say if the primary table has 5 columns,
> I'll need to insert the first 2 columns into table 1 and the last 3
> columns into table 2. And hence need to loop through an entire table
> to access every single column.
> Thanks
>|||Thanks Hongju, this table variable is a feature that I've been
searching for the entire day without success.
I wonder if I can define it as an existing table such that I don't need
to list out the column types?
eg
DECLARE @.t TABLE <existing_table_name>
?
Thanks|||Thanks again Tony,
The examples you've shown me is exactly what I'm doing to avoid
declaring variables for each column.
However because yourtable1 has a new column with type Identity and
yourtable2 has a foreign key referencing yourtable1, I cannot just use
2 simple and clean sql statements.
I'll give Hongju's solution a try.
Thanks again, really appreciate your help.

How to log uses of SELECT

Hello,
Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or entered) their data.
-So I need to be able to keep a log of which users have seen which patient files, but I don't control the app code (and wish to god there were such a thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure into the app?
Thanks,
JohnThere is no trigger on SELECT, and Profiler is probably not valuable enough
for the cost of it running constantly, but you should try it out. You might
investigate some of the auditing tools listed in http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:3F88A456-3DFF-4362-A1F8-E130E1A546E4@.microsoft.com...
> Hello,
> Here's my scenario:
> -I have a vendor application running sql2k.
> -I do not control the code and the vendor isn't currently interested in
implementing what I want.
> -I keep medical data in this app.
> -Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
> -So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
> Can I do this without necessarily being able to write a logging procedure
into the app?
> Thanks,
> John
>|||> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
Yikes, so if username does SELECT * FROM patients they're going to get a row
in your audit table for every row in your data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in
implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
-So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure
into the app?
Thanks,
John
--
It would be difficult because selects are not logged. You can turn on
profiler trace and capture all selects on specific tables. But reviewing
this output is not as straightforward as filtering by a certain patient_id.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Take a look at Lumigent Entegra (www.lumigent.com), which
is an audit tool for SQL Server. I'm not affiliated with
them, but I'm currently evaluating the product. It does
audit queries.
Linchi
>--Original Message--
>Hello,
>Here's my scenario:
>-I have a vendor application running sql2k.
>-I do not control the code and the vendor isn't currently
interested in implementing what I want.
>-I keep medical data in this app.
>-Patients, by law, are entitled to know who's seen (not
just changed or entered) their data.
>-So I need to be able to keep a log of which users have
seen which patient files, but I don't control the app code
(and wish to god there were such a thing as a select
trigger).
>If Joe comes to me soon, and says "I need to know who's
seen my data." I'd like to do something like:
>select username
>from audit_selects
>where patient_id = XXX
>and get a list of people who've seen this.
>Can I do this without necessarily being able to write a
logging procedure into the app?
>Thanks,
>John
>.
>