[Table Partitioning] What is the best method?

Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Lewhy do you want to partition them in the first place? Just because they have millions of rows?

Are these tables transactional, or do you only read from them? If you are only reading, then you can add appropriate indexes without worrying about hurting insert/update/delete perf (because you don't do any inserts/updates/deletes) and don't partition them at all.

In my job I work with tables in sql server that have 100's of millions of rows without problems (not transactional though) - the important thing is to make sure the indexes are correct. also you need decent disks of course. :)|||Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Le

I have only tested functions/ schemes in 2005, but from my testing and reading I havent seen anything that would indicate 10 would be too many. Test it out. You need to realize that this whole concept/ feature is new to 2005, so you are blazing the path for the rest of us to follow. ;)

[SS2K5] : Dynamically construct a FROM statement with current SCHEMA

Hi,

I've got a DB with some schemas.

There's same tables under these schemas as for example :

MySchema1.MyTable1

MySchema2.MyTable1

I wrote CRUD stored procedures for these tables.

I'd like to dynamically create the FROM statement inside the stored procedures by getting the schema name from the current context.

Kind of :

FROM SCHEMA_NAME().MyTable1

So I'll have one generic stored procedure for MyTable1.

This stored proc will be under the DB level and will not have to be replicate under all the schemas.

Is it possible ?

Thanks for any help.

BERTRANDR

Sure, sp_executesql is such a utility to run dynamic sql. Basically you construct a SQL string(NVARCHAR) with parameters first, then use sp_executesql to execute it. Look at Book Online for details.|||Why do you want to use a generic SP? Using dynamic SQL has lot of issues - complexity, performance (it depends on the case), security risks, permissions management, debugging pain, ownership chaining issues in your case etc. You should create a SP in each schema that handles the modifications for each table. This is much more robust, simpler to manage, debug, code etc.|||

Well my point is that I don t want to duplicate objects that have the same purposes and do exactly the same things for the same table but under differents schemas.

I thought it was the best thing to do .

But maybe am I wrong ..

[SS2K5] : Dynamically construct a FROM statement with current SCHEMA

Hi,

I've got a DB with some schemas.

There's same tables under these schemas as for example :

MySchema1.MyTable1

MySchema2.MyTable1

I wrote CRUD stored procedures for these tables.

I'd like to dynamically create the FROM statement inside the stored procedures by getting the schema name from the current context.

Kind of :

FROM SCHEMA_NAME().MyTable1

So I'll have one generic stored procedure for MyTable1.

This stored proc will be under the DB level and will not have to be replicate under all the schemas.

Is it possible ?

Thanks for any help.

BERTRANDR

Sure, sp_executesql is such a utility to run dynamic sql. Basically you construct a SQL string(NVARCHAR) with parameters first, then use sp_executesql to execute it. Look at Book Online for details.|||Why do you want to use a generic SP? Using dynamic SQL has lot of issues - complexity, performance (it depends on the case), security risks, permissions management, debugging pain, ownership chaining issues in your case etc. You should create a SP in each schema that handles the modifications for each table. This is much more robust, simpler to manage, debug, code etc.|||

Well my point is that I don t want to duplicate objects that have the same purposes and do exactly the same things for the same table but under differents schemas.

I thought it was the best thing to do .

But maybe am I wrong ..

[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[SQL2000] permissions to use view based on tables from many databases

Hi
I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.
Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).
Regards,
Grzegorz
Ps. I had sent the post on microsoft.public.sqlserver.security, but I had no
answer.By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dteimk$1b7$1@.inews.gazeta.pl...
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and Operations
> (left join - customers without any operations). In the same database
> (Customers) I have created a stored procedure based on the view. Finally
> I'd like to give to some users permission only to exec the stored
> procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I had
> no answer.|||Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData', database 'Customers',
owner 'dbo'."
Grzegorz
Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> By default when you add a user they do not have any permissions to do
> anything. So just make sure you don't add them to any of the server or
> database roles. Then simply GRANT them execute permission on that sp.
> --
> Andrew J. Kelly SQL MVP|||Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on?
http://support.microsoft.com/?kbid=810474
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dtf589$5h3$1@.inews.gazeta.pl...
> Well, it works fine in situations when all tables are in the same
> database, but it doesn't work when tables are in two databases. If user
> have no rights to read source table from other database SQL Server shows
> error:
> "SELECT permission denied on object 'CustomersData', database 'Customers',
> owner 'dbo'."
> Grzegorz
>
> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Thanks, it works.
Grzegorz
Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:O07NN2uNGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Are the objects owned by the same owner in both db's? If so you may have
> to specify rights on the other tables. Is Cross database Ownership
> chaining turned on?
> http://support.microsoft.com/?kbid=810474
> --
> Andrew J. Kelly SQL MVP
>
> "Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
> news:dtf589$5h3$1@.inews.gazeta.pl...
>|||It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.
Grzegorz Danowski wrote:
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and
> Operations (left join - customers without any operations). In the same
> database (Customers) I have created a stored procedure based on the
> view. Finally I'd like to give to some users permission only to exec the
> stored procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I
> had no answer.

[SQL2000] permissions to use view based on tables from many databases

Hi

I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.

Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).

Regards,
Grzegorz

Ps. I had sent the post on microsoft.public.sqlserver.security, but I had no
answer.By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.

--
Andrew J. Kelly SQL MVP

"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dteimk$1b7$1@.inews.gazeta.pl...
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and Operations
> (left join - customers without any operations). In the same database
> (Customers) I have created a stored procedure based on the view. Finally
> I'd like to give to some users permission only to exec the stored
> procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I had
> no answer.|||Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData', database 'Customers',
owner 'dbo'."

Grzegorz

Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> By default when you add a user they do not have any permissions to do
> anything. So just make sure you don't add them to any of the server or
> database roles. Then simply GRANT them execute permission on that sp.
> --
> Andrew J. Kelly SQL MVP|||Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on?

http://support.microsoft.com/?kbid=810474

--
Andrew J. Kelly SQL MVP

"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dtf589$5h3$1@.inews.gazeta.pl...
> Well, it works fine in situations when all tables are in the same
> database, but it doesn't work when tables are in two databases. If user
> have no rights to read source table from other database SQL Server shows
> error:
> "SELECT permission denied on object 'CustomersData', database 'Customers',
> owner 'dbo'."
> Grzegorz
>
> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>> By default when you add a user they do not have any permissions to do
>> anything. So just make sure you don't add them to any of the server or
>> database roles. Then simply GRANT them execute permission on that sp.
>>
>> --
>> Andrew J. Kelly SQL MVP|||Thanks, it works.
Grzegorz

Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:O07NN2uNGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Are the objects owned by the same owner in both db's? If so you may have
> to specify rights on the other tables. Is Cross database Ownership
> chaining turned on?
> http://support.microsoft.com/?kbid=810474
> --
> Andrew J. Kelly SQL MVP
>
> "Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
> news:dtf589$5h3$1@.inews.gazeta.pl...
>> Well, it works fine in situations when all tables are in the same
>> database, but it doesn't work when tables are in two databases. If user
>> have no rights to read source table from other database SQL Server shows
>> error:
>> "SELECT permission denied on object 'CustomersData', database
>> 'Customers', owner 'dbo'."
>>
>> Grzegorz
>>
>>
>> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
>> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>>> By default when you add a user they do not have any permissions to do
>>> anything. So just make sure you don't add them to any of the server or
>>> database roles. Then simply GRANT them execute permission on that sp.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>|||It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.

Grzegorz Danowski wrote:
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and
> Operations (left join - customers without any operations). In the same
> database (Customers) I have created a stored procedure based on the
> view. Finally I'd like to give to some users permission only to exec the
> stored procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I
> had no answer.

[SQL] One parameter and few values.

Hello!

I have tables "forum_topics" and "forum_categories":

forum_topics:
-topic_id
-topic_title
-topic_cat_id
-topic_user_id
-topic_date

forum_categories:
-cat_id
-cat_name

For example: I have 5 categories in table forum_categories and by this query I can display topics from 2nd and 3rd category only. So I'm using this query:
SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (2;3)

Now I want to parametrized this query by one parameter, so:

SELECT topic_title FROM forum_topics WHERE topic_cat_id IN (@.param)

And I want to input this parameter lik in the query first - so @.param = "2;3"

But I have a problem to make this works, can someone explain me, how to solve this problem ? Thanks.

Czesc Grzesiek,

The quick and dirty way of doing it is to convert your SQL to a string and execute it later, this is useful when you have optional parameters. Your stor proc would look something like that:

Declare @.sqlas varchar(1000)SET @.sql ='SELECT topic_title FROM forum_topics WHERE topic_cat_id IN ('''+ @.param +''')'exec(@.sql)

|||

you want @.param to be comma separated value is that you are asking for ?

well you could do it in c# , vb.net or any language, could you tell me wht are you doing at the moment for that.

its simple just declare one string variable say str

string str = "";

if (str.length == 0)

str = some_value;

else

str += str +", " + some_other_value;

does that makes sense

regards,

satish

|||

Hey Tetris - you're from Poland maybe ? :)

I have:

ALTER PROCEDURE ShowTopics

@.param varchar
AS

Declare @.sql as varchar(max)

SET @.sql='SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN ('''+@.param+'''))'
exec(@.sql)RETURN

All as you wrote. But when I write as @.param "1,2,3" I'm getting only topics (records) where cat_id is "1". If I choose "3,2,1" I will get only from 3rd category.

|||

I wanna add only that this query:

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id IN (1,2,3)

Returns me all topics from category 1st,2nd and 3rd. So my question is simply - how can I put (and how declare, or I don't know) in place 1,2,3 a parameter @.categories, and how make it works ?

|||

Oh God ...

My mistake was at the begin of stored procedure. There was:

ALTER PROCEDURE ShowTopics
@.param varchar()

Should be
ALTER PROCEDURE ShowTopics
@.param varchar(100)

(for example)

No it works !!!! Thanks Tetris ! Big thanks !

|||I'm glad you got it to work :) Yeh I'm polish but I live in Canada.|||

So I have one more question.

I have 5 checkboxes:
Checkbox1 (Category 1st),Checkbox2 (Category 2nd),Checkbox3 (Category 3rd),Checkbox4 (Category 4th),Checkbox5 (Category 5th).

And as cookie "Forum_kategorie" I have value = "1,3,5".

So I have topics from category 1,3 and 5. Do you know how can I easily checked checkbox 1,3 and 5 and unchecked second and fourth ?

Default value of "@.param" in this parametrized query above will be 1,2,3,4,5. And at first enter to site all five checkboxes will be checked. I will unchecked second and fourth and click the button, which will add/change cookie "Forum_kategorie" to value "1,3,5" and reload site. After that I will see topics from 1,3,5 category, but I have in some way to define which checkbox is un/checked.

Any ideas ?

Dzi?ki za pomoc :-) / Thanks for help :-)

|||If you are re-loading the same page, as long as you have EnableViewState = True for each checkbox, it should remember what it's last selection was when the site re-loads (on post back) Is this the case?|||

As you have a small rabge of values, take a look at using bitwise operators.

Your check box values would be bases on multiples of two: 1,2,4,8,16.

The value of the parameter that you pass into your stored procedure would be the sum of the values of the check boxes.

SELECT forum_topics.topic_id, forum_topics.topic_title, forum_kategorie.forum_kat_nazwa, forum_kategorie.forum_kat_id
FROM forum_kategorie INNER JOIN
forum_topics ON forum_kategorie.forum_kat_id = forum_topics.topic_katId
WHERE (forum_kategorie.forum_kat_id & @.Categories = forum_kategorie.forum_kat_id)

[SQL Server 2005] Primary vs Secondary file-groups - deciding which table goes where

I created a set of primary and secondary data-files and organized them into file-groups. Now, I want to decide which tables to allocate to which file-group. What should be the factors on which I ought to decide this? Is there a significance of choosing the primary group over the secondary ones or vice versa? What is the impact of the choice in either case?filegroups in general are all about PERFORMANCE. you can gain significant performance gains if you leverage multiple disks with the multi-filegroup feature of sql server. there is no hard and fast but the general idea is to spread the workload evenly over the disks and to leverage faster disks/cache for the more intensive files|||

Thank you for your reply, Derek. Need one clarification though - would it be correct to say that a table created on a file-group gets evenly distributed across all the data-files in the file-group? In effect, what we are achieving is partitioning the table, right?

My earlier question was more about understanding how SQL Server determines which table belongs to which data-file and file-group. I suppose it might be part of metadata, which some DMV would be able to retrieve. I am not sure but, if this is indeed so, which is the DMV?

A related question that pops up is - should all (or most of the) tables that are most frequently accessed by allocated to the primary file-group? If SQL Server looks at the primary file-group first before scanning the others for the desired table-rows, it would make sense in allocating the most frequently accessed tables to the primary file-group.

[SQL SERVER 2005] Copy data from one database to another on same server

Ok, here is my dilemma
I have an application that has many sites. Each site has it's own database. The databases have common tables (ie the name and fields are the same) What I want to be able to do is when creating a new database, I want to be able to copy certain common table data from one database to another. I have run into an error because the table have an IDENTITY so this is not working

INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers

I also tried
USE ADMS2_RSCS
GO
SET IDENTITY_INSERT Containers ON
GO
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
GO
SET IDENTITY_INSERT Containers OFF
GO

I got an error saying that I need to have a column list. I am trying to use this for any tables, so my question is this..
Is there any way to get around using a column list or is there a way to dynamically create the column list?
Or, Is there a better way that I should be doing this?

Please keep in mind I am not a dba and everything I have learned about SQL is from my good pal Google :)

Thanks for any helpWhy can't you just write out the column list?

using select * in anything but throw away code is a no-no.|||just write the column list as jezemine suggests
OR
use "select into" instead of "insert into". remember the table will be created fresh (should not already exists) and will be created without index etc.
OR
create necessary common tables with data, index everything in "model" database. every new database u create thereafter will have all those objects ready immediately after creation. no sql needed.|||If your server is dedicated to these databases, then I would suggest copying all of the objects and data that you need to the Model database. After that, every database you create will be a copy of model and will have everything you need.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegroups.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegroups.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.
SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegrou ps.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[Microsoft][ODBC Sql Server Driver][Sql Server]Line1: Incorrect Sy

Hi,
I am using SQL server 2000. One of the tables in it has become really large
so i tried to use a sql query to view only a certain rows. I used the
following query:
SELECT * FROM TABLE WHERE Company= X
When i verified the syntax , it verified correctly. Buy when i tried to run
the query i got the error msg box :: [Microsoft][ODBC Sql Server Driver][Sql
Server]Line1: Invalid column name X .
But a column with the company value as X exists!
Also when i tried to use a little different query:: SELECT * FROM TABLE
WHERE Company LIKE s % , then i get the same error but the end part of the
error message says Incorrect Syntax Near %
Why is this happening?
pmud
It's a string, you need string delimiters.
WHERE Company='X'
http://www.aspfaq.com/
(Reverse address to reply.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud
|||you need single quotes around all character fields
SELECT * FROM TABLE WHERE Company= 'X'
SELECT * FROM TABLE WHERE Company LIKE 's %'
-Andre
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
> large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
> run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
> Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
> the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud

[Microsoft][ODBC Sql Server Driver][Sql Server]Line1: Incorrect Sy

Hi,
I am using SQL server 2000. One of the tables in it has become really large
so i tried to use a sql query to view only a certain rows. I used the
following query:
SELECT * FROM TABLE WHERE Company= X
When i verified the syntax , it verified correctly. Buy when i tried to run
the query i got the error msg box :: [Microsoft][ODBC Sql Server Driver][Sql
Server]Line1: Invalid column name X .
But a column with the company value as X exists!
Also when i tried to use a little different query:: SELECT * FROM TABLE
WHERE Company LIKE s % , then i get the same error but the end part of the
error message says Incorrect Syntax Near %
Why is this happening?
--
pmudIt's a string, you need string delimiters.
WHERE Company='X'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud|||you need single quotes around all character fields
SELECT * FROM TABLE WHERE Company= 'X'
SELECT * FROM TABLE WHERE Company LIKE 's %'
-Andre
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
> large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
> run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
> Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
> the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud|||Thanks I was trying to use double quotes instead of single...
Thanks :))
"Andre Chan" wrote:
> you need single quotes around all character fields
> SELECT * FROM TABLE WHERE Company= 'X'
> SELECT * FROM TABLE WHERE Company LIKE 's %'
> -Andre
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> > Hi,
> >
> > I am using SQL server 2000. One of the tables in it has become really
> > large
> > so i tried to use a sql query to view only a certain rows. I used the
> > following query:
> >
> > SELECT * FROM TABLE WHERE Company= X
> >
> > When i verified the syntax , it verified correctly. Buy when i tried to
> > run
> > the query i got the error msg box :: [Microsoft][ODBC Sql Server
> > Driver][Sql
> > Server]Line1: Invalid column name X .
> >
> > But a column with the company value as X exists!
> >
> > Also when i tried to use a little different query:: SELECT * FROM TABLE
> > WHERE Company LIKE s % , then i get the same error but the end part of
> > the
> > error message says Incorrect Syntax Near %
> >
> > Why is this happening?
> > --
> > pmud
>
>

[Microsoft][ODBC Sql Server Driver][Sql Server]Line1: Incorrect Sy

Hi,
I am using SQL server 2000. One of the tables in it has become really large
so i tried to use a sql query to view only a certain rows. I used the
following query:
SELECT * FROM TABLE WHERE Company= X
When i verified the syntax , it verified correctly. Buy when i tried to run
the query i got the error msg box :: [Microsoft][ODBC Sql Server Dr
iver][Sql
Server]Line1: Invalid column name X .
But a column with the company value as X exists!
Also when i tried to use a little different query:: SELECT * FROM TABLE
WHERE Company LIKE s % , then i get the same error but the end part of the
error message says Incorrect Syntax Near %
Why is this happening?
--
pmudIt's a string, you need string delimiters.
WHERE Company='X'
http://www.aspfaq.com/
(Reverse address to reply.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud|||you need single quotes around all character fields
SELECT * FROM TABLE WHERE Company= 'X'
SELECT * FROM TABLE WHERE Company LIKE 's %'
-Andre
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A17DE491-DF25-47D0-BBA4-F84A2FE6602A@.microsoft.com...
> Hi,
> I am using SQL server 2000. One of the tables in it has become really
> large
> so i tried to use a sql query to view only a certain rows. I used the
> following query:
> SELECT * FROM TABLE WHERE Company= X
> When i verified the syntax , it verified correctly. Buy when i tried to
> run
> the query i got the error msg box :: [Microsoft][ODBC Sql Server
> Driver][Sql
> Server]Line1: Invalid column name X .
> But a column with the company value as X exists!
> Also when i tried to use a little different query:: SELECT * FROM TABLE
> WHERE Company LIKE s % , then i get the same error but the end part of
> the
> error message says Incorrect Syntax Near %
> Why is this happening?
> --
> pmud

[help] Possible to script a diff into SQL Query?

My goal is to add a diff into a query that grabs data from 2 different tables.

The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769

UNION all

SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp

GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1

Is it possible to add a command into the query to output diff/compare scenario?

Thanks in advance for any help.

If you are using SQL Server 2005 you might be able to use the EXCEPT operator instead of the UNION ALL operator. Give a look to the EXCEPT operator in books online.

If the EXCEPT operator looks like it does what you want and it does not perform to your satisfaction, come back and talk to us again. While the EXCEPT operator might be conceptually the most straight forward approach it is not always the most efficient.

Kent

|||Yes, we're using 2005. I'll look into using the EXCEPT op. thanks for the advise, Kent.

[HELP!] whats the safe way to export tables(only Data) to file?

Hi:
I'm want to export some tables(only data) to file and import it to another DB. Does anybody know that what 's the most safe way to do it? Because I have some binary data in these tables, I don't think I can export it to .txt or .csv format.You can use the BCP command line utility to import and export image and text data. You can also use DTS, but in DTS there is an upper limit to the image field size.

blindman

[DB2] Update Field in Table A to a Field in Table B

I am trying to change the values in Field2 of table A to the values in Field2 of table B wherever the tables have the same value for Field1.

This works in Microsoft Access, but I can't figure out how to make it work in DB2.

UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];

Any ideas?I know this works in Oracle, but not tested in DB2...

UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,

Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:

update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )

...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.|||thanks, joe, i understand that

i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity

i'd use a WHERE clause to ensure that only those rows which had a match are actually updated|||Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.

[CR6] Can I use "SELECT FROM WHERE"?

Hi, sorry for my english :-\

I'm working on Crystal Report 6.0
I have 2 tables that are not in join, but I need a field with a "where" condition.
Example:
TableA:
fldDescription

TableB:
fldCode
fldDesc

I have a formula field which should show TableB.fldDesc, using a code in TableA.fldDescription
I extract 2 char in TableA.fldDescription:
strMyField = Trim (Mid ({TableA.fldDescription},2 , 2));

Now I must show TableB.fldDesc where TableB.fldCode = strMyField.
How can I do this? I have no join between this tables, I tried to put in the formula field "Select TableB.fldDesc where TableB.fldCode = strMyField" but it doesn't work.

Could you help me, please?How are TableA and TableB related? Do you even need any data from TableA?

"Select TableB.fldDesc where TableB.fldCode = strMyField"

If this were VB, you would have to do it like this:

"Select TableB.fldDesc where TableB.fldCode = '" & strMyField & "'"

In other words, you would have to concatenate the variable into the string you're using for your select statement. Also, you would need to put the string in single quotes (as shown in Red) to tell your database that it's a string. (These examples are derived from VB 6 and SQL Server 7, so you may need to adjust them accordingly).

I don't use Crystal Reports for direct database access. I let VB do all the data gathering and I just pass the data itself to Crystal for displaying. I'm not sure what the correct syntax is for Database SQL Statement within Crystal Reports, but I hope that my examples might give you a starting point to figure out your problem.

Good Luck!

@local_var really cannot hold table name ?

Hi,
I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
from year 1997, 1998 and a027 is just specification of product type. I
need carry out repeated operataions over these tables. To generate
their names is easy, for example

use [P5-01]
declare @.Yint int, @.Y char(2), @.tabName char(8)
set @.Yint = 1997
while @.Yint < 2002
begin
set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
print @.tabName
set @.Yint = @.Yint + 1
/*
select *
from @.tabName
*/
end

prints properly

ag97a027
ag98a027
ag99a027
ag00a027
ag01a027

but when I uncomment the select statement, MS-SQL server responds:
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@.tabName'.

How to use @.local_variable for browsing through tables in above
described way?

thanks
MartinOn 23 May 2004 23:10:43 -0700, Martin Mrazek wrote:

>Hi,
>I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
>from year 1997, 1998 and a027 is just specification of product type. I
>need carry out repeated operataions over these tables. To generate
>their names is easy, for example
>use [P5-01]
>declare @.Yint int, @.Y char(2), @.tabName char(8)
>set @.Yint = 1997
>while @.Yint < 2002
>begin
> set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
> print @.tabName
> set @.Yint = @.Yint + 1
>/*
> select *
> from @.tabName
>*/
>end
>prints properly
>ag97a027
>ag98a027
>ag99a027
>ag00a027
>ag01a027
>but when I uncomment the select statement, MS-SQL server responds:
>Server: Msg 137, Level 15, State 2, Line 11
>Must declare the variable '@.tabName'.
>How to use @.local_variable for browsing through tables in above
>described way?
>thanks
>Martin

Hi Martin,

The error message is caused because SQL Server accepts either a literal
table name or a table variable in the from clause. The @. denotes the next
thing as a variable, so SQL Server will try to find a table variable
called @.tabName, which it can't find.

The best answer to your question is: change your table design. Generally
speaking, 99 out of 100 cases where each year's data is stored in a
seperate table are better off storing everything in one table (adding a
"year" column if there isn't already a "date" column that can serve this
purpose), or using a two table design (one "current" table and one
"history" table), again adding a "year" column if needed.

If you feel you must do it this way, google this newsgroup for "dynamic
SQL". That's the kludge that will do what you're trying to achieve.

(I assume that the above SQL is used only to illustrate your problem and
that you're not actually using "select *" in production code or printing
all data in all tables - who would ever want to read that much output??).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||mrazek@.compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dcd39e84.0405232210.5a75aeb6@.posting.google.com>...
> Hi,
> I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
> from year 1997, 1998 and a027 is just specification of product type. I
> need carry out repeated operataions over these tables. To generate
> their names is easy, for example
> use [P5-01]
> declare @.Yint int, @.Y char(2), @.tabName char(8)
> set @.Yint = 1997
> while @.Yint < 2002
> begin
> set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
> print @.tabName
> set @.Yint = @.Yint + 1
> /*
> select *
> from @.tabName
> */
> end
> prints properly
> ag97a027
> ag98a027
> ag99a027
> ag00a027
> ag01a027
> but when I uncomment the select statement, MS-SQL server responds:
> Server: Msg 137, Level 15, State 2, Line 11
> Must declare the variable '@.tabName'.
> How to use @.local_variable for browsing through tables in above
> described way?
> thanks
> Martin

You can use dynamic SQL for this (see below), but read this first to
understand why you probably shouldn't:

http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

Simon

declare @.tblname sysname
set @.tblname = 'MyTable'
exec('select * from ' + @.tblname)|||Martin Mrazek (mrazek@.compik.fd.cvut.cz) writes:
> How to use @.local_variable for browsing through tables in above
> described way?

The answer is you don't. Well, you can if you go over hops of dynamic
SQL, but you should really only have one table, with year and product
types as keys in your one single table.

If you have some application which really requires all these tables,
then set up a view over all the tables, with year and product type
added:

CREATE VIEW all_my_tables (year, product_type, col1, col2, col3, ...) AS
SELECT '1998', 'a027', col1, col2, col3,
FROM ag98a027
UNION ALL
SELECT '1999', 'a027', col1, col2, col3,
FROM ag99a027
UNION ALL
...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp