Is VARCHAR data type same as UTF-8?

Hello
Is data fields of varchar type internally encoded as UTF-8?
How is cyrillic text stored in varchar data fileds, as UTF-8 or not?No. Char, Varchar and Text are a CodePage representation.
If you don't want to have a lot of problems with Cyrillic and other things
like the Euro symbol; you should use nchar, nvarchar and ntext instead.
See:
http://msdn.microsoft.com/library/d...ataencoding.asp
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
<s_alexander04@.list.ru> wrote in message
news:1143694534.065217.68140@.i40g2000cwc.googlegroups.com...
> Hello
> Is data fields of varchar type internally encoded as UTF-8?
> How is cyrillic text stored in varchar data fileds, as UTF-8 or not?
>|||You can check windows region ,SQL Server settings and Database,Column
Collation.
To support globalization, you use Unicode data type such as
nchar,nvarchar,ntext.
"s_alexander04@.list.ru"?? ??? ??:

> Hello
> Is data fields of varchar type internally encoded as UTF-8?
> How is cyrillic text stored in varchar data fileds, as UTF-8 or not?
>

Is using varchar() as index a bad idea?

Hi ,
I have an application whose data is stored in some other db and I am
converting
to SQL. The existing app has a table, Contractors with a field ContractorCo
de
char(10) as an index to the table. For some reason, I am against using
recognizable data as an index an instead opt for GUID. I s having a char or
varchar
field as an index slower than a GUID field index?
ThanksOpa
I personally try to avoid creating an index on GUID . ( I try keep my index
as small as possible) Having index on VARCHAR/CHAR depends on your
specific requirements . See an execution plan of the query, is an optimizer
used the index and then make a decision?
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||There is a lot of debate on using GUIDs as primary keys. Here is an example:
http://www.sql-server-performance.c...erformance.asp. There is no
problem in using CHAR(10) as your index as long as the column helps you in
identifying the entity uniquely.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:584774
Index on GUID will take up more storage therefore bigger index therefore
more I/0. This would be pronounced on a clustered index.
The Contarctors table with Contractor code, what is the variety of data
within the col ? and what types of searches (if any) will be commited
on that column?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS4EcQjMGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Opa
> I personally try to avoid creating an index on GUID . ( I try keep my
index
> as small as possible) Having index on VARCHAR/CHAR depends on your
> specific requirements . See an execution plan of the query, is an
optimizer
> used the index and then make a decision?
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
char
>|||Opa (Opa@.discussions.microsoft.com) writes:
> I have an application whose data is stored in some other db and I am
> converting to SQL. The existing app has a table, Contractors with a
> field ContractorCode char(10) as an index to the table. For some
> reason, I am against using recognizable data as an index an instead opt
> for GUID. I s having a char or varchar field as an index slower than a
> GUID field index?
The last question is not really meaningful, because there are always a lot
of "it depends".
But generally, as a guid is 16 bytes, it's longer than the code, and the
longer the field, the less keys you get on a page, and the bigger the
index gets, and the more pages to read.
I would suspect, though, that the Contractors table is not of a size
where this is a much of an issue. Then again, the code may be used as an
FK in a larger table where it may matter.
Personally, I rather use the code as key, than GUID which is much more
difficult to manage. If you want artificial keys, integer is probably
better.
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|||Ok so if I have varchar(16) vs GUID will this index field always perform bet
ter
or equal to GUID is SELECTS, INSERTS, JOINS etc?
BTW, I disagree with your comment stating the column should help identify
the entity uniquely in a visual way.
"SriSamp" wrote:

> There is a lot of debate on using GUIDs as primary keys. Here is an exampl
e:
> http://www.sql-server-performance.c...erformance.asp. There is no
> problem in using CHAR(10) as your index as long as the column helps you in
> identifying the entity uniquely.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
>
>|||The performance aspect is something that you need to check for. Regarding my
other comment, what I meant was, if the CHAR(10) field is the "natural" key
for your table, you should go ahead and use it, rather than defining another
key.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:824B3723-E44D-410F-930F-58A4F31F5DB1@.microsoft.com...
> Ok so if I have varchar(16) vs GUID will this index field always perform
> better
> or equal to GUID is SELECTS, INSERTS, JOINS etc?
> BTW, I disagree with your comment stating the column should help identify
> the entity uniquely in a visual way.
>
> "SriSamp" wrote:
>|||Have a look at this excellent article from Kimberly L Tripp
http://www.sqlskills.com/blogs/kimb...
f-a290db645159
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks|||You, sir, are a moron. I usually don't say that about people, but I'll
make an exception in your case.
Stu|||When you use "index", I'm assuming you really mean to say "primary key". A
primary key is a type of index, but an index is not a primary key.
I don't believe there is any basic performance benefit to using integer
based keys over char based keys. As far as SQL Server is concerned, a key is
just a block of bytes, and the smaller the better. A GUID is typically
displayed as a string of text, but is stored internally as a 16 byte
integer. Consider instead an int (4 bytes) or smallint (2 bytes) identity.
The only use I can see for using GUID (globally unique) keys is if the
Contractor data is to be merged with Contractor data from another system and
you want to retain the same surrogate key values. However, this could be
better implemented by adding a SystemCode to the Contractors table and have
SystemCode + ContractorCode as the primary key.
Perhaps keeping ContractorCode as the primary key would be the best solution
unless there is a strong and compelling reason to implement a surrogate key.
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:694D881A-1A70-4233-AC08-7392F3816A19@.microsoft.com...
> Hi ,
> I have an application whose data is stored in some other db and I am
> converting
> to SQL. The existing app has a table, Contractors with a field
> ContractorCode
> char(10) as an index to the table. For some reason, I am against using
> recognizable data as an index an instead opt for GUID. I s having a char
> or
> varchar
> field as an index slower than a GUID field index?
>
> Thanks

