Create Database From Code
Sep 13, 2006
Hi, I want to create a database setup on a server. I've scripted my database from sql server express. I've tested the code in the query window and it worked. When i pasted the same code in a sqlcommand command text....the debuger threw an sql exception....saying the sintax is wrong near keywords GO, USE, some forgein keys, and so on. Here is a chunk of the script.
USE [master]
GO
/****** Object: Database [estate_management] Script Date: 09/13/2006 09:19:32 ******/
CREATE DATABASE [estate_management] ON PRIMARY
( NAME = N'estate_management', FILENAME = N'D:MSSQLestate_management.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'estate_management_log', FILENAME = N'D:MSSQLestate_management_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[admin](
[admin_id] [int] IDENTITY(1,1) NOT NULL,
[log_in_id] [varchar](20) NOT NULL,
[password] [varchar](50) NOT NULL,
CONSTRAINT [PK__admin__07020F21] PRIMARY KEY CLUSTERED
(
[admin_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 1) ON [PRIMARY],
CONSTRAINT [IX_admin] UNIQUE NONCLUSTERED
(
[admin_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
Thanks for your time.
View 1 Replies
ADVERTISEMENT
Oct 2, 2007
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
View 4 Replies
View Related
Aug 12, 2007
Hi
I developed a program. This program will use a SQL server database. How can I create the database using code. I think I need to use sql scipt like this:
create database customers
But where should I write the script and How can I make VB code implement the script.
thank
View 7 Replies
View Related
Jun 14, 2006
Hi,
Is it possible to shrink a DB thru .NET.
Presently I have to go into the SQL server 2005 studio to shrink the DB.
To shrik the DB I right click on the DB and select all tasks -> Shrink -> DB
How would I do this thru SQL codse?
I would like to do it thru my VB.NET code as you would compact an ACCESS DB.
The reason I want to set it thru my code is :
The clients that use my program have no clue of
SQL or for that matter computer itself.
I just want them to click on a button on my application which should
do the shrinking.
Similarly for backup of the DB.
Tnx
View 1 Replies
View Related
Dec 14, 2006
In the SQL Server Technical Article Microsoft .NET Data Provider for mySAP„¢ Business Suite
is a Code example of how to invoke a BAPI in .Net Code. First line shows how to create a Connection to SAP, but there is no information in which lib the SAPConnection type is stored:
SAPConnection con = new SAPConnection("ASHOST=<SAPserver>; CLIENT=<client>;SYSNR=<sysnr>;USER=<user>;PASSWD=<password>;LANG=<logon
language>");
con.Open();Does somebody can help me?
View 3 Replies
View Related
Sep 20, 2007
Hi
I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.
My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...
I have tried to find a solution but so far, no luck.
Anyone have any ideas?
/F
View 3 Replies
View Related
Jun 2, 2006
I have some SQL code as inline SQL (bad habit, I know). Now I want to convert this to an sproc, but I'm pretty much out of ideas here. The code looks like this:
string SQL = "SELECT * FROM MyDBTable WHERE 1=1";
if (txtMyField1.Text != "")
{
SQL = SQL + " AND MyField1 = @MyField";
}
if (txtMyField2.Text != "")
{
SQL = SQL + " AND MyField2 LIKE '%'+ @MyField2 + '%'";
}
if (txtMyField3.Text != "")
{
SQL = SQL + " AND MyField3 LIKE '%' + @MyField3 + '%'";
}
I have an search page built on ASP.NET 2.0. Based on what the user has entered to the form fields, the SQL in constructed on the fly. Since this is now inside codebehind file (aspx.cs), I want to get rid of it and move it to an sproc. But the question is how ? Some simple SQL clauses are easy to convert to an sproc but this is causing me lots of issues.
View 4 Replies
View Related
Jan 17, 2006
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
View 3 Replies
View Related
Jan 23, 2006
I have been reading up on everything I can find on this subject and I am not clear if this is allowed within the SQL Server 2005 CLR. My function calls work within a Windows form project, but don't run when invoked from a SQL function. I don't get any errors or warnings when creating the assembly and functions within SQL Server, but when running via a select statement, the spid just hangs and I have to stop & restart the service to kill the process. I have been investigating the security settings for this assembly, but I think I have that covered via the RunTime Security Policy settings in the .NET Framework 2.0 Configuration tool.
Any insights, knowledge, or thoughts would be greatly appreciated.
Barry
View 1 Replies
View Related
May 5, 2015
My Execute SQL Task will store the file name into a variable(mFile) of type string datatype.
Now I wanted a script task (C# code) to create a filename from the variable (mFile) value.
Since it is a common issue I tried a lot in the internet but none of the queries worked.
View 10 Replies
View Related
May 21, 2015
Below is code I am using to create a heatmap in SSRS. THe code seems to be working well except for one glitch. Sometimes numbers in the "middle of the pack" are white. Obviously they should be one the of colors listed below. Why numbers would be white when I declared only white for IsNothing.Â
 Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
    Dim colours As String() = New String() {"White","DarkGreen","Green","ForestGreen","LimeGreen","YellowGreen","Yellow","Gold","Orange","DarkOrange","OrangeRed","Red"}
        If IsNothing(textBoxValue) = True Then
      Return colours(0)
[Code] ....
View 6 Replies
View Related
Jan 20, 2012
I have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies
View Related
Jun 25, 2015
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
drop table Test
[Code] ....
I have this Query and the below output:
EDate Code CDate Price
2015-06-24Â Â RXÂ 20150701Â 22
2015-06-24Â Â RXÂ 20150701Â 28
2015-06-24Â Â RXÂ 20150701Â 43
[Code] ....
Now the task is to create SSIS package which will create different .txt file for each Code
1) RX20150624.txt
2015-06-24 00:00:00.000Â RXÂ 20150701Â 22
2015-06-24 00:00:00.000Â RXÂ 20150701Â 28
2015-06-24 00:00:00.000Â RXÂ 20150701Â 43
2) NG20150623.txt
2015-06-23 00:00:00.000Â NGÂ 20150701Â 43
3) HO20150624.txt
2015-06-24 00:00:00.000Â HOÂ 20150701Â 43
And so on..
But the requirement is to have a dynamic query where we can have more number of Codes or less number of codes and similarly the package should generate dynamic text files, one .txt file per code. What is the best way to create a package which can meet the above requirement?
View 6 Replies
View Related
Apr 6, 2007
Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks
View 2 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
Mar 26, 2008
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.
Any help would be appreciated. Thanks!
View 9 Replies
View Related
Feb 14, 2008
Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
View 6 Replies
View Related
Oct 18, 2006
Dear friends,
I encounter a serious problem.
I would like to develop an application that can create Data Mining structures and a mining model in SQL Server 2005 with VB.NET. I tried the code from book Data Mining with SQL server 2005 in chapter 14 but did not work. Any good idea?
Please help me.
Best regards,
Manolis
View 5 Replies
View Related
Oct 20, 2006
Thank you very much for your help.
The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously
I tried the code but initially I have encounter the following problems.
1. In any line that have the declaration As Server, As Database like in
Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.
2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.
3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'.
I have some more problems but I thing that by solving the above that I referred I will solve the rest.
Thank you any way.
Best regards,
Manolis
PhD student
View 1 Replies
View Related
Nov 8, 2007
I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?
Thanks
View 6 Replies
View Related
Nov 29, 2007
VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:
SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @Username)
The name of the data source is SqlDataSource2
Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?
View 16 Replies
View Related
Apr 26, 2008
What method would you suggest is the best way to deal with a website?
Say for example, writing SQL. Am I better using Stored Procedures and adding methods for accessing there or just writing the SQL inline. What do you think about this? Is it totally overkill to use stored procedures in a small website?
View 6 Replies
View Related
Jun 6, 2008
Hi Friends, Please any one help me in writing the code in thisI have 3 fileds1)empId-->textbox2)Roles-->4 radiobuttons(MN,PL,TL,CL)3)Responsibilities--->3 check boxes(profile,register,change password)I have 3 tablestable1:RoleMasterTable:roleID roleName1 MN2 PL3 TL4 CLtable2:ResponsibilityMasterTableresId resName1 profile2 register3 changepasswordtables3MasterTable:empId roleId resIdthe form conatains the empId,Roles(radiobuttons),Responsibilies(chechboxes)for example I have to enter empId=1select one radiobutton that is "PL"and I can select check boxes profile,register and manyafter submitting the button("submit) these details has to store in the database table i.e master tableI have to show the final o/p like this in the tableMasterTableempId roleId resId1 2 1,2Please any one help in this I am beginner in database c# programmingPlease its very helpful to me............Thanks & RegardsGeeta
View 7 Replies
View Related
Dec 30, 2005
i have a piece of code in an adp that is currently behind a form but i could put it in a module and make it public if i need to. how can I use DTS to run this piece of code? which connection and task could i use. can this even be done? i have little experiance with DTS.
thanks.
justin
View 1 Replies
View Related
Dec 15, 2007
Where am I going wrong? I have no idea where to start to look for any incorrect code because it all looks ok! Please help!!!
I am entering a record into 5 textboxes - I can see the new record when I open the datagrid view, but when I close the programme and reopen it the record disappears, this code looks ok to me so where is it wrong? Obviously the record is not being written to the database so that when I reopen the programme there is nothing to fill the dataset..........any ideas?
Your help is appreciated!!!
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Mainform.SqlConnection1.Open()
Dim TheNewRow As DataRow
TheNewRow = Mainform.Dataset1.Tables("ChartTable").NewRow
TheNewRow("Chart Number") = TB1CN.Text
TheNewRow("Chart Title") = TB2CT.Text
TheNewRow("Edition Date") = TB3ED.Text
TheNewRow("Print Date") = TB4PD.Text
TheNewRow("Edition Number") = TB5EN.Text
Mainform.Dataset1.Tables("ChartTable").Rows.Add(TheNewRow)
Mainform.SqlDataAdapter1.Update(Mainform.Dataset1)
Mainform.SqlConnection1.Close()
Me.Visible = False
End Sub
Regards,
Steve
View 4 Replies
View Related
Mar 6, 2008
I have the need to detach a database from code, at the moment I am using 'SSEUTIL -d' for this in my code and it works OK but now I want to change to using an SQLCommand. Here is my code so far:
Code Snippet
Dim conDatabase As SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=MyMDFPath"; Integrated Security=True;Connect Timeout=60;User Instance=True")
Dim comDetach As New SqlCommand("EXEC master.dbo.sp_detach_db '" + MyMDFPath + "'", conDatabase)
conDatabase.Open()
comDetach.ExecuteNonQuery()
conDatabase.Close()
However, when I execute this I always get the following exception:
Code Snippet
Cannot detach the database 'MyMDFPath' because it is currently in use.
It seems obvious to me that the problem is that to do the detach I create a connection - how do i get around this?
Thanks!
View 1 Replies
View Related
Jul 6, 2006
Hello.
I think its silly question but i expect some solution .
Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.
Please, if somebody have some solution then guide me in detail.
Thanks in adv.
View 12 Replies
View Related
Sep 10, 2012
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?
View 1 Replies
View Related
Jul 15, 2015
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.
View 6 Replies
View Related
Feb 20, 2007
Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
View 1 Replies
View Related
Feb 17, 2007
Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,
I am running windows Vista Business, SQL SERVER 2005
so what going on here
View 23 Replies
View Related