In sql server 2003 I would create tables with generated sql scripts.
This way I could easily create duplicate dev environments.
Now in SQL 2005 Dev edition I try to run my scripts to create tables.
I create a db [MSS].
They run successfully but there is no table created.
(example script)USE [MSS]
GO
/****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KataNames](
[Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SequenceId] [int] IDENTITY(1,1) NOT NULL,
[Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?
I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.
Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.
Here are the tables that he created. Table 1 EmpID Text 123456 A1, A2, A3
In the text field of table 1 he inserts every task or valueid that the employee is able to do.
He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?
Chris writes "How do I create a copy of a table (not temporary).
In Oracle I do .
Create table newtable as select * from oldtable
This command copies data and column data types.
I tried entering
SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtable
in the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.
I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns."
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 backend. I currently have a selections table in the front end file which theusers use to make selections of records. The table has two fields -- primarykey (which matches primary key of main, SQL Server table), and a booleanfield. The table is linked to the main table in a heterogeneous inner join.I'm looking to move the table to the back end, while still giving eachmachine a unique set of selections. Using one large table with machine nameas part of the primary key actually slows things down. So I'm consideringusing a series of tables, where each machine has its own table in the backend for selections. The machine name would be incorporated in the particularselections table name, and the front end link would be modified on the flywhen the database is opened to point to that machine's back end selectionstable.This would require having about 50-100 individual selections tables in theback end database. Also, if a machine doesn't have a table when the databaseis opened on that machine, then that table would be created on the fly,populated, and pointed to via the ODBC link.Anyone see any problems with this approach, specifically creating the tableon the fly and then immediately using it, as well as having that many littletables running around? Thanks for any input!Neil
I'm transferring data from DB2 to SQL Server - the data gets wiped every night and reloaded. To keep up with any changes in the tables and to reduce the amount of maintenance on keep field & table definition updated, it would be nice to be able to create the tables in SQL Server in the SSIS package, based on the DB2 definition.
I've noticed it can be done in design mode when creating the OLE DB Destination (clicking on the 'New' button), but I'm not sure how I would automate this...
I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is
Dim ques As String For Each ques In questions query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))" cmd = New SqlCeCommand(query, con) cmd.ExecuteNonQuery()
Next
I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying
There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]
I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated. Thanks
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...
hello I am working with an existing database and there is no Foreign key between 2 tables how can i create a FK after , when the tables are allready full ?
product :
product_id report_id name
report :
report_id dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?
I am very new to SQL Server 2005 so please excuse my mandune queastion.
First what I accomplished was I created several tables within SQL Server 2000 using the Database and Table Wizards. I also created all the relationships the same way. Next, I had SQL Server 2000 and had the tool reverse engineer the SQL for this new database with all the relationships.
My next step was to run this newly created SQL script on my local box with SQL Server 2005 (SMSS). What I did was create the database first and then assign the users to the new database. From here, I moved to running the sql script. While it was not 100% perfect, it did create all the tables and relationships with no problems.
Now, I am trying to accomplish the same process one a development instance of SQL Server 2005, but the script fails. The data base error says the new database created does not exist. When we go back and look at the database within SMSS, it is GONE!
I am at a lost at how to replicate the database I have on my local box. Any suggestions would be greatly appreciated.
I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!
I had a SQL db that i copied all the tables into excel and it generated the quereies in order to create those tables....Is there anyway i could excute all those table creations at once or would it be easier to just write an application that does that for me????
I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaff Bookings.StaffID and it gives me error as below;What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?
Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Folks, Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?! Something like alter table add column+@i The alter table statement is within a Cursor and ideally @i should increment each time and alter the table to put in a new column. If I went 3 times thru the loop, I should have column1 column2 column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.
In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.
These are the tables I need to create: Create Table Author(...) Create Table Writes(...) Create Table Book(...) Create Table Copy(...) Create Table Loan(...) Create Table Customer(...)
Here I need to create a view by using following criteria, there is 3 tables which are Tbl.adminCategory, tbl_adminuser, tbl_outbox respectively. I am working on 2000SQL server
I am treing to create view as following but getting some error.
SELECT tbl_adminuser.adminUserName, tbl_AdminCategory.Name, COUNT(tbl_outbox.msgUserID), FROM tbl_adminuser INNER JOIN tbl_AdminCategory ON tbl_adminuser.adminUserID = tbl_AdminCategory.CatID INNER JOIN tbl_outbox ON tbl_AdminCategory.CatID = tbl_outbox.msgUserID AND tbl_outbox.msgUserID <> 0 GROUP BY tbl_outbox.msgUserID But I am getting error pls correct the view,
I am hoping someone could lead me in the right direction on how to figure out why my version of SQL Server 2005 Standard Edition is not working correctly. Here is my issue.
1. I've created two users: normal and superuser and associated the user to the new database. (I don't see any problem here just wanted to state two new users were created.) 2. I've created a new database (TestDB) as sa using the default seetings. 3 Next I want to create new tables in the new database. I've right-clicked on tables directory under the new database for creating a new table inside the Object Explorer Window. I get the below error when I do
Object reference not set to an instance of an object. (SQLEditors)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)
I was thinking something was wrong with the database so I ran sp_helpdb <database_name> and I don't get an error.
Thank for taking the time to read my post. I appreciated your time and any suggestion you could offer.
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows: 1.PerformanceDeficiencyNotice 2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below “Incorrect syntax near the keyword ‘INTO’�. Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT pdn.transactionid, pdn.employeenbr, pdn.lastname, pdn.firstname, pdn.processlevel, pl.facilityname as processlevelname, pdn.department, pdn.jobcode, pdn.title, pdn.supemployeenbr, pdn.managername, pdn.timeframe as pdn_timeframe, pdn.actualeffectivedate as pdn_startdate, /*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/ /*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/ case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then case pdn.timeframe when '30' then dateadd(month,1,pdn.actualeffectivedate) when '60' then dateadd(month,2,pdn.actualeffectivedate) when '90' then dateadd(month,3,pdn.actualeffectivedate) else null end end as pdn_enddate, pdn.status as pdn_status, status.description as pdn_statusdesc, pdn.managersignoff as pdn_managersignoff, pdn.managersignoffdate as pdn_managersignoffdate, pdn.associatesignoff as pdn_associatesignoff, pdn.associatesignoffdate as pdn_associatesignoffdate, pdn.witnessname as pdn_witnessname, /*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/ /*where the evaluationtype = 'X' (Extension Final).*/ /*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/ case when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate) else null end as ext_startdate, eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/ eval.evaluationtype as eval_evaluationtype, evaltype.description as eval_evaltypedesc, eval.status as eval_status, status2.description as eval_statusdesc, eval.effectivedate as eval_effectivedate, eval.managersignoff as eval_managersignoff, eval.managersignoffdate as eval_managersignoffdate, eval.associatesignoff as eval_associatesignoff, eval.associatesignoffdate as eval_associatesignoffdate, eval.witnessname as eval_witnessname into #pdninfo FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval on pdn.transactionid = eval.transactionid left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl on pdn.processlevel = pl.processlevel left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status on pdn.status = status.status and status.type = 'PDN' left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2 on eval.status = status2.status and status2.type = 'EVAL' left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype on eval.evaluationtype = evaltype.type /*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/ WHERE pdn.status in ('A','S') /*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/ /*'D' - In Progress, or 'N' - Not started)*/ OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/ /*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/ /* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/ /* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/ SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate into #perfreview FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview WHERE #pdninfo.employeenbr = perfreview.employeenbr AND perfreview.updatedate = (SELECT max(updatedate) FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2 WHERE perfreview2.employeenbr = perfreview.employeenbr AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/ SELECT 'orig' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/ SELECT 'ext' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
Hey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..
I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.
Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.
Code:
drop table Property_rental; drop table Property_type; drop table Property_owner; drop table Staff; drop table Tenant; drop table Tenant_category;
Msg 547, Level 16, State 0, Line 55 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 56 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 57 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 58 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 59 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 60 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 61 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 82 The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'. The statement has been terminated.
Hi folks, i need ur guidance. I have a few DDL and DML scripts which i want to be automatically applied during MSDE setup; or an .MDF file be attached automatically. Please suggest an easier way!
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT Article.articleId , Article.articleName , Article.articleStatus , Articlegroup_2.ArticlegroupId , Articlegroup_2.g2_key , Articlegroup_2.g2_name , articleGroup.articleGroupId FROM HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2 WHERE articleGroup.articleGroupId = Article.articleGroupId AND Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
Hi I have developed an application in ASP/SQL server 7. Ths system is single user. One of the tables is updated by usr actions( say Table A). To make it multi user. I want to create table such as A_Username. How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username . Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option? pls suggest