Is using Max in varchar a bad thing?

I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.
> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?

> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.
|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A
|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J
|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
sql

Is using Max in varchar a bad thing?

I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.Hi Jeff,
I would avoid using varchar(max) unless you actually need to store
very long values (> 8000 chars)
As an alternative, why not create your temp table from the underlying
source columns - that way your temp table columns will always match
the source columns.
You can do this very easily by creating the table like this:
SELECT <col list>
INTO #tmp_table
FROM <table list>
WHERE 1 = 0
Good luck!
J|||> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A

Is this SQL stored prodcedure is valid

what i want to achive is the proc sh'd return a master-detail value in one go.
master value should be returned with Out Parameter and detail value as a recordset.

will it return the recordset of detail table as below...

Create Procedure ProductDetail
(
 @.ProductID int,
 @.ProductCode varchar(15) OUTPUT,
 @.ProductName varchar(60) OUTPUT,
 @.CategoryID int OUTPUT,
 @.CategoryName varchar(60) OUTPUT,
 @.Image1 varchar(256) OUTPUT,
 @.Image2 varchar(256) OUTPUT,
 @.UnitPrice smallmoney OUTPUT,
 @.UOMValue numeric(9) OUTPUT,
 @.UOMName varchar(10) OUTPUT,
 @.ShippingWeight numeric(9) OUTPUT,
 @.Directions varchar(1500) OUTPUT,
 @.Ingrediants varchar(1500) OUTPUT,
 @.Warnings varchar(1500) OUTPUT,
 @.ShortDescription varchar(1000) OUTPUT,
 @.LongDescription varchar(2000) OUTPUT,
 @.NutritionFacts varchar(1000) OUTPUT,
 @.SearchKeywords varchar(500) OUTPUT,
 @.IsTaxable varchar(15) OUTPUT,
 @.CreatedBy varchar(60) OUTPUT,
 @.CreatedOn varchar(15) OUTPUT,
 @.UpdatedBy varchar(60) OUTPUT,
 @.UpdatedOn varchar(15) OUTPUT,
 @.Status int OUTPUT
)
AS
SELECT
 @.ProductCode = ProductCode,
 @.ProductName = ProductName,
 @.CategoryID = CategoryID,
 @.CategoryName = (select CategoryName from mCategory where CategoryID=a.CategoryID),
 @.Image1 = isnull(Image1,''),
 @.Image2 = isnull(Image1,''),
 @.UnitPrice = isnull(UnitPrice,0),
 @.UOMValue = isnull(UOMValue,0),
 @.UOMName = isnull(UOMName,''),
 @.ShippingWeight = isnull(ShippingWeight,0),
 @.Directions = isnull(Directions,''),
 @.Ingrediants = isnull(Ingrediants,''),
 @.Warnings = isnull(Warnings,''),
 @.ShortDescription = isnull(ShortDesc,''),
 @.LongDescription = isnull(LongDesc,''),
 @.NutritionFacts = isnull(NutritionFacts,''),
 @.SearchKeywords = isnull(SearchKeywords,''),
 @.IsTaxable = case when isnull(IsTaxable,0)=0 then 'No' else 'Yes' End,
 @.CreatedBy = isnull((select LName + ',' + FName from mUser where UserID=InsertedBy),''),
 @.CreatedOn = InsertedOn,
 @.UpdatedBy = isnull((select LName + ',' + FName from mUser where UserID=UpdatedBy),''),
 @.UpdatedOn = UpdatedOn,
 @.Status = Convert(int,isnull(Status,0))
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
BhairavI believe the way you are doing it is possible, but why not return two recordsets back to a dataset? Then you would have a master datatable and detail datatable. I believe this would work:

Create Procedure ProductDetail
(
 @.ProductID int
)
AS
SELECT
 ProductCode,
 ProductName,
 CategoryID,
 (select CategoryName from mCategory where CategoryID=a.CategoryID),
 isnull(Image1,''),
 isnull(Image1,''),
 isnull(UnitPrice,0),
...
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

HTH|||thks for u'r suggesion
but can u plz explain me in more detail ...
how my dataset code will look like when a single strore proc return more than one recordset.

proc must not be called more than once for that...

Regards,
Bhairav|||Sure,
Just follow the code (I'm using Microsoft Data Access Application Blocks to call):

SqlParameter [] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@.ProductID", SqlDbType.Int);
arParms[0].Value = 1;
DataSet myDS = SQLHelper.ExecuteDataset("connectiion", StoredProcedure, "ProductDetail", arParms);
DataTable myTable1 = myDS.Tables[0];
DataTable myTable2 = myDS.Tables[1];

That should give you an example of the calling code. There are other ways to manipulate the dataset data. If you are unfamiliar, just hollar and we can give you some direction, or search the archives of the data access forms. HTH|||thks..
its really the nice way to code
thks again

Regards,
Bhairav

Is this SQL related problem?

Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please

Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)

Timeout means that the server does not replies or completes the job within 30 sec (default)
Are you sure the the database is online ?
Can you execute a simple query such as select @.@.spid without having error ?
Can you post the query ?

Is this possible? SQL Server 2000 Write times

I created an online application for one of our educational programs. The da
ta is inserted via website entry -> client side validation -> stored procedu
re -> database. Given a table stucture as follows, if 100 or even 1000 peop
le applied at the exact same time using an online application, what is the m
aximum time interval that could separate each record. How long would SQL Se
rver 2000 generally take to insert each record into the database. The probl
em is that each year, only the top 100 students are admitted in the program,
but there is one student who says they applied at 9:00 am and their record
insert time shows 9:59am. There are records before it and after it that sho
ws times like 9:58, 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL
Server 59 minutes to actually write the record if 1000 people hit the datab
ase at the same time? Please help, they want to get counsil involved.
ApplicantID int primary key identity
SSN varchar(11)
FirstName varchar(30)
LastName varchar(50)indexed
Address varchar (50)
City varchar (50)
State char (2)
Zip varchar (11)
Phone varchar (20)
Email varchar (50)
dateEntered smalldatetimeShawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?

Is this possible?

I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:

> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:

> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:

>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.

Is this possible?

I have a stored procedure and I want to do something like:
INSERT INTO @.Table
(Field1, Field2, Field3, ...FieldN)
SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
FROM @.Table2
WHERE n is null
SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
FROM @.Table2
WHERE x < 50
SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
FROM @.Table2
WHERE x >= 50
Can I somehow do this? Since I couldn't return the result sets from my
3 selects, I combined them into one result set.
Right now I have a giant UPDATE statement, but it seems really
unwieldy...it's something like:
UPDATE @.Table
SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
There has to be a better way. (Can you tell I only half know what I'm
doing?)
Thank you!union your selects together
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Do:
SELECT SUM( CASE WHEN n IS NULL THEN a END ) AS "Field1",
SUM( CASE WHEN n IS NULL THEN b END ) AS "Field2",
SUM( CASE WHEN n IS NULL THEN c END ) AS "Field3",
SUM( CASE WHEN x < 50 THEN a END ) AS "Field4",
SUM( CASE WHEN x < 50 THEN b END ) AS "Field5",
SUM( CASE WHEN x < 50 THEN c END ) AS "Field6",
SUM( CASE WHEN x >= 50 THEN a END ) AS "Field7",
SUM( CASE WHEN x >= 50 THEN b END ) AS "Field8",
SUM( CASE WHEN x >= 50 THEN c END ) AS "Field9"
FROM Table2 ;
Anith|||"Confused" <cschanz@.gmail.com> wrote in message
news:1135287913.453858.252290@.g49g2000cwa.googlegroups.com...
>I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>
See the following example. Of course you would also replace the word "field"
with "column" because if you even half knew what you were doing then you'd
know that a column isn't a field. :-)
INSERT INTO @.Table
(field1, field2, field3, field4, field5, field6, field7, field8, field9)
SELECT
SUM(CASE WHEN n IS NULL THEN a END) AS field1,
SUM(CASE WHEN n IS NULL THEN b END) AS field2,
SUM(CASE WHEN n IS NULL THEN c END) AS field3,
SUM(CASE WHEN x < 50 THEN a END) AS field4,
SUM(CASE WHEN x < 50 THEN b END) AS field5,
SUM(CASE WHEN x < 50 THEN c END) AS field6,
SUM(CASE WHEN x >= 50 THEN a END) AS field7,
SUM(CASE WHEN x >= 50 THEN b END) AS field8,
SUM(CASE WHEN x >= 50 THEN c END) AS field9
FROM @.Table2 ;
David Portas
SQL Server MVP
--|||Use a case statement to populate Table2.
select <Other Columns>, sum(case when n is null then a else 0 end) as
Field1,
sum(case when n is null then b else 0 end) as Field2,
sum(case when n is null then c else 0 end) as Field3,
sum(case when x < 50 then a else 0 end) as Field4,
sum(case when x < 50 then b else 0 end) as Field5,
sum(case when x < 50 then c else 0 end) as Field6,
sum(case when x >= 50 then a else 0 end) as Field7,
sum(case when x >= 50 then b else 0 end) as Field8,
sum(case when x >= 50 then c else 0 end) as Field9
from Table2
group by <Other Columns>|||ok - i'm not even waiting for my other post to get out there - ignore it
- incomplete
insert into @.table (Field1, ..., Field9)
select
sum(case when n is null then a end) as Field1,
sum(case when n is null then b end) as Field2,
sum(case when n is null then c end) as Field3,
sum(case when x<50 then a end) as Field4,
sum(case when x<50 then b end) as Field5,
sum(case when x<50 then c end) as Field6,
sum(case when x>=50 then a end) as Field7,
sum(case when x>=50 then b end) as Field8,
sum(case when x>=50 then c end) as Field9
from @.Table2
another possibility is to change your table1 to have a criteria
indicator and fewer columns, and union the queries together, e.g.
insert into @.Table (criteria, Field1, Field2, Field3)
select 'null n', sum(a), sum(b), sum(c)
from @.table2
where n is null
union all
select 'x>50', sum(a), sum(b), sum(c)
from @.table2
where x>50
union all
select 'x<=50', sum(a), sum(b), sum(c)
from @.table2
where x<=50
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Oops...I forgot to put that...I did try that. And when I do that I get
the following error:
The select list for the INSERT statement contains fewer items than the
insert list. The number of SELECT values must match the number of
INSERT columns.|||Thank you everyone for the input and help! It is much appreciated!

