Create Table Script Incorrect syntax near 'Collate'

HI.

I am using SQL Server 2000. I generate a script of some table from EmpDB database when I run script in query analyzer it return error "Incorrect syntax near 'COLLATE'."

Scripts is

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

CREATE TABLE [dbo].[Emp] (
[EmpID] [int] NOT NULL ,
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

************************************************** ****
When I remove " COLLATE SQL_Latin1_General_CP1_CI_AS" from script manually then it run successfully.

Please guide why it is happening and how to escape from error.

Thanx in advance.I have tried your code and it works on my PC.

Try creating the table without the Collation and then use entreprice manager to assign the spicfic collation to the field. Does the specific calation exist?sql

Create table script

Hi all,
Often when I post a query problem the replies ask that a create
table script is provided. Is there an easy way to do this on SQL 2000?
What if I don't want to include a whole table only certain fields?
Any pointers would be gratefully received.
M

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can generate a CREATE TABLE script automatically in Query Analyzer
(right-click on the table in the Object Browser) or in Enterprise Manager
(right-click on the database. All Tasks > Generate SQL Script).

Ideally, edit the script to just the columns relevant to your problem but
always include the primary key for each table. Include the foreign keys if
there's more than one table involved. Also include any check, unique and
nullability constraints on the relevant columns.

Often it helps if you can construct some sample data (post as INSERT
statements) to illustrate your problem and show the result you require based
on that sample data. Oh, and do test your script out before you post it :-)

See also: www.aspfaq.com/5006

--
David Portas
----
Please reply only to the newsgroup
--

Create Table Question

Hi

Im wondering if someone could help me out with how to write sql for the following type of query.

I have 3 known strings of characters and three associated sql queries. The queries will always return an integer.

I want a table so that column 1 is the list of known strings, and column 2 is the results of the three queries.

Thank youcreate table mytable
( string varchar(100)
, result integer
)|||Or maybe something like this:
create table mytable
( string varchar(100)
, result integer)
AS
select string, SUM(result) from (
select string, result from query1
UNION ALL
select string, result from query2
UNION ALL
select string, result from query3)
group by string;
:D

create table permission

Hi,
Is there any query available to check the existence of 'CREATE TABLE' permission in a database
?
Please advice,
Thanks,
MiraJI really need to get my hands on an SQL Server ER Diagram...but start with syspermissions

Create table from web application

i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table.

this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page.

How can i permit my sql server to allow creation of tables from my web application.

Thanks

It all depends on the type of authentication that you are using for the app. The users of your web app will have to have an account capable of creating tables or whatever else you want them to do. What ever it is make an user account just for them. Please do not give the the sa account or you may be sorry later.|||

i want to know how i can authenticate the user to create the table from the server. I know i have to authenticate the user but what it is procedure to do that. I want to know how i can achieve that in Sql 2000

Thank you

|||Ok, What you can do now is in your connection string use the user and pass that the end user supplies. This will need to be a username and pass that you have supplied to them that is valid for the database. When the user logs in just add their user and pass to session so that you can call it back whenever you need it. Then when you create a connection for that user in your connection string use (string)Session["UserName"] and (string)Session["Password"] for the user and pass. To add their user and pass to the Session just use Session.Add("UserName",txtThetextboxthattheyentertheirusernamein.Text); and Session.Add("Password",txtThetextboxthattheyenteredtheirpasswordin.Text); I would put my connection properties in a class so that you can call them whenever and make your method so that it will accept the user and pass that you need to through it. Like this:
[pre]Public bool DBConnection(string Username,String Password)
{
bool connected = false;
Try
{
your connection params using the strings UserName and Password
connected = true;
}
Catch
{
connected = false;
}
Return connected;
}[/pre]
Then you can check the return value to make sure that they connected and use the connection for whatever you want to process. To call it up just type DBConnection((string)Session["UserName"],(string)Session["Password"])
if you need to check it set the return value to a bool and check for true or false
[pre]
bool didconnect = DBConnection((string)Session["UserName"],(string)Session["Password"])
if (didconnect != false)
{
Whatever stuff you need to run against the db;
}
else
{
Errormessage to the user;
}
[/pre]sql

create table from query results

Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1

ID col1 col2

1 a a
2 b d
3 c f

this would be my new table named table2

ID col1 col2 col3

1 a a aa
2 b d bd
3 c f cf

this new table has an additional column by concatenating col1+col2
tried this procedure but is not working

CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2

thanksare you sure you need to store the concatenation in a separate table?SELECT ID, COL1, COL2, COL1+COL2 AS COL3
INTO TABLE2
FROM TABLE1|||thanks Rudy

yes I'll be storing the concatenated field in a new table

