I create a database which has the following tables:
1. Supplier
2. Item:
3. Store:4. Item Stored:5. Invoice:6. Invoice Lines:7. Recite: 8. Recite Lines:9. Client:where the Supplier table has the following field:· Code: contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456") (PK)· First Name: string of 30 character it could be Arabic or English· Middle Name: string of 30 character it could be Arabic or English· Last Name: string of 30 character it could be Arabic or English· First Contact Date: states the date of the first contact· Last Contact Date: states the last date of contact (updated after any goods sold by this supplier to us)· Phone: Phone number of this supplier· Address: 200 character also could be in Arabic or English· Rank: default = 0(number of Buying Transactions from that supplier)states its rank (the more goods we buy from the higher the rank) and the Invoice table has the following fields:· Code: contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456") (PK)· Date: date of insuring that invoice· Shipment Date: the date suppose the items ordered arrival· Type: Buying or Sellingo If Buying the user must select Suppliero If Selling the user must select Client· Supplier Code: incase Type is Buying it shouldn't allow null else it allows null, Code (FK) from table Supplier· Client Code: incase Type is Selling it shouldn't allow null else it allows null, Code (FK) from table Client
Now i want to create this database with it's tables
i just want to know the data type for each both tables supplier and invoice tables
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
I want to create a table in a SQL-database, and I'm wondering how to do it in Visual Studio 2005. Is it possible to create a table using an SQL-query in the SqlDataSource wizard? Or, in general... what would be the best way to create a database table from VS2005?
I am using ASP.NET and a normal ODBC stringconnectionDriver={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 MyBasei am using windows authenticationwhat can be wrong ?thank you
I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable. What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify. There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues. The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it? +Identity Specification (Is Identity) Identity Increment Identity Seed
Hello there,Now I'm really down, how do I simply create a Table in a database?It must be something likeCreate Table TableName ( column_name data_type ) But first how do I execute that string, so it create the table..And if we get that far, how do you then set a table to primarykey? Hope really for help, because this is a importen thing, and I cant find the answer? :S
I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB
E.g
Table1 1111KB Table2 123300MB Table3 120448KB
etc for all the tables in a particukar DB
I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.
public partialclassTrigger { [Microsoft.SqlServer.Server. SqlTrigger(Name = "myPM10000", Target = "[dbo].[PM10000]", Event = "FOR UPDATE, INSERT")] publicstaticvoidmyPM10000() etc.
When I try to build the project it fails:
Error 1 SQL71561: Trigger: [dbo].[myPM10000] has an unresolved reference to object [dbo].[PM10000]. D:ProjectsVS2013myproject_CLRobjDebugmyproject_CLR.generated.sql 8 32 myproject_CLR
I have tried with and without adding a database reference. With the reference all I accomplish is lost time waiting for that monster link to the database to generate. There has to be a way to make this happen, right? I don't want to have to rewrite this in Transact SQL.
I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?
Hi everyone, this is is my first post, so please reply and help. I'm working on a project right now that uses asp 2.0 and SQL server 2005 express edition. This is a general idea of the project. In our company some of us receive ECO notifications (engineering change orders) for our products and we need to implement these to the test scripts that are on the production floor. So the project is about entering the new ECO into a database which will send an automatic notification to our test team. When they receive the notification they will have to sign in to the website and introduce their login and password to sign off the ECO (Following some checkpoints already defined by me, for example, Area ready, Test script modification necessary, new firmware introduction, comments, etc...) but I also need to record WHO and WHEN sign that ECO. We have 3 different test areas in our factory: Electrical, Functional and Systems, so all THREE areas must be signed off in order to the ECO go to a IMPLEMENTED state (at this point i need to send a new email saying that the eco has been implemented in all three areas). So far I've completed the following things: -users validation (logins, areas) -New custom entry form for the ECOs and automatic email notification (part of what I did is described below). Dim ECODataSource As New SqlDataSource()ECODataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ECO_ICSConnectionString1").ToString()
ECODataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure ECODataSource.InsertCommand = "EcoNew" ECODataSource.InsertParameters.Add("EcoNumber", EcoNumberTextBox.Text) ECODataSource.InsertParameters.Add("EcoDescription", EcoDescriptionTextBox.Text) ECODataSource.InsertParameters.Add("EcoMandatory", EcoMandatoryDropDownList.Text) -Depending on which test area is the the engineering from, I can filter the ECOs and just shows the ones that their test area is pending. (using GridView) But I'm stuck right now when the engineers have to sign the ECO for their test areas. I was able to use the Gridview and DetailsView to EDIT most of the things that I need. But there are somethings that I don't like: 1. When using the EDIT option on Gridview or Detailsview, all fields can be edited including ECO number, description and mandatory, which I don't want them to change. If I set those columns to read only, when editing that row again. It gives me an error that says that the ECOnumber can't be NULL, but if I remove these 3 columns the Engineer will not know which ECO they have sign. They are only going to be able to see the EcoId, which doesn't say much. 2. Also I saw that I wasn't able to do is to enter the USER login and CURRENT system date and time automatically. I don't want them to manually enter the date and their login manually. 3. Finally, when the last area signs the ECO, I want to update that record and set a flag that tells me that the ECO has been completed. So what I really want is to create some sort of form (textboxes, labels, checkboxes, etc.) that will UPDATE the selected ECO from the gridview for instance. So when I select the row from the GridView, It will show the data (Econumber, description and mandatory as READ ONLY) and use the rest of the things as INPUT for the engineer to complete. At the end an "update button" and when I click it, It will enter/update the data on that specific row, but including the time and user login as well. Also to check if the other 2 areas have signed and if so, change the ECOReadiness flag to 1 and send the email. Is there a code like the one I used above to do this ? Or if you think there a better way to do this, I'll be very glad to hear it. I'm new using sql and asp, so If i'm asking some dumb questions please forgive me. .
Here's my table definition for your reference: EcoId - primary key. EcoNumber EcoDescription EcoMandatory EcoReadiness <- Flag for the entire ECO, when ALL 3 areas have signed, this will be 1. ATE < - Flag for Electrical area. ATEscripts < - Just a Yes/no input. ATEengineer <- user login ATEdatetimestamp <- Date.Now() FAT < - Flag for functional. FATscripts FATengineer FATdatetimestamp SYSTEMS < - Flag for systems. SYSTEMSscripts SYSTEMSengineer SYSTEMSdatetimestamp
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--/////////////////////////// USE MyDatabase GO CREATE TABLE dbo.LabResults (SampleID int PRIMARY KEY NOT NULL, SampleName varchar(25) NOT NULL, AnalyteName varchar(25) NOT NULL, Concentration decimal(6.2) NULL) GO --Inserting data into a table INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration) VALUES (1, 'MW2', 'Acetone', 1.00) VALUES (2, 'MW2', 'Dichloroethene', 1.00) VALUES (3, 'MW2', 'Trichloroethene', 20.00) VALUES (4, 'MW2', 'Chloroform', 1.00) VALUES (5, 'MW2', 'Methylene Chloride', 1.00) VALUES (6, 'MW6S', 'Acetone', 1.00) VALUES (7, 'MW6S', 'Dichloroethene', 1.00) VALUES (8, 'MW6S', 'Trichloroethene', 1.00) VALUES (9, 'MW6S', 'Chloroform', 1.00) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00 VALUES (11, 'MW7', 'Acetone', 1.00) VALUES (12, 'MW7', 'Dichloroethene', 1.00) VALUES (13, 'MW7', 'Trichloroethene', 1.00) VALUES (14, 'MW7', 'Chloroform', 1.00) VALUES (15, 'MW7', 'Methylene Chloride', 1.00 VALUES (16, 'TripBlank', 'Acetone', 1.00) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00) VALUES (19, 'TripBlank', 'Chloroform', 0.76) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51) GO //////////Parse/////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'. ////////////////Execute//////////////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'.
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, Bryan Ax
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent Type_1 is 2nd Parent Type_2 is 3rd Parent Type_3 is 4ed Parent
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
