I have the TAMCreate.sql file which contains the script to create all tables in the Database TAMaintenance. Its created and used in the SqlServer 2000. But i have only SqlServer 7. From the script i copy and paste each table and created upto 7 tables without any problem, when i create the 8th table i get the following error the code for the table
CREATE TABLE AA_Branch_Master(
Branch_Code varchar(15) CONSTRAINT PK_AA_Branch_Master1 PRIMARY KEY,
Branch_Name varchar(50) NOT NULL,
Branch_Opened datetime NOT NULL,
Branch_Location varchar(50) NOT NULL,
Branch_Province varchar(25) NOT NULL,
Branch_Incharge varchar(50) NOT NULL,
Branch_Remark varchar(150),
Branch_Status BIT(1))
go
Error:
Server: Msg 2716, Level 16, State 1, Line 1
Column or parameter #8: Cannot specify a column width on data type bit.
In the same way i have get the error or another table, rest of the tables are created successfully.
the code for the table
CREATE TABLE AA_Supplier_Master(
Supp_Code varchar(15) CONSTRAINT PK_AA_Supplier_Master1 PRIMARY KEY,
Supp_Name varchar(100) NOT NULL,
Supp_Address varchar(150) NOT NULL,
Supp_Contact_Person varchar(50) NOT NULL,
Supp_Paymode varchar(25) NOT NULL,
Supp_Tel1 varchar(25),
Supp_Tel2 varchar(25),
Supp_Fax varchar(25),
Supp_Postbox varchar(15),
Supp_Postal_Code varchar(15),
Supp_City varchar(50) NOT NULL,
Supp_Country varchar(50) NOT NULL,
Supp_Status BIT(1))
go
Error:
Server: Msg 2716, Level 16, State 1, Line 1
Column or parameter #13: Cannot specify a column width on data type bit.
I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY, studentId varchar(50) NOT NULL, course varchar(50) ) CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY, course varchar(50) NOT NULL, sco varchar(50) )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath
HelloIs there a way to use Goto or something like it in QA? I have a script witha few GO statements, as I'm sure you know, declared variables, labels, andgoto's only have scope within each GO block. If i have an error in thesecond or third, of 5 GO blocks, I want to trap the error number and gotoonly ONE error handler label.Anyone know of a way?Thansk.Matthew WellsJoin Bytes!
How do I update a table of 600 records with varying amounts of the same branch names say, there are 60 records that are Atlanta etc.Based on the following query that retrieves the Branch Id. Select t1.Branch, t2.SCName, SCID from WWFE07BoothLeads As t1JOIN SMC_New_Products.dbo.SupportCenters As t2ON t2.SCName Like t1.Branch+'%'Update WWFE07BoothLeads Set BoothId = ?(1) Where Branch = ?(Atlanta)
Dear All i have a problem In Query Analyzer .When I create two diffrent table with two diffrent schemas dbo and guest but when i log on query analyzer and run query from guest schema's tahle it gives error object not found i think its some rights problem ..
We have several databases but one is behaving differently in SQL Query Analyzer. Please assume we have selected the correct db in the the pull-down and assume database name is DB1 and table name is Table1
SELECT * FROM db1.[table1] works fine.
SELECT * FROM [table1] returns "Invalid Object Name Error".
This problem causes some complex queries not to work so I'd like to know if this db has some special setting that requires explicit db name in each query.
We have also tried.. use [db1] SELECT * FROM [table1]
It still gives the same error. Any help would be appreciated. Thansk
I am trying to import a sql table with Query Analyzer. I opened up the Query Analyzer, found the sql table I needed, opened it and it read everything in the table. I see the execution script that defines the headers and the data type for all the columns and the information populated below that. So I told it to "Run".
It says it is executing the query batch, I see the globe spinning like it is doing something, it says it is on line 10, row 43 (whatever that means), so it appears to be still working.
I stopped one of the Query Analyzers and noticed this message: "The name 'T' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted"
One of the many lines that it began having issues with this is: insert into TABLENAME values(1,'AAVS','ANALYZER','S310','','357C','855','776493','DIG VID ANLZR CORE MOD','P','VOTE5WBKAA',207610,'207610--1','CO','','1','','1','S310','ADVANCED AUDIO VISUAL SYSTEMS',T);
It appears that it does not much care for the 'T' at the end of each row. Is this required? Is there a way to take it out of the entire table before running it again?
Is there a way to choose what information I want to populate?
insert into TABLENAME values(1,'AAVS','ANALYZER','S310','','357C','855','776493','DIG VID ANLZR CORE MOD','P','VOTE5WBKAA',207610,'207610--1','CO','','1','','1','S310','ADVANCED AUDIO VISUAL SYSTEMS',T);
What if I only want some of these fields? Is there a way to tell it that I only want: BCKEY, MFG, LIST, SERIES, DSC, EQTYPE, CLEI, PN & EPN? The rest I do not want, so can I take the rest out somehow?
I have a table with two fields Part_num and Pic in SQL server 2000 Pic is of Image type. Is there a way I can save images for each part_num using Query analyzer?
Hi All !I open a table in Query Analyzer by right clicking on it andselecting 'Open' and when the data is displayed in the view pane Iwould like to be able to edit it. It seems however that tables markedas read only (as shown on the top bar e.g. SQL Query Analyzer - [OpenTable ServerName.DBName.dbo.TableName(read-only)] ) can not be editedwhereas the one that do not have that attribute infront of the name Ican edit. Is there a way I can change the read only attribute of atable so that I can edit data directly in the view pane?Please let me know if there is !Thanks a heap ! :)Harkirat
Hi there,I struggle to get this going i would like to insert data into 2 tmptables in a view.If i run the code on it's own it works perfectly until i want to createa view it complains about the INSERTthis is my codeCreate view dbo.vew_SwitchesAsINSERT INTO tmpInsSelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.INAME,MB.NOfrom Cars BIVLEFT JOIN MountainBikes MBON MB.ID = BIV.IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'IN'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC------ Step 2 -------Into tmpOutsInsert Into tmpOuts ---- All Switches In ----SelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.NAME,MB.NOfrom Cars BIVLEFT JOIN Mountainbikes MBON MB.ID = BIV._IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'OUT'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC----------------------Step 3 ----------------SelectDistinctins.DATE,ins.ID,ins.NAME ,insIND,ins.AMOUNT/100 as AmountIn,outs.IND,outs.AMOUNT/100 as AmountOut,outs.NAME
MS SQL Server 2000 SP3Windows XP SP2Error when try to debug in Query Analyzer:Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect todebugger on HORNET (Error = 0x80070005). Ensure that client-sidecomponents, such as SQLDBREG.EXE, are installed and registered onEUGENE. Debugging disabled for connection 63.Tried:- 'Troubleshooting the Transact-SQL Debugger' ( Book on-line )- EXEC ... 'legacy_on'- firewall is disabledWhat else can be done ?Thanks , Eugene
How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?
Hi All, Please let me know how we can need to run “SELECT” statement (in SQL SERVER query analyzer) for a table in different database without going/loading DATABASE B (i.e. without running “USE B”) e.g. we are in a database “A” and we need to run “SELECT” command for a table in Database say “B” without going/loading (i.e. without running “USE B”) the Database “B” Thanks in Advance J
Please provide some help regarding the "Interface Defining Error: IID_IDBDataSourceAdmin" error while trying to create a SDF database using Query Analyzer on a Windows CE 5.0 mobile device (Symbol MC3000).
Error: 0x80004005 E_FAIL Native Error: 28558 Description: SQL Mobile encountered problems when creating database [,,,,]
PS. Basically I have developed a mobile application that programmatically creates the database, the code worked on a similar device (Win CE 50), trying to run the application on a new device resulted in database creation errors. I tried creating a test database manually .. and this is what I got.
Browsing MSDN or searching on the Forum did not help.
I don't see what's wrong with this command. >> Create View BCPOutBatchWeightTaiwan AS Select * from batchWeight a JOIN Geography b ON a.GeographyPtr = b.GeographyPtr where b.countrycode = 'tw' << I get the following error if I try to create it. >> Server: Msg 4506, Level 16, State 1, Procedure BCPOutBatchWeightTaiwan, Line 2 Column names in each view must be unique. Column name 'GeographyPTR' in view 'BCPOutBatchWeightTaiwan' is specified more than once. <<
But it runs fine if I run only th 'Select' part of the command without 'CREATE' line.
i want to create table using another table but i am getting the following error.
Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'AS'.
my query is:
CREATE TABLE errorlog AS SELECT * FROM log where 1=0 becos i a trying to copy the structure of the table log to table errorlog.please rectify my problem,please
I just wondering where can I find the table of my database SuplliersDatabase. I need to know if it is already exists or not? I tried to look around but then still I can't find it.
select c.name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID where t.name= 'Hosea_tblDATA_NOTES'and c.is_identity = 0 order by column_id
I was thinking if it is possible to create a table from the results of this query, my table name(in this case 'Hosea_tblDATA_NOTES') will be a parameter, that whatever table name I pass, with the results I get I will be able to create another table, assuming all data types are varchars, but it should be generic.
I want to create a new table with the about of that query, that output has to be my columns.
There are no primary or candidate keys in the referenced table 'Donation' that match the referencing column list in the foreign key 'fk_Branch_bloodType'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors. The table name affected is Purchase ,below are the tables the table Donation is created but purchase can't
create table Donation ( Donation_Code varchar (5) primary key, Blood_Type varchar (4) not null, Date_Recd date not null, Date_Of_Expiry date not null, Purpose varchar (30),
i am trying to create stored procedure but i am getting error
create proc t @i int as
if @i = 1 begin select s Name,identity (int,1,1) as intid into #T from ( select 'SS' s) p end if @i = 2 begin select s Name,identity (int,1,1) as intid into #T from ( select 'S' s) p end
Server: Msg 2714, Level 16, State 1, Procedure t, Line 15 There is already an object named '#T' in the database. Server: Msg 170, Level 15, State 1, Procedure t, Line 17 Line 17: Incorrect syntax near 'p'.
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
I am new in T-SQL. I have just create a empty database and would like to create some table in it. So I type the following in SQL Query Analyzer:
CREATE TABLE mydatabasename.mytablename (
some code here ) But when execute, it give me an error from the first line "Specified owner name 'mydatabasename' either does not exist or you do not have permission to use it."
when I remove mydatabasename it inserts the table in to the master database, not in my database.
How can I insert the table into my database? Should I use SQL Query Analyzer or use others program? I 've tried to use the command "create table" in enterprise manager but it immediately give another error, some what like "could not use empty column name ... " Seem to be too simple question, but I am really appreciate your help
I created database using SQL server and runs under Cassini. The creation of database is ok but I have a problem when I am creating the table in database. Whenever I execute the code to create the tables, it shows the error message like 'Server not found' or just hang there. Does anybody know why it hang while I am creating the table? Is it because of the code or it's the time out error. Pls help as I am very new to this area.
AA!I am having problems on creating table through Enterprise Manager. Itgives me Error 1038 i.e. is as followsUnexpected ErrorODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot useempty objector column names. Use a single space if necessary.
Hi All,I am new to MS SQL Server.I am using MS SQL 2000.I have a problem increating a table by using Select command.I have table called "test"and i want to create another table with the same structure and rows.Itried with the following commandcreate Table test1 as select * from test;But it give an syntax error.I have tried the same command in Oraclebut i was working.Does MS SQL 2000 Server supports this kind of Query.Please help me to solve the problem or any other methods to performthis operation.Thanks in AdvanceKevin
I have to write a couple scripts that will update a couple columns in two separate tables and also insert a new row with the same data except for a few calculated or provided values ...... see specs below ...
1. tGradeHist Table Script One (Needs to be run first)
a. Read tGradeHist Table and Select rows with GradeEndDate = NULL and GradeStartDate = '1/1/2007 12:00:00 A.M.'
b. Calculate New Step Amount = StepAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
GradeCode - Same
GradeLocationCode - Same
Step - Same
GradeStartDate - '7/1/2007 12:00:00 A.M.'
GradeEndDate - NULL
GradeCurrencyCode - Same
StepAmount - Result of b (above)
GradeFrequencyCode - Same
RangeMaximumAmount - Same
RangeMidAmount - Same
RangeMinimumAmount - Same
GradeCurrentFlag - 'True'
MarketMaximumAmount - Same
MarketMidAmount - Same
MarketMinimumAmount - Same
GradeGUID - Same
TSCOL - Same
d. Update Row read in a (above) with GradeEndDate = '6/30/2007 12:00:00 A.M.' and GradeCurrentFlag = 'False'
2. tPersonBasePayHist Table Script Two (Needs to be run second)
a. Read tPersonBasePayHist Table and Select rows with PersonBasePayEndDate = NULL
b. Calculate New PersonBasePayAmount = PersonBasePayAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
PersonGUID - Same
PersonBasePayStartDate - '7/1/2007 12:00:00 A.M.'
PersonBasePayEndDate - NULL
PersonBasePayCurrencyCode - Same
PersonBasePayAmount - Result of b (above)
PersonBasePayFrequency - Same
PersonBasePayPayrollFrequencyCode - Same
BasePayReasonCode - 'SA'
ConductedBasePayReviewDate - Same
ScheduledBasePayReviewDate - Same
PayrollCode - Same
PersonBasePayCurrentFlag - 'True'
ApprovedByPersonGUID - Same
PersonBasePayGUID - Same
TSCol - Same
d. Update Row read in a (above) with PersonBasePayEndDate = '6/30/2007 12:00:00 A.M.' and PersonBasePayCurrentFlag = 'False'
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
hai guys, i have written a stored procedure which creates a table ex: USE PUBS GO IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC') DROP PROCEDURE RC_STRPROC GO USE PUBS GO CREATE PROCEDURE RC_STRPROC (@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35)) AS IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME') DROP TABLE @TBLNAME CREATE TABLE @TBLNAME (@COLVAL1, @COLVAL2) GO it gives an syntax error at '@tblname' can u guys tell me the problem
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100) Declare @File varchar(100) set @FileName='brkrte_121227102828' SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1) --=select @File
[Code] ....
Error massage:- Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.