will that procedure create automatically the table2 even if i don't use CREATE TABLE?

alex

Create table as I need it, how to reference in query?

I'll try to use the customer/orders example for my situation.
Let's say I have 10 customers and each customer has n order records. Most
of the time I will just accept the default order of the order records. In
other words, when I SELECT a customers orders I get them back in the order
they are in the table.
In some cases (remember, I'm not really dealing with customers/orders) I
want to explicitly re-order a customer's order records. Not by any
particular field. There is no logical pattern.
I was thinking that I could maintain a Customer_Orders_Order table. It
would look something like this:
[ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK to
Order tbl) ]
the values might look like:
1 1 2
1 2 5
1 3 8
1 4 9
2 1 2
2 2 4
2 3 3
2 4 1
2 5 5
I would use this table to display the orders in the desired order in the UI.
OrderIndex would be the index in a ListView or DataGrid, etc.
My question is, would you store all the information in a single table or
would you create a table per customer to store this information and only if
the Customer's orders have actually been reordered from their default
presentation order (row order in the table)?
If your answer is create a table for each customer, how do you work with a
dynamically created table in a sproc or query? If my client app detects
that a user dragged an order above another order thus changing the
presentation order, I would create a table with a unique name, maybe
Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
ordering information. Now, later when I want to display this customer's
orders and want to check if there is explicit presentation ordering that I
should use, how do I query the table I just created? Assuming I know the
naming convention used, can you concatenate a string in a sproc to build a
table name to be used in a Query?
Man, I hope this makes sense to someone, it's hard to explain.. :)
Thanks for any help,
SteveI was just thinking about this and realized that updates and inserts would
be involved due to the quantity of item in the update/insert. This got me
thinking... If I store this data per customer, inside the customer record,
then I don't need to have anything other than an array of OrderIDs.
So I can just have a delimited string stored in a field called
"OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" but
sticking with the example....)
something like:
4;2;7;1;3;5
Insert and updates are easy (fast) and there really is no need to normalize
data like this, is there? I won't use it for searches or anything.
If anyone has a compelling reason why I shouldn't do this, please share,
please, please.
"Steve" <sss@.sss.com> wrote in message
news:uThe317QGHA.6084@.TK2MSFTNGP10.phx.gbl...
> I'll try to use the customer/orders example for my situation.
> Let's say I have 10 customers and each customer has n order records. Most
> of the time I will just accept the default order of the order records. In
> other words, when I SELECT a customers orders I get them back in the order
> they are in the table.
> In some cases (remember, I'm not really dealing with customers/orders) I
> want to explicitly re-order a customer's order records. Not by any
> particular field. There is no logical pattern.
> I was thinking that I could maintain a Customer_Orders_Order table. It
> would look something like this:
> [ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK
> to Order tbl) ]
>
> the values might look like:
> 1 1 2
> 1 2 5
> 1 3 8
> 1 4 9
> 2 1 2
> 2 2 4
> 2 3 3
> 2 4 1
> 2 5 5
> I would use this table to display the orders in the desired order in the
> UI. OrderIndex would be the index in a ListView or DataGrid, etc.
> My question is, would you store all the information in a single table or
> would you create a table per customer to store this information and only
> if the Customer's orders have actually been reordered from their default
> presentation order (row order in the table)?
> If your answer is create a table for each customer, how do you work with a
> dynamically created table in a sproc or query? If my client app detects
> that a user dragged an order above another order thus changing the
> presentation order, I would create a table with a unique name, maybe
> Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
> ordering information. Now, later when I want to display this customer's
> orders and want to check if there is explicit presentation ordering that I
> should use, how do I query the table I just created? Assuming I know the
> naming convention used, can you concatenate a string in a sproc to build a
> table name to be used in a Query?
> Man, I hope this makes sense to someone, it's hard to explain.. :)
> Thanks for any help,
> Steve
>|||I have a feeling you're about to feel the wrath of JC.
But to answer your question, you would create a single table for this.
I see what you mean by your sorting information. In that case, I
consider that type of data to not have anything to do with the database
-- while it's human readable and client readable, I consider the data
to be arbitrary to anything accessing it within the database. It might
as well be random binary data for all your database is concerned. Now,
the question is, does this data belong in your database? You're
breaking encapsulation by storing this in your database if it's not
specific to the data. I make two exceptions to putting
application-specific data within a database. The first is when the
database is used by one and only one application, and will be used by
one and only one application for the purpose of storing data and logic
specific to that application. The second is when the performance or
implementation advantages of storing such application-specific logic
and data are exceptionally significant.
-Alan|||Steve wrote:
> I was just thinking about this and realized that updates and inserts would
> be involved due to the quantity of item in the update/insert. This got me
> thinking... If I store this data per customer, inside the customer record
,
> then I don't need to have anything other than an array of OrderIDs.
> So I can just have a delimited string stored in a field called
> "OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" b
ut
> sticking with the example....)
> something like:
> 4;2;7;1;3;5
> Insert and updates are easy (fast) and there really is no need to normaliz
e
> data like this, is there? I won't use it for searches or anything.
> If anyone has a compelling reason why I shouldn't do this, please share,
> please, please.
>
What reason would you have for NOT normalizing in this case?
You seem to be making something very simple into something very
complex. No question in my mind: One table in Normal Form. The keys
would be I assume (customer, order_index) and (customer, order_id).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've upset JC?
(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
Yes, the data does belong to the database. I'll tell you why; given my
example, it sounds like an app preference or user preference, but this
ordering is actually used to generate source code. If a user logs in at
location A and initiates a build without knowing the ordering of the
records, that would result in invalid firmware and an irritated customer.
This is a feature I should have added from the get go, but I overlooked it.
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string?
If not, make your case. I want to learn the right way.
Thanks,
Steve
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141943305.488882.121840@.u72g2000cwu.googlegroups.com...
>I have a feeling you're about to feel the wrath of JC.
> But to answer your question, you would create a single table for this.
> I see what you mean by your sorting information. In that case, I
> consider that type of data to not have anything to do with the database
> -- while it's human readable and client readable, I consider the data
> to be arbitrary to anything accessing it within the database. It might
> as well be random binary data for all your database is concerned. Now,
> the question is, does this data belong in your database? You're
> breaking encapsulation by storing this in your database if it's not
> specific to the data. I make two exceptions to putting
> application-specific data within a database. The first is when the
> database is used by one and only one application, and will be used by
> one and only one application for the purpose of storing data and logic
> specific to that application. The second is when the performance or
> implementation advantages of storing such application-specific logic
> and data are exceptionally significant.
> -Alan
>|||>> So I can just have a delimited string stored in a field [sic] called"OrderOrde
ring" (stupid name, my actual name would be "ProtocolOrdering" but sticking
with the example....) something like:4;2;7;1;3;5 <<
Once more, you missed the most basic concepts again! Look up "First
Normal Form" (1NF), and learn why a column is not a field, a row is not
a record and a table is not a file. You might want to read at least
one book on RDBMS before you code in SQL.|||>I've upset JC?
>(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
I think you've probably figured this out by now.
So, you've decided to keep this in your database. Be aware of the
possible future implications of this. If you store multiple values a
single row, those values will be utterly useless in your database and
you'll be forced to do your sorting on the client. Next, if you dump
your front-end and it's rewritten, you'll have this artifact in your
database that those inheriting your work won't know what to do with
(or, given enough time, you won't either). In this situation, where
you've decided to store application-specific data in your database, at
least make it friendly to whoever may be administering thing. Store
your application-specific data in an application-specific location --
either a "helper" database that is specific to your application, or in
tables that are clearly indicated as being specific to your application
(e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
dropped without implications once your application is no longer used.
-Alan|||>> 've upset JC? (who is JC? Like Jesus Christ JC or is there a NG regular
named JC?) <<
This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
us all the time.
hout knowing the ordering of the records [sic], that would result in invalid firm
ware and an irritated customer. <<
So the ordering has logical meanng in the data model!
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string? <<
NO, it is a valid reason for a sequencing column in First Normal Form.
Since you will not post any DDL or even helpful narrative with
meaningful names, here is a guess:
CREATE TABLE FirmwareTemplates
(customer_id INTEGER NOT NULL
REFERENCES Custromers (customer_id),
template_seq INTEGER NOT NULL,
template_txt VARCHAR (255) NOT NULL,
PRIMARY KEY (customer_id, template_seq ));|||LOL.. let the NG lashings begin!
My entire database is application specific to a single app. It's entire
reason for existence is my application. It would seem in that case that ALL
my data is application specific. Is that bad? I'm getting the distinct
feeling that is somehow a bad thing?
Either way, yes, I am doing the sorting on the client. This is intentional.
My application is a high level firmware editor for our sales people. We
sell a product that can be customized (a bit) per customer. However, ALL
device's firmware must share some data that is constant.
One of the things that can be customized is the ordering of menu items in a
UI menu. All devices have the same menu items, but some customers want them
in a different order. I have approached this by maintaing a base shared
collection of menu items that all customers use. When they want to reorder
the items I store "meta data" (I'm not even sure if I understand that term,
seems like "settings" to me, but whatever) in their record.
Point is, I don't return a separate set of ordered menu item records for
each customer, I get the menu items for all customers, then the ordering
data for each customer, then on the client each customer has a reference to
the shared items and I order them the way they want in the UI.
Hope that makes a bit of sense.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141944627.875280.107100@.i40g2000cwc.googlegroups.com...
> I think you've probably figured this out by now.
> So, you've decided to keep this in your database. Be aware of the
> possible future implications of this. If you store multiple values a
> single row, those values will be utterly useless in your database and
> you'll be forced to do your sorting on the client. Next, if you dump
> your front-end and it's rewritten, you'll have this artifact in your
> database that those inheriting your work won't know what to do with
> (or, given enough time, you won't either). In this situation, where
> you've decided to store application-specific data in your database, at
> least make it friendly to whoever may be administering thing. Store
> your application-specific data in an application-specific location --
> either a "helper" database that is specific to your application, or in
> tables that are clearly indicated as being specific to your application
> (e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
> dropped without implications once your application is no longer used.
> -Alan
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141944759.875765.197440@.e56g2000cwe.googlegroups.com...
> This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
> us all the time.

>
> So the ordering has logical meanng in the data model!
yes, yes it does.

>
> So moving forward and agreeing (I assume we agree?) that the data does
> belong in the DB, is this a valid case for a delimited string? <<
> NO, it is a valid reason for a sequencing column in First Normal Form.
> Since you will not post any DDL or even helpful narrative with
> meaningful names, here is a guess:

> CREATE TABLE FirmwareTemplates
> (customer_id INTEGER NOT NULL
> REFERENCES Custromers (customer_id),
> template_seq INTEGER NOT NULL,
> template_txt VARCHAR (255) NOT NULL,
> PRIMARY KEY (customer_id, template_seq ));
>
Gotcha, I will investigate this, thanks for the suggestion!

CREATE TABLE and CONSTRAINTS

well very new to sql,as I am...
I am posting this query,please explain me what it is,

Code: ( text )

1. CREATE TABLE [dbo].[DOORD_ORDER] (

2. [ORDER_NBR] [int] NOT NULL ,

3. [TOTAL_SEQ_NBR] [int] NOT NULL ,

4. [DELIVERY_IND] [char] (1) NOT NULL ,

5. [CONTACT_LST_NME] [varchar] (30) NOT NULL ,

6. [CONTACT_FRST_NME] [varchar] (40) NOT NULL ,

7. [CONTACT_TYP] [varchar] (30) NOT NULL ,

8. [CONTACT_TXT] [varchar] (100) NOT NULL ,

9. [OR_CDE] [char] (1) NOT NULL ,

10. [DROPOFF_DTE] [datetime] NOT NULL ,

11. [PICKUP_DTE] [datetime] NOT NULL ,

12. [LST_OPR_ID] [char] (8) NOT NULL ,

13. [LST_MNT_TSMP] [datetime] NOT NULL ,

14. [WAIT_INSTORE_IND] [char] (1) NOT NULL

15. ) ON [PRIMARY]

16. GO

17. ALTER TABLE [dbo].[DOORD_ORDER] WITH NOCHECK ADD

18. CONSTRAINT [PK_DOORD] PRIMARY KEY CLUSTERED

19. (

20. [ORDER_NBR]

21. ) WITH FILLFACTOR = 90 ON [PRIMARY]

22. GO

Hirak

This query creates a new table called [dbo].[DOORD_ORDER] and then adds a constraint to the table in this case designates the primary key [ORDER_NBR].

Mary|||well mary,is this oracle?

we didnt do like this in oracle,right?
please tell me what type of db query is this
hirak

Quote:

Originally Posted by mmccarthy

Hirak

This query creates a new table called [dbo].[DOORD_ORDER] and then adds a constraint to the table in this case designates the primary key [ORDER_NBR].

Mary

|||

Quote:

Originally Posted by hirak1984

well mary,is this oracle?

we didnt do like this in oracle,right?
please tell me what type of db query is this
hirak

Actually it does look like Oracle to me. It's been a while since I've used it though so I could be wrong. It could also be SQL Server. Sorry Hirak I haven't used either in a few years and they all just blend together now.

Mary|||you need not be sorry mary because you are right.

this is a sqlserver query I found out now.
I am sorry to post it in oracle forum,because then I didnt have an idea,what it was.
I dont have necessary privileges,please transfer it to the sqlserver forum

Quote:

Originally Posted by mmccarthy

Actually it does look like Oracle to me. It's been a while since I've used it though so I could be wrong. It could also be SQL Server. Sorry Hirak I haven't used either in a few years and they all just blend together now.

Mary

Create sequential numbers in a column

I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0

There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:

INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent

If this does answer your question, please provide the following:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.

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

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

Create query from table SS2K vs. SS2005

Hello,
When using SS2K, you can create a query by right-mouse click on the table
and then selecting "query" in the Enterprise Manager. How do you do this in
SS2005 when using the Management Studio? When I right-mouse click on the
table, the only option I have is to select "Open", which runs the whole
dataset.
Thanks in advance,
StevenHi Steven,
If you're just looking to add a filter before running the query, then
when you right click on the table, you can select
Script Table As > SELECT to > New Query Editor Window | Clipboard |
File
Hopefully that's close to what you had in mind.|||If it is the query builder you are after, you can reach it from the view fol
der. Right-click, new
view, construct the query and you then don't have to save it as a view. Pers
onally, I don't
appreciate query builders, though. I find them too limiting and they don't a
llow me to expand on my
SQL knowledge.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steven K0" <stroy@.api.com> wrote in message news:eDpZyG2LGHA.2316@.TK2MSFTNGP11.phx.gbl...[v
bcol=seagreen]
> Hello,
> When using SS2K, you can create a query by right-mouse click on the table
and then selecting
> "query" in the Enterprise Manager. How do you do this in SS2005 when usin
g the Management Studio?
> When I right-mouse click on the table, the only option I have is to select
"Open", which runs the
> whole dataset.
> --
> Thanks in advance,
> Steven
>[/vbcol]|||
> Personally, I don't appreciate query builders, though.
> I find them too limiting and they don't allow me to
> expand on my SQL knowledge.
Dear Tibor,
I beg to differ with you about limited functionality of query builders.
Recently I've found one that differs from the rest. Active Query Builder
can build queries with unions and sub-queries. I can't find nothing in
it that could be called 'limitation', this is just a useful addition to
the text editor. And it keeps my queries untouched even it has no
appropriate visual control for some of their clauses.
Sincerely
Sergey Smagin
*** Sent via Developersdex http://www.codecomments.com ***|||> I beg to differ with you about limited functionality of query builders.
Sorry, I should have said that I find the Query builder that comes with Ente
rprise Manager and
Visual Studio limiting. These are the only one I've tried (apart from the on
e found in Excel a long
time ago). I can imagine that there are other QBs out there that are more po
werful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Simnard" <nospam@.devdex.com> wrote in message news:uII9tUMMGHA.1532@.TK2MSFTNGP12.phx.gb
l...
>
> Dear Tibor,
> I beg to differ with you about limited functionality of query builders.
> Recently I've found one that differs from the rest. Active Query Builder
> can build queries with unions and sub-queries. I can't find nothing in
> it that could be called 'limitation', this is just a useful addition to
> the text editor. And it keeps my queries untouched even it has no
> appropriate visual control for some of their clauses.
> Sincerely
> Sergey Smagin
> *** Sent via Developersdex http://www.codecomments.com ***

Create query from table SS2K vs. SS2005

Hello,
When using SS2K, you can create a query by right-mouse click on the table
and then selecting "query" in the Enterprise Manager. How do you do this in
SS2005 when using the Management Studio? When I right-mouse click on the
table, the only option I have is to select "Open", which runs the whole
dataset.
Thanks in advance,
Steven
Hi Steven,
If you're just looking to add a filter before running the query, then
when you right click on the table, you can select
Script Table As > SELECT to > New Query Editor Window | Clipboard |
File
Hopefully that's close to what you had in mind.
|||If it is the query builder you are after, you can reach it from the view folder. Right-click, new
view, construct the query and you then don't have to save it as a view. Personally, I don't
appreciate query builders, though. I find them too limiting and they don't allow me to expand on my
SQL knowledge.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steven K0" <stroy@.api.com> wrote in message news:eDpZyG2LGHA.2316@.TK2MSFTNGP11.phx.gbl...
> Hello,
> When using SS2K, you can create a query by right-mouse click on the table and then selecting
> "query" in the Enterprise Manager. How do you do this in SS2005 when using the Management Studio?
> When I right-mouse click on the table, the only option I have is to select "Open", which runs the
> whole dataset.
> --
> Thanks in advance,
> Steven
>
|||
> Personally, I don't appreciate query builders, though.
> I find them too limiting and they don't allow me to
> expand on my SQL knowledge.
Dear Tibor,
I beg to differ with you about limited functionality of query builders.
Recently I've found one that differs from the rest. Active Query Builder
can build queries with unions and sub-queries. I can't find nothing in
it that could be called 'limitation', this is just a useful addition to
the text editor. And it keeps my queries untouched even it has no
appropriate visual control for some of their clauses.
Sincerely
Sergey Smagin
*** Sent via Developersdex http://www.codecomments.com ***
|||> I beg to differ with you about limited functionality of query builders.
Sorry, I should have said that I find the Query builder that comes with Enterprise Manager and
Visual Studio limiting. These are the only one I've tried (apart from the one found in Excel a long
time ago). I can imagine that there are other QBs out there that are more powerful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Simnard" <nospam@.devdex.com> wrote in message news:uII9tUMMGHA.1532@.TK2MSFTNGP12.phx.gbl...
>
> Dear Tibor,
> I beg to differ with you about limited functionality of query builders.
> Recently I've found one that differs from the rest. Active Query Builder
> can build queries with unions and sub-queries. I can't find nothing in
> it that could be called 'limitation', this is just a useful addition to
> the text editor. And it keeps my queries untouched even it has no
> appropriate visual control for some of their clauses.
> Sincerely
> Sergey Smagin
> *** Sent via Developersdex http://www.codecomments.com ***

Create query from table SS2K vs. SS2005

Hello,
When using SS2K, you can create a query by right-mouse click on the table
and then selecting "query" in the Enterprise Manager. How do you do this in
SS2005 when using the Management Studio? When I right-mouse click on the
table, the only option I have is to select "Open", which runs the whole
dataset.
--
Thanks in advance,
StevenHi Steven,
If you're just looking to add a filter before running the query, then
when you right click on the table, you can select
Script Table As > SELECT to > New Query Editor Window | Clipboard |
File
Hopefully that's close to what you had in mind.|||If it is the query builder you are after, you can reach it from the view folder. Right-click, new
view, construct the query and you then don't have to save it as a view. Personally, I don't
appreciate query builders, though. I find them too limiting and they don't allow me to expand on my
SQL knowledge.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steven K0" <stroy@.api.com> wrote in message news:eDpZyG2LGHA.2316@.TK2MSFTNGP11.phx.gbl...
> Hello,
> When using SS2K, you can create a query by right-mouse click on the table and then selecting
> "query" in the Enterprise Manager. How do you do this in SS2005 when using the Management Studio?
> When I right-mouse click on the table, the only option I have is to select "Open", which runs the
> whole dataset.
> --
> Thanks in advance,
> Steven
>

Create query based on a field that wont be the same value in both tables

I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.

CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)

INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')

Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB

Here's what the results should look like for the above data.

Name TestA Date TestB Date
-- ---- ----
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Any help would be much appreciated!!!!On 29 Sep 2004 07:41:18 -0700, Jim G wrote:

>Here's what the results should look like for the above data.
>Name TestA Date TestB Date
>-- ---- ----
>Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
>Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
>Account 1 9/3/2004 3:00pm (NULL)
>Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Hi Jim,

Thanks for posting DDL ans INSERTS for sample data!

The following query gives the above results:

SELECT a.Name, a.RunDate, b.RunDate
FROM TestA AS a
LEFT JOIN TestB AS b
ON b.Name = a.Name
AND b.RunDate >= a.RunDate
AND NOT EXISTS (SELECT *
FROM TestA AS a2
WHERE a2.Name = a.Name
AND a2.RunDate > a.RunDate
AND a2.RunDate < b.RunDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Awesome! That worked perfectly. Thanks!

Create Query

I have a table where the first column has several numbers in numerous
formats. I would like to sort by using the first two digits. (15)
This is what I have right now.
select *
FROM GL1400
WHERE GL_ACCT '15';
GO
Obviosuly in the 300k rows there are a few '15' that are in the middle of
the numbers. I was able to cut the amount of arounious results by doing this
search as the column format is 15-XXXX-XXXX-XXXX
select *
FROM GL1400
WHERE GL_ACCT '15-%';
GO
I have been searching for the command that will only search the first two
characters with no such luck.
Any help would be appreciated.
Thanks
Beginnerselect *
FROM GL1400
WHERE left(GL_ACCT,2) = '15'
GO
http://sqlservercode.blogspot.com/|||Am I missing something, I do not see a formula that you wrote.
here is an example of the culumn:
14-15-123
15-11-123
expected result = 1 row
15-11-123
Given the above example you can see where my curent search failed.
SELECT *
FROM GL1400
where GL_ACCT LIKE '02-%';
GO
Using the above fomula I am getting both results.
"Absar Ahmad" wrote:
> Seems that followign command will help you. Check BOL for details of this
> command:
> Left
> If this is not what you are looking for, please send DDL of GL1400 table a
nd
> a few rows of sample data along with example of expected Result from the
> query.
> "bluesrock12000" wrote:
>|||I am using the LEFT function
LEFT(FIELD,2) = '15'
WHERE left(GL_ACCT,2) = '15'|||Any of the following query should work:
SELECT *
FROM GL1400
where left(GL_ACCT,2) = '15'
or
SELECT *
FROM GL1400
where GL_ACCT like '15%'
Note: The second query can benefit from index on GL_ACCT.
"bluesrock12000" wrote:

> Am I missing something, I do not see a formula that you wrote.
> here is an example of the culumn:
> 14-15-123
> 15-11-123
> expected result = 1 row
> 15-11-123
>
> Given the above example you can see where my curent search failed.
> SELECT *
> FROM GL1400
> where GL_ACCT LIKE '02-%';
> GO
> Using the above fomula I am getting both results.|||
> select *
> FROM GL1400
> WHERE GL_ACCT '15-%';
> GO
> I have been searching for the command that will only search the first two
> characters with no such luck.
select *
FROM GL1400
WHERE GL_ACCT LIKE '15%'
(You had no operator, so what it was effectively doing was a bit like:
SELECT GL_ACCT '15' -- I.e. select the values from GL_ACCT, but give the
column an alias of "15"|||Seems that followign command will help you. Check BOL for details of this
command:
Left
If this is not what you are looking for, please send DDL of GL1400 table and
a few rows of sample data along with example of expected Result from the
query.
"bluesrock12000" wrote:

> I have a table where the first column has several numbers in numerous
> formats. I would like to sort by using the first two digits. (15)
> This is what I have right now.
> select *
> FROM GL1400
> WHERE GL_ACCT '15';
> GO
> Obviosuly in the 300k rows there are a few '15' that are in the middle of
> the numbers. I was able to cut the amount of arounious results by doing th
is
> search as the column format is 15-XXXX-XXXX-XXXX
> select *
> FROM GL1400
> WHERE GL_ACCT '15-%';
> GO
> I have been searching for the command that will only search the first two
> characters with no such luck.
> Any help would be appreciated.
> Thanks
> Beginner|||Thank you for all your help,
That was exactly what I was looking for.
"Absar Ahmad" wrote:

> Any of the following query should work:
> SELECT *
> FROM GL1400
> where left(GL_ACCT,2) = '15'
> or
> SELECT *
> FROM GL1400
> where GL_ACCT like '15%'
> Note: The second query can benefit from index on GL_ACCT.
> "bluesrock12000" wrote:
>
>

create new database in analysis service not use BI

i have AAAA project create by BI, now i want to change my computer, so i get XMLA from AAAA project in BI and run it in analysis service XMLA query but it return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the Analysis Services Project1 database, or the database does not exist.

Execution complete

i try another way: i click right mouse on my database from analysis service, so i choice "Script database as "--> CREATE to... and i get a new XMLA file so igo to another computer, and run it, SQL return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the AAAA database, or the database does not exist.

Execution complete
can i solve this problem.
thanks

Another way to do this is to use BI dev studio to import the database and then redeploy to another server

For example

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

After loaded, go to project properties and set the target server.

Also you can use the Deployment Wizard under the start menu/SQL Server 2005/Analysis Services/Deployment Wizard

You do need to be an administrator on the target server in order to create a database there.

HTH

-Jamie

|||sorry, i want to create on new computer not connect to my computer, how to export analysis services database to import to another computer

As you said, i have to use

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

on my computer and select database on my computer, after that change target server. on properties change server name,

then i go to another computer and open my project on target server?

i can't understand about your idea,

can you explain to me

|||I'm confused now. What is the true goal of what you are trying to accomplish? Are you simply trying to move an Analysis Services database from one machine to another machine? If so, the correct way to do so is to use backup and restore. If you want to move the definition of a database between machines, these other options are the way to go.|||

This link gives more information about the deployment wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx which can create a database on Computer B from a project on Computer A

This link gives information about the synchronization wizard which may also be useful: http://msdn2.microsoft.com/en-us/library/ms174488.aspx - it can copy a database from Computer A to Computer B

There also other methods, 5 in total, described here: http://msdn2.microsoft.com/en-us/library/ms175446.aspx

Which method you use will depend on exactly what you would like to acheive. But I think you will find one of these methods suitable.

create new database in analysis service not use BI

i have AAAA project create by BI, now i want to change my computer, so i get XMLA from AAAA project in BI and run it in analysis service XMLA query but it return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the Analysis Services Project1 database, or the database does not exist.

Execution complete

i try another way: i click right mouse on my database from analysis service, so i choice "Script database as "--> CREATE to... and i get a new XMLA file so igo to another computer, and run it, SQL return follow error:
Executing the query ...
Either the user, PCHOME\max, does not have access to the AAAA database, or the database does not exist.

Execution complete
can i solve this problem.
thanks

Another way to do this is to use BI dev studio to import the database and then redeploy to another server

For example

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

After loaded, go to project properties and set the target server.

Also you can use the Deployment Wizard under the start menu/SQL Server 2005/Analysis Services/Deployment Wizard

You do need to be an administrator on the target server in order to create a database there.

HTH

-Jamie

|||sorry, i want to create on new computer not connect to my computer, how to export analysis services database to import to another computer

As you said, i have to use

Launch BI Dev Studio

New Project/Business Intelligence Projects/Import Analysis Services 9.0 Database

on my computer and select database on my computer, after that change target server. on properties change server name,

then i go to another computer and open my project on target server?

i can't understand about your idea,

can you explain to me

|||I'm confused now. What is the true goal of what you are trying to accomplish? Are you simply trying to move an Analysis Services database from one machine to another machine? If so, the correct way to do so is to use backup and restore. If you want to move the definition of a database between machines, these other options are the way to go.|||

This link gives more information about the deployment wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx which can create a database on Computer B from a project on Computer A

This link gives information about the synchronization wizard which may also be useful: http://msdn2.microsoft.com/en-us/library/ms174488.aspx - it can copy a database from Computer A to Computer B

There also other methods, 5 in total, described here: http://msdn2.microsoft.com/en-us/library/ms175446.aspx

Which method you use will depend on exactly what you would like to acheive. But I think you will find one of these methods suitable.

create multiple views

Hi,
I want to create multiple views in one run in the query analyzer, but it
won't work. I've the following sqlcode in the analyzer:
create view test1 as select.......
create view test2 as select......
etc.
What is wrong with this code?
On Thu, 5 Aug 2004 08:09:02 -0700, Ezekil wrote:

>Hi,
>I want to create multiple views in one run in the query analyzer, but it
>won't work. I've the following sqlcode in the analyzer:
>create view test1 as select.......
>create view test2 as select......
>etc.
>What is wrong with this code?
Hi Ezekil,
The CREATE VIEW statement must be the first in a batch. So your code will
run if you add batch seperators:
create view test1 as select.......
GO
create view test2 as select......
GO
etc.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create multiple views

Hi,
I want to create multiple views in one run in the query analyzer, but it
won't work. I've the following sqlcode in the analyzer:
create view test1 as select.......
create view test2 as select......
etc.
What is wrong with this code?On Thu, 5 Aug 2004 08:09:02 -0700, Ezekil wrote:

>Hi,
>I want to create multiple views in one run in the query analyzer, but it
>won't work. I've the following sqlcode in the analyzer:
>create view test1 as select.......
>create view test2 as select......
>etc.
>What is wrong with this code?
Hi Ezekil,
The CREATE VIEW statement must be the first in a batch. So your code will
run if you add batch seperators:
create view test1 as select.......
GO
create view test2 as select......
GO
etc.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create Memory intense query

I need a query that will consume al lot of memory.
I have a server with 8GB of memory but it uses only 400MB.
I want to see SQL server using 5GB or something.
Can I accomplish this with the pubsdatabase?
Regards.
A systematic way is to (1) create a database that is larger than the
allocated amount of memory, (2) have you query(ies) randomly touch all the
pages, and (3) run the queries from multiple clients. This will create memory
pressure. Note that it'll most likely create I/O pressure.
In your case, to consume 5GB of memory, you can create a bunch of temp
tables, populate them to 5GB, and kep the queries running on them to keep the
pages hot in memory. This is just one of many ways.
Linchi
"Zekske" wrote:

> I need a query that will consume al lot of memory.
> I have a server with 8GB of memory but it uses only 400MB.
> I want to see SQL server using 5GB or something.
> Can I accomplish this with the pubsdatabase?
> Regards.

Create Memory intense query

I need a query that will consume al lot of memory.
I have a server with 8GB of memory but it uses only 400MB.
I want to see SQL server using 5GB or something.
Can I accomplish this with the pubsdatabase?
Regards.A systematic way is to (1) create a database that is larger than the
allocated amount of memory, (2) have you query(ies) randomly touch all the
pages, and (3) run the queries from multiple clients. This will create memor
y
pressure. Note that it'll most likely create I/O pressure.
In your case, to consume 5GB of memory, you can create a bunch of temp
tables, populate them to 5GB, and kep the queries running on them to keep th
e
pages hot in memory. This is just one of many ways.
Linchi
"Zekske" wrote:

> I need a query that will consume al lot of memory.
> I have a server with 8GB of memory but it uses only 400MB.
> I want to see SQL server using 5GB or something.
> Can I accomplish this with the pubsdatabase?
> Regards.