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 TABLE in wrong database (Master)

I am using ASP.NET 1.1 and MS SQL 2005

the folowing ODBC stringconnection
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written inMaster !! and not inMyBase

I am using windows Authentication

what can be wrong ?

thank youWhat is your reason for creating table in VB.NET instead of managment studio with SQL or the GUI and why are you using obsolete ODBC instead of ADO.NET? When you get the answer to that you will know why you are creating the table in the Master database. Hope this helps.|||Caddre if you donyt know the answer to that problem please give up !
i must do it in that way because the database allready exists in many intranets on > 10 000 PC|||the problem is why is it creating tables in Master when the connection string =
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Connection=;

?

|||But you still don't do it that way, you have two options get the DBA to create the table for you or you should just right click and register the SQL Server with the database at the top of management studio. The number of users on the network is not relevant the only requirement is on the same network and in an intranet you are. And you don't need new SQL Server with your SQL Server running your DBA can give you the personal SQL Server free it comes with the license. Here I have read only access to Oracle if I need to create something the DBA will create it. And yes I also use SQL Server I can help you with most problems. Post again if you still need more help. Hope this helps.|||thanks a lot Caddre but it works fine now

just forgottent database= in the connectionstring when i have copy-pasted the string

and that way of course works perfectly|||I am glad you got it resolved and I am sorry about my first post.

create table from another table

Hello,

under MSSQL8 doing this "create table tblA as ( select Name from tblB );"
results in error 156 (wrong syntax near AS keyword). What is my mistake?

thanks and regards
MarkSee SELECT in Books Online:

select Name
into dbo.tblA
from dbo.tblB

Simon

CREATE TABLE in wrong database (Master)

I am using ASP.NET and a normal ODBC stringconnection

Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;

when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBase

i am using windows authentication

what can be wrong ?

thank youHi

Your default db will be master. You need to name your params in the string. How about:

Driver={SQL Server};Server=(local);Database=MyBase;Uid=;Pwd=;T rusted_Connection=;
?

EDIT - BTW - do you not need to put True after trusted connection or does that work?|||And...

never need to ask a connection string question of anyone again:
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

HTH|||my connection string is perfect .. it is not a connectionstring problem|||No it's not. Have another look. MyBase is just floating there. And Trusted Connection, Uid and Pwd are parameters without values.

Check the links.|||my connection string is perfect .. it is not a connectionstring problem

So....

Why are you asking for help then?|||So....

Why are you asking for help then?It's perfect... That doesn't mean that it is working.

-PatP|||You could also set the default for the userid to the database you want the table(s) written to. Then the default would be MyBase instead of Master.

Lookup sp_defaultdb in BOL.|||this connecting string is working fine since 6 months, i have installed MS SQL 2005 on MS SQL 2000 and it doesnt work any more, it is not a connectionString problem
it is a database rights problem, and a microsoft problem too|||You could also set the default for the userid to the database you want the table(s) written to. Then the default would be MyBase instead of Master.

Lookup sp_defaultdb in BOL.

there is no UserId no password I am using windows authentication

what do you mean by Then the default would be MyBase instead of Master.

do you mean the user account ? I am using only one user account for all my databases

thank you|||Hi

Whatever account you use (SQL or NT) to connect to SQL Server will have a default database assigned to it. If you don't specify a database in your connection string you will connect to the default database. You have not specified a database in your connection string (however perfect it may be) so you are connecting to your default database, typically master (master is the default default database :) ). So - either specify a database in your connection string or change your default database in SQL Server.|||it works now !!

thank you|||this connecting string is working fine since 6 months, i have installed MS SQL 2005 on MS SQL 2000 and it doesnt work any more, it is not a connectionString problem
it is a database rights problem, and a microsoft problem tooAh - just realised your problem - I bet in 2000 the default db was set up but not in 2005 eh? Seriously - the MyBase bit in your connection string is doing nothing - I'm surprised it didn't throw an exception to be honest.

Glad to have helped :D|||it works now !!

thank you

Must be the miracle connection string...

And I'm sure you didn't change a thing...

Create Non-Sequential Unique ID (EAN?)

Hi all,

I might be getting this all wrong but bear with me.

I need to create some kind of Unique field in my DB that isnonsequential. This is because I need it to be difficult to guessids if you have an example in front of you.

I have looked at8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell mehow many uniques I can get out of this system?

For my ID: I have looked at something along the lines of:

Hex(row_id) + "T" + Hex( Trimmed(EAN)) The "T" serves to split the numbers for when I am converting back.

So for example:

row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) )
---------------------------
3166 00031663 C5ET7BAF
3167 00031673 C5FT7BB9
3168 00031686 C60T7BC6

Is this too easy to guess (once you can tell there are two hex numbers there?)

What do people think?

Thanks,

Pete

Hi Pete,

Based on your description, I understand that you need a field in the databasae table, which is generated automatically. It has to be unique and non-sequential.

In this case, I would suggest the UniqueIdentifier data type. It is a GUID value and can be generated with NEWID() function.

Here are some more information about this data type:

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

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Hi Kevin,

Sorry, once again I have forgotten to post somepretty vital information! However, I didnt know about SQLGUID so thanks anyway!

The limitation on the ID I requireis that it needs to be typed into a text message (SMS) so I'm trying tokeep it as short as possible.

Does anyone know of any suchnumbering systems? I think anything between 8-14 chars would beperfect. (I realise that we may have anything up to amillion records so the number of chars in the ID will go up - or atleast - Any leading zeros will be removed)

Thanks again,

Pete

|||

Hi Pete,

Sorry for my ignorance, but I don't know any ID system like that. May the the other community member has some idea on this.

|||Cheers Kevin, maybe I'm just looking for a Silver Bullet!