New To SQL - Create Table Command Question
Jun 22, 2007
hi
can someone please explain the following please
why does this fail with specified owner testdb does not exist or you do not have permissions
create testdb.testtable (
testval int
)
yet if i create testdb and then run the following against this new db
create testtable (
testval int
)
command command completes successfully......what am i not understanding or doing wrong?
thank you
View 3 Replies
ADVERTISEMENT
Jul 25, 2006
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.
It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 8: Incorrect syntax near 'GO'.
'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the variable '@usb'.
Must declare the variable '@usb'.
Must declare the variable '@i'.
A RETURN statement with a return value cannot be used in this context.
Line 89: Incorrect syntax near 'GO'.
Line 91: Incorrect syntax near 'GO'.
Line 94: Incorrect syntax near 'GO'.
Protected Sub Install()
Dim err As String = ""
While err.Length < 1
' Dim your StreamReader
Dim TextFileStream As System.IO.TextReader
'Load the textfile into the stream
TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql")
'Read to the end of the file into a String variable.
executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful"
End While
If err = "Susscessful" Then
Response.Redirect("Default.aspx")
Else
Me.lblError.Text = err
End If
End Sub
Private Function executesql(ByVal s As String, ByRef err As String) As Boolean
Try
Dim conn As New Data.SqlClient.SqlConnection(GenConString())
Dim cmd As New Data.SqlClient.SqlCommand(s, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
err = ex.Message.ToString
Return False
End Try
End Function
Example sql file
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MyFunc]
GO
CREATE FUNCTION [dbo].[MyFunc]
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(1000)
-- Add the T-SQL statements to compute the return value here
-- Do something here
-- Return the result of the function
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 1 Replies
View Related
Sep 23, 2004
Hi,
Please help
The following command works ok:
create default [Zero] as 0
but the command:
if not exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Zero]')
and OBJECTPROPERTY(id, N'IsDefault') = 1)
create default [Zero] as 0
returns:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'default'.
Env: Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thanks
Helena
View 3 Replies
View Related
Aug 22, 2007
sqlplus for Oracle has a CREATE DATABASE LINK command. What is the equivalent functionality for MSSQL using sqlcmd or other script mechanism.
View 1 Replies
View Related
Mar 21, 2008
Hi All,
I have a requirement to create a dynamic SQL Command in an OLE DB Source due to the fact that I need to read data from another database based on a date range. For example, the SQL command would look like
SELECT * FROM Table1 WHERE DateField BETWEEN '17/03/2008' and '21/03/2008'
and I need to change the dates - '17/03/2008' and '21/03/2008' to different dates when the package is deployed in production, how do I do that ?
Regards
Ash
View 12 Replies
View Related
Jan 15, 2005
Hi
Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??
If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts
How Can I do that .. ??
And thanks with my regarding
Fraas
View 1 Replies
View Related
Sep 7, 2006
This script will read the contents of a DB backup file, and generate a restore command.
Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.
This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.
Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.
-- Create Restore Database Command from DB Backup File
set nocount on
go
declare @backup_path nvarchar(500)
select @backup_path =
-- Path to Backup file
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
create table #header (
BackupNamenvarchar(128)null,
BackupDescriptionnvarchar(128)null,
BackupTypeintnot null,
ExpirationDatedatetimenull,
Compressedintnot null,
Positionintnot null,
DeviceTypeintnot null,
UserNamenvarchar(128)not null,
ServerNamenvarchar(128)not null,
DatabaseNamenvarchar(128)not null,
DatabaseVersionintnot null,
DatabaseCreationDatedatetimenot null,
BackupSizedecimal(28,0)not null,
FirstLsndecimal(28,0)not null,
LastLsndecimal(28,0)not null,
CheckpointLsndecimal(28,0)not null,
DatabaseBackupLsndecimal(28,0)not null,
BackupStartDatedatetimenot null,
BackupFinishDatedatetimenot null,
SortOrderintnot null,
CodePageintnot null,
UnicodeLocaleIdintnot null,
UnicodeComparisonStyleintnot null,
CompatibilityLevelintnot null,
SoftwareVendorIdintnull,
SoftwareVersionMajorintnull,
SoftwareVersionMinorintnull,
SoftwareVersionBuildintnull,
MachineNamenvarchar(128)not null,
Flagsintnull,
BindingIDuniqueidentifier null,
RecoveryForkIDuniqueidentifier null,
Collationnvarchar(128)null,
Seqintnot null
identity(1,1),
)
create table #filelist (
LogicalNamenvarchar(128)not null,
PhysicalNamenvarchar(128)not null,
Typenvarchar(10)not null,
FileGroupNamenvarchar(128)null,
Sizedecimal(28,0)not null,
MaxSizedecimal(28,0)not null,
Seqintnot null
identity(1,1),
)
insert into #header
exec ('restore HeaderOnly from disk = '''+@backup_path+''' ')
insert into #filelist
exec ('restore FilelistOnly from disk = '''+@backup_path+'''')
declare @tab varchar(1), @cr varchar(2)
select @tab = char(9), @cr = char(13)+Char(10)
select
[--Restore--]=
case
when a.Seq = 1
then
@cr+
@cr+'restore database '+c.DatabaseName+
@cr+'from disk ='+@cr+@tab+''''+
@backup_path+''''+@cr+'with'+@cr
else ''
end+
@tab+'move '''+a.LogicalName+
'''to '''+a.PhysicalName+''' ,'+
case
when a.Seq = b.Seq
then
@cr+@tab+'replace, stats = 5 , recovery'
else ''
end
from
#filelist a
cross join
( select Seq = max(b1.Seq) from #filelist b1 ) b
cross join
( select DatabaseName = max(c1.DatabaseName)
from #header c1 ) c
order by
a.Seq
go
drop table #header
drop table #filelist
Results, modify as needed:
--Restore--
--------------------------------------------------------------------
restore database MY_DB
from disk =
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
with
move 'PRIMARY'to 'D:MSSQLDATAMY_DB_PRIMARY.MDF' ,
move 'DATA1'to 'D:MSSQLDATAMY_DB_DATA1.NDF' ,
move 'DATA2'to 'D:MSSQLDATAMY_DB_DATA2.NDF' ,
move 'LOG'to 'D:MSSQLDATAMY_DB_LOG.LDF' ,
replace, stats = 5 , recovery
CODO ERGO SUM
View 4 Replies
View Related
Aug 3, 2012
Any command which will make create script for my entire database including all sp's , tables, functions..i dont want to backup the db ..i want to create scripts for creating an entire database schema (only the structure and not data)
View 1 Replies
View Related
Apr 24, 2007
Scenario:
I have installed SQL Express 2005 and I want to create a new DB using sqlcmd.
I am running a command like: sqlcmd -i file.ini (I am logged like Administrator of PC)
the content of the file.ini is:
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = 'c:Program FilesMyTestdatabase'
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE DataCollection ON
( NAME = DC_dat,
FILENAME = '''+ @data_path + 'DC.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = DC_log,
FILENAME = '''+ @data_path + 'DClog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
) ;
GO
I obtain this error:
Msg 1802, Level 16, State 4, Server TESTQ5SQLEXPRESS, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server TESTQ5SQLEXPRESS, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'c:Program FilesMyTestdatabaseDC.mdf'.
If I change the security of directory database (I add "modify" permission to Users windows group) the error disappear.
I am logged like Administrator... why is it necessary to change directory permission?
View 9 Replies
View Related
Sep 12, 2007
Hi all,
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...
2007-09-12 12:05:46.58 User-specified agent parameter values:2007-09-12 12:05:46.58 --------------------------------------2007-09-12 12:05:46.60 -Publisher EDOM04SQLstandard2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest2007-09-12 12:05:46.60 -ReplicationType 22007-09-12 12:05:46.60 -Distributor EDOM04SQLstandard2007-09-12 12:05:46.60 -DistributorSecurityMode 12007-09-12 12:05:46.60 -PublisherSecurityMode 12007-09-12 12:05:46.60 --------------------------------------2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04SQLstandard'2007-09-12 12:05:46.96 The replication agent had encountered an exception.2007-09-12 12:05:46.96 Source: Replication2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command. Contact your system administrator.2007-09-12 12:05:46.96 Message Code: 142602007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
View 6 Replies
View Related
May 9, 2010
Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
View 2 Replies
View Related
May 3, 2004
Hello all,
Please help....
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)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Aug 4, 2004
Hi All,
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:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Jul 20, 2005
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.
View 1 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Nov 21, 2006
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.
View 6 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Jan 27, 2006
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
View 2 Replies
View Related
Dec 19, 2007
i have a question if anyone can help please..
i have need to create a database table as a go-between, where users will upload info. then on a backend page admin will make some data settings, then transfer it to the proper, permanent tables.(all this to avoid expecting users themselves to upload files to proper places).
can i use a basic SQL INSERT statement to move from one table directly to another without the use of parameters? for example,
INSERT INTO tblOne (fieldOne,fieldTwo,fieldThree) VALUES (tblTwo(fieldOne,fieldTwo,fieldThree)
if so, what is the syntax? and if not, then what would i have to do, just use some go between variables or objects? perhaps a DataSet to pull the values, then use same DataSet to load the values into new table, then a simple DELETE statement on the intermediate table?
i mean, i can work my way through this, but if anybody has gone through a similar situation i sure could use any pointers or tips to keep it as clean and simple as possible.
thanks all!
View 3 Replies
View Related
Apr 7, 2008
In query analyzer, what is the command to tell me the attributes of the entities in a table? In oracle I can use the describe command. I know their is a way to do it in Query analyzer but I can't remember how. Also I can look visually by expanding the node of the table. But if I can do this through the command line in query analayzer, it is sometimes quicker.
Example. I want to find out about a table named "Employee"
What command would I type that would tell me all of the columns/attributes in that table, and the data types which they are?
Bill
View 2 Replies
View Related
Mar 27, 2006
Hi all,
I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).
Anyone know how to get this done.
Please advice.
Thanks and appreciated.
View 4 Replies
View Related
Jun 3, 2008
hi
Can We Use Truncate Command on a table which is Refernced by Foreign Key?
View 5 Replies
View Related
Nov 22, 2007
Hi AllAm new to sql server to sobear with me, have checked around but cantfind an answer to this.I want to change fieldname from nvarchar(50) to nvarchar(255) as partof a scriptAm running this command :ALTER TABLE MYTABLE MODIFY fieldname nvarchar(255) nulli test this out in the sql window but cant get it to run error is:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'MODIFY'.Can anyone help ?
View 1 Replies
View Related
Jul 20, 2005
Hi,I'm trying to run the ALTER TABLE command using a dynamic string for thetable, like so:DECLARE @TableName CHARSET @TableName = 'Customers'ALTER TABLE @TableNameADD ...blahIs this possible? We know this works:ALTER TABLE Customers ADD ...blahIt looks like I need a way to convert the CHAR value to a literal or perhapseven a table ID?Thanks in advance,Paul
View 3 Replies
View Related
Sep 8, 2006
hi! can i create a table based on a join command? if it is how could it be done?
thanks!
View 2 Replies
View Related
Dec 5, 2007
I have several databases that are used to collect data for differentclient-projects. My intention was to collect the data, and administermySQL so each client could see only their data. Each of thesedatabases used to collect data have the same tables. There are otherdatabases which are available to all the users which contain commoninformation.The command "show databases" lists all of the databases (visible tothe particular user) and not the one's I would like to display (thedatabases used to collect data). Is there a way to nest a singlemySQL command to show only the databases that have the same particulartable?
View 1 Replies
View Related
Sep 21, 2006
Hi,
I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.
The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.
Could this be due to a table-locking issue? Any suggestions?
Thanks
ray
View 6 Replies
View Related
Mar 22, 2004
I want to ask something because i need.
Any SQL/T-SQL command inside MsSQL Server 2000 can show the description of {all table columns or specified table columns} of specified table inside specified database?
can you teach me how to do and any example(s)?
View 2 Replies
View Related
Aug 24, 2000
Hi
Does anyone know how i can grant select permission on more than one table in the same database using a single grant commmand....its painful to use grant statement seperately on each table
thanks and regards,
reshma
View 3 Replies
View Related
Apr 15, 2004
is there a command in ms sql server 2000 equivalent to this oracle table copy command?
create table myTable_bak as select * from myTable;
View 1 Replies
View Related
Nov 19, 2014
I am debugging a DB maintance script which creates a table of index maintainance commands which are created separately for each index according to the level of fragmentation and other factors.
For the debugging process, I'm looking for a way to parse each command in the table without actually running them to locate any syntax errors. In other words, as if you clicked the blue check on each one.
Does such a function exist in SQL 2008 (the version I'm doing this on) or other versions?
View 3 Replies
View Related
Sep 16, 2009
I have a primary key named pk, name and surname fields. I need to insert to my table names and surnames.
INSERT INTO People (name,surname) VALUES ('john','black');
I'm not giving pks database gives is auto. But my problem is i need to know the pk that my database gave. Because i have lots of duplicate records. Is there any way to retrieve pk while inserting to table.
View 7 Replies
View Related
