When I right click on a table and click Script Table as --> Create to --> it scripts out:
CREATE TABLE [dbo].[Test](
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Address] [varchar](50() NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Test] ADD [City] [varchar](50) NULL
Why is there an Alter table statement? I’m assuming this table was altered at some point but why is SQL not just doing a create table and how is it keeping track of these changes?
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
Im trying to insert the values from this query into a table, so I can later check the history of memory usage:
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB] ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB] ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB] ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB] ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB] ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB] ,[system_memory_state_desc] AS [System_Memory_State_Desc] FROM [master].[sys].[dm_os_sys_memory]
What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.
I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql -- saved in C:/Documents/SQLServerIndexes_downloadCode -- 26 May 2015 10:52 AM USE ScottChangDB; GO CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL DROP TABLE #Pyr GO SELECT vst_int_id, [4981] AS Primary_Ins, [4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.
USE [Work_Allocation] GO /****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spUser_Parameters_update]
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2 ( [PART] ASC, [ORG] ASC, [SPLRNAME] ASC, [REPITEM] ASC, [RFQ] ASC,
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
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:
NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:
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
Can someone point me to any whitepaper on how to use a Transact SQL script to create an SSIS package. I will need to be able to run the script at various customer sites.
Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this
Create Table [dbo].[BuildingInformation] ( ID INT IDENTITY NOT NULL, BuildingName varchar(50), RAName varchar(50)
[code]....
Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA]. If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change. If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test (esn nvarchar(35), dateaudit datetime, opid int) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5) insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
I have a Java application (APP) that use SQL Server 2008 - 20014. In the SQL server there can be more than my database. During setup of the database the first user (ex:ADM) is created using MS SQL Server Management Studio (SMS). ADM can then launch the APP with proper credentials and connect to the database.ADM can then, in the APP, create new users for the APP and database. After some testing for proper CREATE and GRANTS I use the following code to create a new Create, Read, Update, Delete (CRUD) user for the APP and database: CREATE LOGIN testuser WITH PASSWORD='password', DEFAULT_DATABASE = testdb
USE testdb CREATE USER testuser FOR LOGIN testuser GRANT AUTHENTICATE TO testuser WITH GRANT OPTION GRANT CONNECT TO testuser WITH GRANT OPTION GRANT ALTER ANY USER TO testuser WITH GRANT OPTION GRANT CONNECT SQL TO testuser WITH GRANT OPTION GRANT INSERT,SELECT,UPDATE, EXECUTE, DELETE TO testuser WITH GRANT OPTION
USE master GRANT CONTROL SERVER TO testuser
USE testdb..When I the use the SMS and look at security the "testuser" is only added to testdb. To test I create a second database "testdb2" --> launch the APP to connect to testdb2 and can login with "testuser".I don't know if I give to much grants or missing one...proper TSQL so new users only have CRUD access to "testdb" and no other database on the SQL server.
I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.
How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT CASE GROUPING([Source]) WHEN 1 THEN 'ALL' ELSE [Source] END AS 'Input Source', avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp) GROUP BY [source] WITH ROLLUP
I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.
I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays. This is the syntax I have to start with, but it is presenting with an error:
Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.
What should I alter?
CREATE TABLE dbo.CreateCustomCalendar ( wk_Date INT IDENTITY NOT NULL, FullDate DATETIME NOT NULL, WeekDayName VARCHAR(9) NOT NULL, IsWorkday varchar(20) NOT NULL )
I need to create a T-sql script to create logins in bulk so users can access views on the database. Usernames as used in the local domain are all stored in a table.
Users already exist in the domain (let’s say: MYDOMAIN)ADSI.dbo.Ad.username is the column wich contains the usernames as used in the Active Directory domain (+/- 350)Script needs to check if login already exists to make sure login is not added twice.If possible give all these users db_datareader role, in let’s say: MYDATABASE
This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort
Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result] From foobar As fb Inner Join chezter As cz On fb.username = cz.username and [fb].[Last Result] = [cz].[Last Result]