Is this possible without using Cursors?

Hello!
I have a stored proc that checks the file existance in one location and
copy them to another location. The stored proc reads the record one by
one and builds the DOS COPY command. In the end, it exccutes the DOS
command using xp_cmdshell. See below the code.
The stored proc is working great but I had to use the CURSOR for reading
the records. I was wondering if I can avoid using it? Is this possible?
how? Thanks in advance for your help!
CREATE TABLE [dbo].[FILE_PATH] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Source] [varchar] (150) NULL ,
[Destination] [char] (150) NULL ,
[Environment] [char] (25) NULL ,
[Filename] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file1')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file2')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
CREATE proc CopyFiles
as
set nocount on
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
declare @.source_dir varchar(200)
declare @.filename varchar(30)
declare @.environment varchar(200)
declare @.environementTmp varchar(200)
declare @.errortext varchar(200)
select
@.environementTmp =
case @.@.servername
when 'server A' then 'Development'
when 'server B' then 'UAT'
when 'server C' then 'Production'
end
create table #files(filename sysname NULL)
declare filecursor cursor for
select source, destination, environment, [filename]
from file_path
open filecursor
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
while (@.@.fetch_status = 0)
begin
if @.environementTmp = @.environment
begin
set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
+ ' /b'
insert #files exec master..xp_cmdshell @.source_dir
if (select filename from #files where filename is not null) = @.filename
begin
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
' +'"'+ rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
end
else
begin
set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
exist!!!'
exec master.dbo.xp_sendmail
@.recipients = 'abc123',
@.copy_recipients = 'abc123'
@.subject = @.errortext,
@.message = @.errortext
end
end
truncate table #files
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
end
close filecursor
deallocate filecursor
GO
*** Sent via Developersdex http://www.examnotes.net ***Test Test wrote:
> Hello!
> I have a stored proc that checks the file existance in one location
> and copy them to another location. The stored proc reads the record
> one by one and builds the DOS COPY command. In the end, it exccutes
> the DOS command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for
> reading the records. I was wondering if I can avoid using it? Is this
> possible? how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5') go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) =
> @.filename begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
Something tells me more time is spent executing xp_cmdshell than the
time used for the cursor, so you're probably fine. Cursors are good for
this type of operation IMO since it makes the code relatively clear. You
should always use local, read-only forward-only cursors, for this type
of read-only processing so update your declare statement accordingly.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||We had a similar problem - we solved this by using COM objects.
We created our own custom set of COM objects, but WSH.FileSystemObject does
the same thing.
Essentially:-
Call master.dbo.sp_OACreate to create the com object (returns @.lpObject)
Then create a UDF to do the file exists check
Call master.dbo.sp_OAMethod for file exists passing filename returning file
exists
Then destroy the object using master.dbo.sp_OADestory
Then you'd do
UPDATE
tableWithFileNamesIn
SET
FileExists = dbo.fnFileExists( @.lpObject , FullPathToFile ) AS
FileExists
Assuming: tableWithFileNamesIn( FullPathToFile VARCHAR(260) , FileExists
BIT )
http://msdn.microsoft.com/library/d.../>
sotutor.asp
If the process isn't going to run as system admin, create a role in master
called COMCreator, and add all the sp_OA... (except for OAStop) to that
role and add the process user account to COMCreator.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uB58v9A0FHA.268@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a stored proc that checks the file existance in one location and
> copy them to another location. The stored proc reads the record one by
> one and builds the DOS COPY command. In the end, it exccutes the DOS
> command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for reading
> the records. I was wondering if I can avoid using it? Is this possible?
> how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) = @.filename
> begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Is this possible ?

is it possible to have a select statement from a stored proceedure ? example ...

CREATE PROCEDURE spA
SELECT * FROM ( exec spB '1','1' )

go

the reason for doing this is becos spB does some data massaging and is used across many stored procedures, spB will be passing back a table if it is possible.

Thanks.I'd recommend that you rewrite spB as a User-defined table function. Then you can reference it in stored procedures, views, triggers, etc, just like any other table or subquery:

CREATE PROCEDURE spA
SELECT * FROM dbo.spB ('1','1')

blindman|||Or it that's something you would like to avoid, you could use

Insert Into #TmpSpB Exec SpB '1','1'

where #TmpSpB is a temporary table which has the structure of the result return by the stored procedure. The only problem with this, is that can not be nested, and I am not sure if it's working "below" SQL2000.

Best regards!|||thanks for the replies ... you've been a great help ...

IS THIS GOOD DESIGN?

I have a 3 tables
tblAgent, tblClient, tblVendor.
Since each entity requires different info to be stored, it makes sense to
have 3 different tables.
Now each agent, client or vendor can write many notes, so I created the
table like this to store all info
ContactID, ContactTypeID, NoteEnterDate, Note
where
ContactID will contain the id of either agent, client, or vendor (since id
will be the same data type for all 3 tables)
ContactTypeID 1 = Agent
ContactTypeID 2 = Client
ContactTypeID 3 = Vendor
So based on the ContactTypeID passed in to stored procedure as parameter, I
would know which table to join and contactID will be selective to filter
data. Of course there would be no referential integrity with ContactID
since it contains ID from 3 different tables. I am planning to have a
clustered index on ContactID, ContactTypeID (table will have no primary key)
Is this good design? Should I have separate note table for each Agent,
Client, and Vendor?
Can you think of any better design with regard to indexes?
Can you think of any better design to accomodate aforementioned scenario?
ThanksLooks good enough except that
contacttypeid,contactid,notedate (in that order can) be the primary key
clustered, if your notedate is not smalldatetime, of course.
--
"Justin" wrote:

> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary ke
y)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>
>|||If each row in this notes table will specifically apply to only one of the
other three tables, I would say create 3 seperate tables for notes. This
lets you define the referential integrity constraints and you will know that
all client notes are in one place, vendors in another, etc. Other than the
table structures being similar, is there any other reason why you would want
these all in one table?
"Justin" <jus820@.hotmail.com> wrote in message
news:O3WqCxebGHA.1208@.TK2MSFTNGP04.phx.gbl...
> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary
key)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>sql

Is this a silly way to use SSIS?

I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.

I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.

My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.

I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?

Thanks in advance for any info.

I'm a bit biased but in my opinion if you're moving data about then you should use SSIS. There's no point in building an application when you have a product available to do the work for you!

-Jamie

|||

Thanks for the feedback Jamie, I've read a lot of your posts so I will take what you say very seriously. I guess about the only thing I can think of to reply is that if my client decided tomorrow to scrap SQL Server for MySQL or Oracle or DB2 (all of which they have and run at various locales in the organization) then a properly written VB app could be modified slightly and keep on ticking. But if they scrap SQL server (and hey, with the way acquisitions, etc. work these days it's not beyond the realm of possibility) then they would have to start from scratch with their ETL process. But, I guess that would really argue more for a 3rd party ETL tool than for a special purpose written app.

Thanks,

Steve

|||

Note that your client's company could choose to use other database storage engines, but continue using SQL Server Integration Services, especially in light of the favorable pricing (for SQL). We knew of companies that purchased SQL licenses in the past just to get DTS...given the increased power of SSIS, this may become even more common (although we'd love to see everyone taking advantage of the entire SQL package, of course).

|||

Absolutely, SSIS has no reliance on SQL Server.

Its also worth saying that if you were to change your RDBMS its unlikely that your SSIS packages would be too affected - assuming the metadata of the tables is the same. In certain (lucky) scenarios the only thing you might have to change is a connection string. And if you're using best practice of storing connection strings external to your package then that is incredibly easy

-Jamie

Is this a permissions problem

I have a stored procedure that creates a temporary table, populates it,
deletes certain records from it and then selects all the data from it.
In query analyzer I get a resultset, however in my VB6 code it doesn't
return a resultset, the recordset isn't even open after running it.
If I run other stored procedures they work no problem and return a resultset
i cant seem to figure out the issue and I am assuming that its down to
permissions, as the only difference in the sp's are that this one uses temp
tables, although I thought that any #tables created inside of a stored
procedure are there till execution of the sp ends. Do I have to set
something that allows temporary tables to be created
TIAI dont think this is a permission problem. Just check to which SP u are
referring to and in which database does the SP reside.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||I'm calling the right sp as I can call some others from the same database, I
can pass paramaters to the other sps and they run fine, the only difference
is one creates and uses a temp table, yet it runs fine under QA.
Its just bugging me as I can't seem to figure out the problem
"Chandra" wrote:
> I dont think this is a permission problem. Just check to which SP u are
> referring to and in which database does the SP reside.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "steven scaife" wrote:
>|||I found the solution needed to have
set nocount on in my sp
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||SET NOCOUNT ON suppresses DONE_IN_PROC messages and can improve performance
by avoiding extra round trips. With the default SET NOCOUNT OFF,
DONE_IN_PROC messages are returned to ADO apps as empty closed recordsets
and can interfere with data retrieval unless you skip them using the
NextRecordset method.
Hope this helps.
Dan Guzman
SQL Server MVP
"steven scaife" <stevenscaife@.discussions.microsoft.com> wrote in message
news:88F9E00F-DFB3-4E44-AAC6-454042C7DE46@.microsoft.com...
>I found the solution needed to have
> set nocount on in my sp
> "steven scaife" wrote:
>sql

Is this a permissions issue?

Hi All I have created a Stored proc to get the all user rights on a server b
y
database. I am going to use this SP in my reporting services 2005. I
created a user with public rights in all Databases on my server. For some
reason unless I grant this user SA rights, it will not return any rows. whe
n
I give him SA rights all rows return correctly. I am attaching my code can
someone see why SA rights are the only rights that seem to work?
TIA,
Joe
Here is the code!
/*
if exists (select * from sysobjects where id =
object_id('SOX_Audit..Temp_DBO_Audit') )
DROP table SOX_Audit..Temp_DBO_Audit
create table SOX_Audit..Temp_DBO_Audit (DBNAme varchar(50),DbFixedRole
varchar(25),MemberName varchar(50),MembersIS Varbinary(85))
if exists (select * from sysobjects where id =
object_id('SOX_Audit..Temp_Users_Audit') )
DROP table SOX_Audit..Temp_Users_Audit
create table SOX_Audit..Temp_Users_Audit (DbFixedRole varchar(25),MemberName
varchar(50),MembersIS Varbinary(85))
*/
delete Temp_DBO_Audit
delete Temp_Users_Audit
declare @.DBName varchar(50), @.str varchar(100)
Declare crsCall cursor for
select name from master..sysdatabases
open crsCall
fetch Next from CrsCall
into @.DBName
while @.@.fetch_status=0
begin
select @.str = 'use ['+@.DBNAMe+']'+ Char(13) +
'insert into SOX_Audit..Temp_Users_Audit exec sp_helprolemember '
--select @.str
exec (@.str)
insert into SOX_Audit..Temp_DBO_Audit
select @.DBNAme, DbFixedRole, MemberName, MembersIS
from SOX_Audit..Temp_Users_Audit
delete SOX_Audit..Temp_Users_Audit
fetch Next from CrsCall
into @.DBName
end
close crsCAll
deallocate crsCAll
select DBName, dbFixedRole, MemberName from SOX_Audit..Temp_DBO_Auditjaylou (jaylou@.discussions.microsoft.com) writes:
> Hi All I have created a Stored proc to get the all user rights on a
> server by database. I am going to use this SP in my reporting services
> 2005. I created a user with public rights in all Databases on my
> server. For some reason unless I grant this user SA rights, it will not
> return any rows. when I give him SA rights all rows return correctly.
> I am attaching my code can someone see why SA rights are the only rights
> that seem to work?
That is indeed a permissions issue. In SQL 2005, you are not permitted
to see metadata in the same way as you were in previous versions of SQL
Server. Essentially, you need permissions to view metadata. Sometimes
that is implicit. If you have SELECT permissions on a table, you also
have VIEW DEFINITION on the table.
The hour is late, and I don't know on the top of my head which exact
privileges that are needed to view this kind of information. Then
again, to be permitted to the information your procedure retrieves,
you should have fairly hefty privileges.
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

Is this a bug? And does SQL Server 2005 fix it?

Hi all,
In the past, I've tried something like the following in my stored procedures
to allow table filtering:
SELECT * FROM Widgets
WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
AND (@.Color IS NULL OR Widgets.Color = @.Color)
NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
parameters to the stored procedure, allowing me to filter down my Widgets
table quite nicely.
This works great, but apparently hinders SQL Server 2000's ability to index
the table! This can lead to very bad performance when doing these types of
filtered queries.
My questions are: 1) Is this behavior a bug? I'm not sure why adding the IS
NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
the same problem?
Thanks!
George Saliba
Six88 SolutionsThe ISNULL is not the cause, the OR is the bad guy.
Example:
use northwind
go
exec sp_helpindexes 'dbo.orders'
go
set showplan_text on
go
declare @.d datetime
select orderid, orderdate, customerid
from dbo.orders
where orderdate = @.d and @.d is null
select orderid, orderdate, customerid
from dbo.orders
where orderdate = @.d OR @.d is null
go
set showplan_text off
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"George Saliba" wrote:

> Hi all,
> In the past, I've tried something like the following in my stored procedur
es
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
> NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
> parameters to the stored procedure, allowing me to filter down my Widgets
> table quite nicely.
> This works great, but apparently hinders SQL Server 2000's ability to inde
x
> the table! This can lead to very bad performance when doing these types of
> filtered queries.
> My questions are: 1) Is this behavior a bug? I'm not sure why adding the I
S
> NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
> the same problem?
> Thanks!
> George Saliba
> Six88 Solutions|||Ahh, very interesting!
So why does an OR clause cause indexes to be used when one side of the OR
clause contains a scalar (@.Column IS NULL) and the other contains a table
column (Table.Column = @.Column)? It seems like if one side is a scalar it
should still be able to maintain indexes?
I guess that's my real question then. Does an OR clause have to cause SQL
Server to not use indexes, even when one side of the OR clause is purely
scalar?
Thanks!
-George
"Alejandro Mesa" wrote:
> The ISNULL is not the cause, the OR is the bad guy.
> Example:
> use northwind
> go
> exec sp_helpindexes 'dbo.orders'
> go
> set showplan_text on
> go
> declare @.d datetime
> select orderid, orderdate, customerid
> from dbo.orders
> where orderdate = @.d and @.d is null
> select orderid, orderdate, customerid
> from dbo.orders
> where orderdate = @.d OR @.d is null
> go
> set showplan_text off
> go
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "George Saliba" wrote:
>|||SQL Server Transact-SQL WHERE Clause
http://www.sql-server-performance.c...t_sql_where.asp
AMB
"George Saliba" wrote:
> Ahh, very interesting!
> So why does an OR clause cause indexes to be used when one side of the OR
> clause contains a scalar (@.Column IS NULL) and the other contains a table
> column (Table.Column = @.Column)? It seems like if one side is a scalar it
> should still be able to maintain indexes?
> I guess that's my real question then. Does an OR clause have to cause SQL
> Server to not use indexes, even when one side of the OR clause is purely
> scalar?
> Thanks!
> -George
> "Alejandro Mesa" wrote:
>|||> I guess that's my real question then. Does an OR clause have to cause SQL
> Server to not use indexes, even when one side of the OR clause is purely
> scalar?
SQL Server 2005 corrects this to some degree. I took Alejandro's script and
modified it for the new AdventureWorks database:
set nocount on
use AdventureWorks
go
exec sp_helpindex 'Sales.SalesOrderHeader'
go
set showplan_text on
go
declare @.d datetime
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
set @.d = '20040731'
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
go
set showplan_text off
go
All four queries utilize a clustered index scan. The ones with AND perform
an additional filter before the scan. Here is the output, including
sp_helpindex:
index_name index_description index_keys
AK_SalesOrderHeader_rowguid nonclustered, unique located on PRIMARY rowguid
AK_SalesOrderHeader_SalesOrderNumber nonclustered, unique located on PRIMARY
SalesOrderNumber
IX_SalesOrderHeader_CustomerID nonclustered located on PRIMARY CustomerID
IX_SalesOrderHeader_SalesPersonID nonclustered located on PRIMARY
SalesPersonID
PK_SalesOrderHeader_SalesOrderID clustered, unique, primary key located on
PRIMARY SalesOrderID
StmtText
----
---
declare @.d datetime
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
StmtText
----
----
---
|--Filter(WHERE:(STARTUP EXPR([@.d] IS NULL)))
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d]))
StmtText
----
--
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
StmtText
----
----
---
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d] OR [@.d]
IS NULL))
StmtText
----
--
set @.d = '20040731'
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
StmtText
----
----
---
|--Filter(WHERE:(STARTUP EXPR([@.d] IS NULL)))
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d]))
StmtText
----
--
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
StmtText
----
----
---
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d] OR [@.d]
IS NULL))
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Hmm, it's unfortunate that this IS NULL is one of these non-sargable beasts.
It sure would make my life easier if they weren't. :(
On the upside, I realized I can remove the @.Param IS NULL part for columns
that are not nullable (since Column = @.Param where the @.Param is null will
return false always for non-nullable columns), which would leave only the
sargable part of those parts of the WHERE clause, which should increase
performance a bit. :)
Not ideal, but it will do. Thanks for your help!
-George
"Alejandro Mesa" wrote:
> SQL Server Transact-SQL WHERE Clause
> http://www.sql-server-performance.c...t_sql_where.asp
>
> AMB
> "George Saliba" wrote:
>|||"George Saliba" <GeorgeSaliba@.discussions.microsoft.com> wrote in message
news:6592A19D-5F74-44F2-94C9-401301101EA7@.microsoft.com...
> Hi all,
> In the past, I've tried something like the following in my stored
procedures
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
As mentioned the Or is the problem: If the Columns cannot be null you can
use:
WHERE (Widgets.WidgetID = COALESCE(@.WidgetID,Widgets.WidgetID))
AND (Widgets.WidgetTypeID = COALESCE(@.WidgetTypeID,Widgets.WidgetTypeID))
AND (Widgets.Color = COALESCE(@.Color,Widgets.Color))
Good Luck,
Jim|||George,
In some situation, the following syntax improves the performance a lot.
However, you have to make sure that the columns do not contains NULLs,
because this syntax will discard all rows with NULLs in any of the
columns WidgetID, WidgetTypeID, Color.
The query assumes that WidgetID is an int, WidgetTypeID is a smallint
and Color is a char or varchar.
SELECT * FROM Widgets
WHERE WidgetID BETWEEN COALESCE(@.WidgetID, -2147483648)
AND COALESCE(@.WidgetID, 2147483647)
AND WidgetTypeID BETWEEN COALESCE(@.WidgetTypeID, -32768)
AND COALESCE(@.WidgetTypeID, 32767)
AND Color LIKE COALESCE(@.Color,'%')
Hope this helps,
Gert-Jan
George Saliba wrote:
> Hi all,
> In the past, I've tried something like the following in my stored procedur
es
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
> NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
> parameters to the stored procedure, allowing me to filter down my Widgets
> table quite nicely.
> This works great, but apparently hinders SQL Server 2000's ability to inde
x
> the table! This can lead to very bad performance when doing these types of
> filtered queries.
> My questions are: 1) Is this behavior a bug? I'm not sure why adding the I
S
> NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
> the same problem?
> Thanks!
> George Saliba
> Six88 Solutions|||Nice trick with between (I have used the '%' trick for varchar values
before). Probably not a big difference, but you could use 0 instead of the
lower bound if you know the values must all be positive integers.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601C3E.2AE96DC5@.toomuchspamalready.nl...
> George,
> In some situation, the following syntax improves the performance a lot.
> However, you have to make sure that the columns do not contains NULLs,
> because this syntax will discard all rows with NULLs in any of the
> columns WidgetID, WidgetTypeID, Color.
> The query assumes that WidgetID is an int, WidgetTypeID is a smallint
> and Color is a char or varchar.
> SELECT * FROM Widgets
> WHERE WidgetID BETWEEN COALESCE(@.WidgetID, -2147483648)
> AND COALESCE(@.WidgetID, 2147483647)
> AND WidgetTypeID BETWEEN COALESCE(@.WidgetTypeID, -32768)
> AND COALESCE(@.WidgetTypeID, 32767)
> AND Color LIKE COALESCE(@.Color,'%')
> Hope this helps,
> Gert-Jan
>
> George Saliba wrote:

Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is these .net methods supported to call from SQL Stored Procedures?

Is these .net methods supported to call from SQL Stored Procedures?

public static int MultiplyMany(params int[] intArray)
{
int result = 1;

foreach(int now in intArray)
{
result *= now;
}

return result;
}

public static void TestOut(string name, out string firstName, out string lastName)
{
int spaceIndex = name.IndexOf(' ');
firstName = name.Substring(0, spaceIndex);
lastName = name.Substring(++spaceIndex);
}

What do you mean by "call from SQL SP"? You cannot call CLR methods directly from Transact-SQL. You can invoke them from T-SQL only by calling SQLCLR stored procedure/function/UDT method etc.

How do you expect to obtain an int[] array in SQL?

MultiplyMany method: you cannot make it as SQLCLR stored procedure, because there is no SQL type corresponding to int[]. If you will call it from another SQLCLR method - then, of course, you will be able to call it as you are already inside CLR.
If you are trying to multiply values from the column of the table it might be useful for you to look through the new SQL CLR User-Defined Aggregate.

TestOut method - you will have to create an SQLCLR stored procedure that will invoke this method to be able to call it from T-SQL. You can either add [SqlProcedure] attribute to the method so that SQLCLR SP will be created automatically by Visual Studio, or manually run the statement (don't forget to replace AssemblyName, YourNamespace and ClassName with correct ones ):

CREATE PROCEDURE dbo.TestOut
@.name nvarchar(250),
@.firstName nvarchar(125) output,
@.lastName nvarchar(125) output
AS
EXTERNAL NAME [AssemblyName].[YourNamespace.ClassName].[TestOut]

|||

I agree with you for TestOut.

For the 1st method I didn't think that I would send array object offcourse, but you didnot notice the params keyword.
My question for this method is: is it supported to send undetermined number of parameters that will be put into array in the .net?

Thank you for your help.

|||No, it is not possible.
Don't forget that each SQLCLR stored procedure has two parts: implementation on the CLR side and registration of the stored procedure on the SQL side. If the syntax of CREATE PROCEDURE statement doesn't allow undetermined number of parameters, you cannot expect it from SQLCLR stored procedure.|||

Thank you.

I am convinced.

Is there way to run something on subscriber after distribution agent pushed transactions?

I need execute stored procedure on subscriber right after distribution agent
finished pushing transactions.
How to do it?
I don't have anonymous and pull subscribers.
MS SQL2K+SP3a
In the best we trust
Georgy Nevsky
If you are not running continuously you can make this the 4th job step.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Georgy Nevsky" <apokriffree@.hotbox.ru> wrote in message
news:%23gVfg7UCFHA.2032@.tk2msftngp13.phx.gbl...
> I need execute stored procedure on subscriber right after distribution
agent
> finished pushing transactions.
> How to do it?
>
> I don't have anonymous and pull subscribers.
> MS SQL2K+SP3a
>
> --
> In the best we trust
> Georgy Nevsky
>
|||I'm manually running distribution agent to push changes from publisher to
subscriber and I'm running that on publisher but I need execute stored
procedure on subscriber so I can't add that as last step. Is there other
way?
In the best we trust
Georgy Nevsky
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O9n8fCWCFHA.3784@.TK2MSFTNGP15.phx.gbl...
> If you are not running continuously you can make this the 4th job step.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Georgy Nevsky" <apokriffree@.hotbox.ru> wrote in message
> news:%23gVfg7UCFHA.2032@.tk2msftngp13.phx.gbl...
> agent
>
|||In my push subscriptions, I have a trigger on the MSsubscription_agents
table and I check the status flag last_sync_status and call sp_start_job to
do some reporting following the push (I think 2 is successful).
Message posted via http://www.sqlmonster.com

Is there sp_helptext for tables

Hello Everybody, Please help me out:

Is there a system stored procedure for retrieving the sql statement that created a table.

I know i can use sp_helptext for views etc; i want the equivalent for tables.

sp_columns is not adequate either.

please help! thanks in advance;)sp_help will return all the columns of a table.|||sp_help will return all the columns of a table.
Hi Blindman,

I ran exec sp_help tblcustomers and i got:

Name: tblcustomers
Owner: dbo
Type: user table
Created_datetime: 4/18/2007 2:26:12 PM

Am i missing something??