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)
Hi all, I am trying to do a very basic ALTER Command and am trying to change its DEFAULT value. Code below is what I currently have:
Code Snippet
ALTER TABLE Table_1
ALTER COLUMN TEST VARCHAR(1000) NULL DEFAULT 2
Thanks, Onam.
*UPDATE* I found this code but are there alternative methods? Additionally, if I was to update its DEFAULT value again how would I go about doing that? Do I first have to remove the CONSTRAINT and then run the command?
Code Snippet
ALTER TABLE Table_1 ADD CONSTRAINT DF_Albumns_Comment DEFAULT 2 FOR TEST
Hi I want to create a table with one column, which is a identitycolumn.Let's say like this:CREATE TABLE DefaultTable(N int identity(0,1))Then I want to fill this table with 15,000,000 records, so that I havea table with the numbers 0 to 14,999,999.How can I do this as fast as possible. A standard INSERT would take along time.(It can be a temp table or a table variable. I just need a list withnumbered 0 to 15,000,000)Thank you.Gidon
I went ahead and installed RS even though it was clear Microsoft install was confused (re: preivous post). Now I get to experience the thrill of configuration hell.
Report Server Virtual Directory had a green check. Unfortunately the Name block says <Not Set>. When I try to create a New... default directory I get the error message: "The virtual directory could not be created. The previously set virtual directory will still be used."
Does anyone know where the <Not Set> virtual directory is stored on the computer?
addl info:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified already exists. Specify a different name.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)
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
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 ?
I'm trying to create a login in the default domain. I know I can pullthis information from xp_loginconfig, but don't see how I can use it inthe context of sp_grantlogin.For example, pull the domain the user is currently logged in on andinsert it into the sp_grantlogin script. Has anyone ever done this inthe past?
This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.
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
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
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)
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?
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
I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005? -Mathminded
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?
I've never had to do this, but when I downloaded the Web Workflow Approvals Starter Kit, it requested that I install the database into a User Instance of .SQLEXPRESS.
Now the problem is, I've installed it onto a default instance, so I was wondering whether you can create a named instance on top of a default instance... and if so, how would you do that?
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.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
Hi, Does anyone have a workaround or know of a fix to this problem: Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok. However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
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:
