Create Table syntax SQL in SQL Server 7

Hi guys,I need to pass some SQL to someone else who will run it on their database. I have got the SQL for SQL Server 2000 but they are running SQL Server 7. Apparently the below MSSQL 2000 script doesn't work;SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tableName]( [id] [int] IDENTITY(1,1) NOT NULL, [ArticleID] [int] NULL, [Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WrittenDate] [datetime] NULL,CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ))What is the equivalent of the above in for SQL Server 7? I don't have access to it via SQL Server Manager so have to run the script.

Looks like the forum didn't like Safari. I'll try that againwith Firefox and see if that comes out a bit better. SO to summarise,how do I do the following in SQL Server 7, as this is for SQL Server2000 and I don't know what the syntax difference is:

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tableName]

( [id] [int] IDENTITY(1,1) NOT NULL,

[ArticleID] [int] NULL,

[Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[WrittenDate] [datetime] NULL,

CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC ))

|||

I've written this in what should be OK in SQL Server 7. Idon't have anyway of testing this, and would prefer that it is correctbefore sending it. So, is the below equivalent to the SQL Server 2000code above:

CREATE TABLE tableName
(
id int IDENTITY NOT NULL,
ArticleID text,
Heading text,
BodyContent text,
WrittenDate datetime
)

This doesn't seem to specify any primary key, how do I do that if indeed I need to?

Slowly realising how much I rely on SQL Server Managment studio...!

Create Table Syntax

Hi Guys
Really need your help I donno what I am doing wrong in here
Want to create a table with another existing table
Here is the syntax I am using

create table pctemp1
As
(SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date]
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null )

and it says Syntax error with AS clause, tried removing AS clause but no go , can anybody help me please ...

thanksselect * into NEW TABLE NAME from OLD TABLE NAME--
THIS IS THE SYTAX..YOU CAN USE THIS FOR UR NEED|||SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date] INTO NEW_TABLE_NAME
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null

TRY THIS...

Create subcube action from currently sliced cube?

Hi guys! I'm wondering if its possible to create an action that would create a subcube by right clicking on a measure cell? For example, the user narrows the cube to 1000 accounts in a particular market. Now they want a cube based on only these 1000 accounts... so they right click on the "1000" cell, pick actions (just like drill through) and there's a chioce to create a subcube. And advice?

Thanks!

You can create a statement action that could execute some MDX against your Cube, but depending on your client browser, the statement action may not be exposed to the end user. Another option would be to create a reporting action that would launch an SSRS report that executed some MDX against your cube. Even if you were able to create an action that would create a subcube, I don't see how your front end tbrowser would be able to use it. The only thing that I've encountered that may solve your problem is by using proclarity. I believe Proclarity gives you the option to browse the cube and then save selected dimension members as named sets.

Hope this helps.

Van Dieu

Create Multiple Store Procedures in 1 SQL statement

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

I can't get your Question..

Need more information

|||

I hope you need to write sp like this way.

/*
sp1
*/
CREATE PROC MultipleStoreProcedures;1
AS
BEGIN
SELECT [CategoryID], [CategoryName], [Description], [Picture]
FROM [Northwind].[dbo].[Categories]
END

GO

/*
sp2
*/
CREATE PROC MultipleStoreProcedures;2
AS
BEGIN
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
FROM [Northwind].[dbo].[Customers]
END
GO

**
--execute sp1
exec MultipleStoreProcedures;1
--execute sp2
exec MultipleStoreProcedures;2
**

|||

thx. it helps. :) However, if using 2005 for creating, then the 'GO' statement will be dissappear when u modify the store procedure. But it seems ok anyway. Thx alot.

Best Regards,

Hans

|||

The 'GO' isn't part of a T-SQL statement, it's just a marker that identifies where the end of batch is.
So, the above is, if you save it all as one file, and run it all at the same time, still two batches ( or statements if you will ), but it looks like it's working for you wanted anyway =:o)

/Kenneth

Create login problem in SQL Server 2005

Hey guys,

I'm having a problem making a new login inside the sql management studio, the problem is, when i create a new login, i selected SQL Authentication, then type a password, then uncheck Enforce password policy.

i then select the database i want the login to be associated with, but once i click ok i get this exception:
Create failed for Login ''. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)

I even tried with Northwind and a brand new database with a table and 2 columns but it's the same story every time.
Any ideas?

Thanks a bunchMake sure you have entered "Login Name" in the text box provided at the top of the window

thanks
Anoop

Create Indexes & extra columns!

Hi guys
2 questions.
Is it recomenable to create
1. Indexes
2. extra columns
on a table while the database is being used?
I have not yet had the guts to do it while the database was in use however
it would be somewhat easier if I could?
Regards
JonasJonas Larsen wrote:
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use
> however it would be somewhat easier if I could?
> Regards
> Jonas
Recommended is a loaded word. You can certainly perform both operations
while users are accessing the database. Adding a column should be quick
as long as you don't have to load data into the new column. Adding an
index is a more time consuming process if the table is large. If you
create a clustered index, you'll likely take the table offline until the
operation is complete and all non-clustered indexes are rebuilt.
If you are concerned about affecting the availability of the table in
question, schedule the operation to take place at night using the SQL
Server Agent.
David G.|||I agree with David, if you choose to do this during the day, the structures
will still ( automatically) be unavailable until the process completes,
(which could take some time.)
most people would schedule this during off hours... and backup prior...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23m%23Ai4liEHA.3288@.TK2MSFTNGP10.phx.gbl...
> Hi guys
> 2 questions.
> Is it recomenable to create
> 1. Indexes
> 2. extra columns
> on a table while the database is being used?
> I have not yet had the guts to do it while the database was in use however
> it would be somewhat easier if I could?
> Regards
> Jonas
>

Create DTS PACKAGE programmatic

Hi guys..!!

i am working on Dynamic creation of DTS-packages in C#.NET 2005(sql server 200)

but i not must create package in Sql Server.
but i cant...

can i do ?
any ideas ?

thx...

DTS has a Save as VB option. This is a great way of getting sample code, so try that on a demo package. VB to VB.Net has some differences, see this link for some tips.

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

VB.Net to C# should not be hard, plenty of tools that do this.

Hopefully when you view the VB sample code you will get a feel for the object model and how it is used, and going forward you can just write the C# directly.

There is a DTS specific newsgroup (microsoft.public.sqlserver.dts) which may be worth search and posting on, as it is DTS not SSIS focused.

|||

but this source not contain code for vb or vb.net
no problem is writing vb or vb.net

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

thx...

|||

You appear to be repeating yourself, so can we try again...

Mehmet Metin Altuntas wrote:

problem is "how to create programmatic (dynamic) DTS-Package in dotnet? "

To create a DTS package dynamically in dotnet you need to write some code. DTS uses an object model, have you added a reference?

Mehmet Metin Altuntas wrote:

but this source not contain code for vb or vb.net

What is not source code? I suggested using the Save as VB option to generate some sample code. If you already know how to use the DTS object model in code, what are you asking? If you do not know how to use the DTS object model in code, try generating some sample code as a guide.

Mehmet Metin Altuntas wrote:

no problem is writing vb or vb.net

Sorry, that does not make sense.

The Save as VB option wil give you VB code. The link told you how to convert this to VB.net. There are tools that convert VB.net to C#. It may be a three stage process but it will give you C# code. Once you are familiar with this you can write c# directly, but to HELP you LEARN how to use it in code, Save as VB is a start point.

Surely some code, even VB is easier to learn from than no code at all. There is no Save As C# option, so learn from the VB.

Any help?

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DB on the fly

Hi guys.

Here is what I want to do:

1. create a small desktop application

2. when this application starts, it will check if the file "...mydb.mdf" file exists in a specified folder

3. if the file doesn't exist, I want to create the database and connect to it using User Instance

How can I create the database?

I tried to connect to SQLExpress and run a script using SqlCommand. It didn't work.

Is there any other way to do this?

Or what is the correct way to do it?

Thanks.

Mircea

You can create entire databases in SQL Script using CREATE DATABASE, CREATE TABLE, and other similar commands. Some of them (like CREATE VIEW) must be sent by themselves, but otherwise its pretty simple.

Alternatively, you can embed an empty database in your application and extract it out as needed.

-Ryan / Kardax

|||

Hi Ryan.

I have the script for creating the database. My question is how should I run this script?

Thanks.

Create Customer Ranking in Cube

Hey guys,

is there a way i can create a Rank Member or measure in my cube based on sales for that customer, no matter what time heirarchy we are looking at.

I have reporting requirements where they want to see all customers, ordered by Sales Amount. It could be at a year level, month level, or even a week/day level..

Im new to SSAS and MDX, i have looked at a few examples posted here, but cannot work out where i put the script in my cube. Is it a measure or do i do it as a calculated column in the DSV.

Any help is much appreciated.

Thanks

Scotty

Here's an Adventure Works ranking query, which uses a query-scoped calculated measure (assuming that the Customer hierarchy is on the rows of the query):

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select [Date].[Fiscal Year].Members *

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Geography].[Country].Members on 1

from [Adventure Works]

--

All Periods All Periods FY 2002 FY 2002 FY 2003 FY 2003 FY 2004 FY 2004 FY 2005 FY 2005
Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank Internet Sales Amount CustSalesRank
Australia $9,061,000.58 2 $2,568,701.39 1 $2,099,585.43 1 $4,383,479.54 2 $9,234.23 3
Canada $1,977,844.86 6 $573,100.97 3 $305,010.69 6 $1,088,879.50 6 $10,853.70 2
France $2,644,017.71 5 $414,245.32 6 $633,399.70 4 $1,592,880.75 5 $3,491.95 6
Germany $2,894,312.34 4 $513,353.17 5 $593,247.24 5 $1,784,107.09 4 $3,604.83 5
United Kingdom $3,391,712.21 3 $550,507.33 4 $696,594.97 3 $2,140,388.50 3 $4,221.41 4
United States $9,389,789.51 1 $2,452,176.07 2 $1,434,296.26 2 $5,483,882.67 1 $19,434.51 1

>>

|||Please remove this line, as it is wrong:

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Obviously, even if [Internet Sales Amount] is NULL, the rank will never be NULL - it is always an integer value.

|||

Ok guys, thanks for the replys..

I added the following into a calculated measure that i called CustSalesRank .. (taken from the 1st line of code) is that Correct?

When i added this to the expression box in the calculated member, i get a red undeline on the "with" statement.. What am i doing wrong?

CREATE MEMBER CURRENTCUBE.[MEASURES].[CustSalesRank]

AS with member [measures].[CustSalesRank] as

Rank([Customer].[Customer Group Name].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Group Name]),

[Measures].[Sales Amount], BDESC)),

select [Date].[Fiscal Hierarchy].Members *

{[Measures].[Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY [Customer].[Customer Group Name].Members on 1

from [Sales By Market],

VISIBLE = 1 ;

--

|||

the error i get when i try to deploy is:

Error 2 MdxScript(Sales By Market) (17, 5) Parser: The syntax for 'with' is incorrect. 0 0

|||

Hi Mosha,

You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

>>

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Brunswick (null) 5
Manitoba (null) 6
Ontario $36.96 4
Quebec (null) 7

versus:

with member [Measures].[CustSalesRank] as

Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC)),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

select

{[Measures].[Internet Sales Amount], [Measures].[CustSalesRank]} on 0,

NON EMPTY DrillDownLevel([Customer].[Customer Geography].[Country].&[Canada]) on 1

from [Adventure Works]

-

Internet Sales Amount CustSalesRank
Canada $1,977,844.86 1
Alberta $22,467.80 3
British Columbia $1,955,340.10 2
Ontario $36.96 4

>>

|||

> You're right in the strict sense - the reason I added NON_EMPTY_BEHAVIOR was to remove rows with no sales for this specific query (ie. a query ranking only among customers with sales data), as in this example:

Deepak - this is very very dangerous path. NON_EMPTY_BEHAVIOR is not a semantic feature, it is a performance hint. It doesn't cause rows to be removed by NON EMPTY. They might get removed sometimes when NEB is defined incorrectly, like in the example above, but it is purely a hint, and in other situations they won't get removed. So you will get inconsistant and even wrong results.

To properly remove rows with no sales, there must be specific IIF for that, i.e.

with member [Measures].[CustSalesRank] as

IIF( IsEmpty([Measures].[Internet Sales Amount]), NULL, Rank([Customer].[Customer Geography].CurrentMember,

Order(Extract(Axis(1),[Customer].[Customer Geography]),

[Measures].[Internet Sales Amount], BDESC))),

NON_EMPTY_BEHAVIOR = [Measures].[Internet Sales Amount]

Now NON_EMPTY_BEHAVIOR is correctly defined. Another idea is to optimize performance here would be to remove Order from inside Rank, and use version of Rank with 3 parameters.

|||

Mosha,

Thanks for your input.. Could you please kindly answer my question for me.. I just need to know where i put this code, i put it in a calculated measure and it didnt work. I removed all things from the select statement and down, but im not sure if it is working correctly..

Also, with all this code talk, im not 100% sure what i should be putting in now...

Thanks

Scotty

|||

For Adventure Works, you can put something like that inside MDX Script:

CREATE [CustSalesRank] = Rank([Customer].[Customer Geography].CurrentMember,[Customer].[Customer Geography].CurrentMember.Level.Members,[Measures].[Internet Sales Amount]);

|||

Thanks Mosha.. this worked great..

If i may ask for one more piece of advise, when i do this,, all the customers that have 0 sales are still shown with the lowest rank number.. in my case 81 . what i can i add to expression that will supress customers with 0 sales..?

Thanks

scotty

|||

Thanks for clarifying that, Mosha - I had (mistakenly) assumed that, in AS 2005, NON_EMPTY_BEHAVIOR would cause those rows to be removed.

On the use of Rank() with 3 parameters in lieu of Order() - has the semantics of Rank() changed in AS 2005, because BOL still says the following:

http://msdn2.microsoft.com/en-us/library/ms144726.aspx

>>

SQL Server 2005 Books Online

Rank (MDX)

Updated: 17 July 2006

...

The Rank function does not order the set.

>>

|||

The comment that Rank function doesn not order the set is correct, although I can see how it can be misleading. It probably refers to the fact that internal implementation of Rank doesn't need to order the set in order to find the rank. Please see detailed discussion about Rank and algorithms behind it in this blog:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx

|||

Thanks - since the same comment also appeared in AS 2000 BOL, it could be confusing. But the MDX Solutions chapter, referenced in your blog entry, draws this distinction:

"..The semantics for this function have changed between Analysis Services 2000 and 2005. When the expression is provided, it is used to determine if ties exist and what the right rank should be. In Analysis Services 2000, the expression was used when the tuple was found to search neighbors in the set and determine fair ranking numbers.."