Create Fields In Tables If They Do Not Exist In Database

Mar 31, 2008

Hello.

I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.

Could anyone help me construct this. Im pretty novice at SQL.

Thanks.

View 7 Replies


ADVERTISEMENT

How Do You Create ##Temp Tables If They Don't Exist, Use Them If They Do?

Aug 30, 2006

I have two SPs, call them Daily and Weekly. Weekly will always callDaily, but Daily can run on its own. I currently use a global temptable because certain things I do with it won't work with a local temptable or table variable.I have been trying to get code so that if the table already exists, itjust keeps going and uses it, but creates the temp table if it doesn'texist. Unfortunately, no matter how I try to do it, it always attemptsto create it, raising an error and breaking the code.create table ##load_file_log (id int identity(1,1),contentsvarchar(1000))insert into ##load_file_log (contents) values ('test record')IF object_id('tempdb..##load_file_log') IS not NULLprint 'exists'ELSEcreate table ##load_file_log (id int identity(1,1),contentsvarchar(1000))select * from ##load_file_logdrop table ##load_file_logIf I change it to IS NULL, the same error occurs (Server: Msg 2714,Level 16, State 1, Line 7There is already an object named '##load_file_log' in the database.)I have found one way to do it, but it seems a bit...clunky.IF object_id('tempdb..##load_file_log') IS NULLexec ('create table ##load_file_log (id int identity(1,1),contentsvarchar(1000))')I'll use that for now, but is there something I missed?Thanks.

View 4 Replies View Related

Create A Link Between Fields Of Two Tables

Jan 24, 2007

Amir writes "Dear Team Members;

Im writing a program in ASP language which it is using SQL Server for database.

I want to know how to create a link between fields of two tables. For example:
I have a table named: Projects containing:
p_name
status
date
member1
member2

and another table named results containing:
p_name
s_status
status1
status2

I want to create a link between field s_status of STATUS table and field status of table Projects

Users are sending data to table projects, when a user send some data to status of projects, I want this link to send data from status in project to s_status of results.

Could you please help me how to do it?

Regards.
Amir Zandi"

View 1 Replies View Related

How Do I Create A Composite Primary Key, Using Fields From Multiple Tables?

May 22, 2007

Hi All



I hope someone smart can help me, it would be highly appriciated.



I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.



TblOrders: OrderNum, Orderdate, ....

TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress



The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.



Can this be done, and how.



Many thanks



Kind regards

Tina

View 3 Replies View Related

Return A List Of New Tables Or Columns Which Do Not Exist In A Database

Mar 24, 2008



What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.

Server A and Server B are linked servers.

I am using SQL Server 2005. Thanks.

View 1 Replies View Related

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

Feb 5, 2007

From Newbie to Newbie,



Add reference to:

'Microsoft ActiveX Data Objects 2.8 Library

'Microsoft ADO Ext.2.8 for DDL and Security

'Microsoft Jet and Replication Objects 2.6 Library

--------------------------------------------------------

Imports System.IO

Imports System.IO.File





Code Snippet

'BACKUP DATABASE

Public Shared Sub Restart()

End Sub



'You have to have a BackUps folder included into your release!

Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo


addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"


'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""



Try

File.Copy(z, f)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try



MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub






Code Snippet

'RESTORE DATABASE

Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String


With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName



'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With


'Notify user
MsgBox("Data restored successfully")


Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub








Code Snippet

'CREATE NEW DATABASE

Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;

Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table



'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet

OLEDB:Engine Type=5")


'Open the Catalog
Cat.ActiveConnection = Cn



'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"


'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable



Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")


'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)



'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)



'User notification )
MsgBox("A new empty database was created successfully")


'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing


'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If

End Sub








Code Snippet



'COMPACT DATABASE

Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine


'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program

FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")


'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")


'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")


'User notification
MsgBox("The database was compacted successfully")

End Sub

End Class

View 1 Replies View Related

Database Tables And NULL Fields

Nov 17, 2006

Hi  there,
 I have a fairly generic question regarding database design.
I am working  on my first database using SQL  Express.
I am testing the database by inserting records into the pre  defined tables.
Sometimes I  forget to input text into a non null  field. When
this  happens  it displays an error  and then skips a  few rows
which makes my  row numbering  messy.  For example  say I have 
Row  1, Row 2 Row  3 all  nicely  formatted but on Row 4  I make  a mistake
this  row then disapears and  their is  gap  between  3 and 5 where  4  was.
Although I am just testing, I was  wondering  what happens in a  real
scenario if an inexperienced data input person makes a few  mistakes it
makes the database  numbering  systems look bad. In  this case
is it common  to  make  most  fields  NULL to allow for admin errors??
 
 
 

View 8 Replies View Related

SQL Server 2012 :: Getting All Fields From All Tables In Dynamics CRM 2011 Database?

Nov 5, 2014

Doing a data migration from one CRM to another and need to get a listing of all entities in current CRM, together with fields and field types. OK, I got the XRMToolBox which gives me that, but I'm hoping there is a sql tool out there that will do the same, plus give me a count on each field of the number of entries in that field in the database.

View 0 Replies View Related

Checking If Fields Exist

Mar 14, 2007

Hello all.

I am going to be writting a script to add columns to a given table. Can anyone advise me how i check if the coulmn exists in a table before i modify it?

Cheers

View 7 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Update If Exist-one Table To Another From Tow Date Fields- Single Row For Each Day

Apr 20, 2008

question need help
how can i use this code below not for insert
i need it for update another table but only if exist
the link to the code in this FORUM
http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True






Adam Haines wrote:









GPS,



Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.



Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.

Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.


Now the code to get the results you need:



Code Snippet
declare @t table(
id int,
fname char(4),
Start_Date datetime,
End_Date datetime,
val_holiday int
)
insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 )
insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3)
insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4)
insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )

select id, fname, cal.ISODate, val_holiday
from @t t1

inner join Calendar cal
on cal.isodate >= t1.start_date and
cal.ISODate <= t1.end_date







is possible to do it
TNX

View 4 Replies View Related

Create Tables Within A New Database

Jan 20, 2012

I'm attempting to use SQL statement to create tables within a new database, and I'm somewhat lacking in experience for this. Normally I'd simply create them through the GUI, but I want to get the hang of DDL statements.

I have written what I think are scripts to create one table with a primary key, create a unique index on it, and then create another table with a primary key and constrain two columns in the second table to the non-primary unique index in the first.

Code:
Use SKUTracking
CREATE TABLE
SKUTracking.dbo.tblOraUnits

[code]...

View 7 Replies View Related

Create Tables For A Database?

Jul 16, 2014

Create a Database with the following:

a) Database to contain a list of people, their addresses, email addresses, and phone numbers.

b) Database to contain a list of cars, their names, their type (2 doors,4 doors, etc.), and their colour.

c) Database to allow only one owner per car

d) Database to allow multiple people to be able to act upon behalf of one car. (Disclosure of MOT details, request change of parts, etc)

View 4 Replies View Related

How To Create Database And Tables In MSDE

Sep 25, 2005

I have install the MSDE from Ofiice 2000 cd and my MSDE sql server is running fine that I can see from my tray bar with green arrow button. Now i want to create the database so can any body tell me how do i create database. Is there ne query analyser or enterprise manager by which I can create database. Also when i try to install starterkit it doen not allow me to install it. It ask me to select database from the dropdown list and when I select Localhost (only option available) and click on test connection it gives me error UnsuccessfullI am new at MSDN so please help me.

View 4 Replies View Related

How To Connect To SQL Database, Create Tables

Jan 2, 2006

Hi All, 1st of all happy New Year to all asp.net forum members
    I am new at asp.net. I want to design a website
using asp.net as frontend and sql database as backend. I am able to
connect and add,update as well delete records when I use MsAcess and
Asp.net using the following connection strings...
**********
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("/database/northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers where city LIKE 'Berlin' order by city ASC"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
**********

But when I try to connect to sql database using the following connection strings I am unable to do so...

**************
SqlConnection myConnection = new SqlConnection("server=PLATINUMVSdotNET;database=pubs;Trusted_Connection=yes");
        SqlDataAdapter myCommand = new SqlDataAdapter(" * from Authors", myConnection);

        DataSet ds = new DataSet();
        myCommand.Fill(ds, "Authors");

        MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
        MyDataGrid.DataBind();

**************
I have written the servername as "PLATINUMVSdotNET" because when I
installed SQL SERVER 2000 I found a tray icon where the server name was
displaying the same (my computer name is PLATINUM).
When I used webmatrix I enterd the same server name and  windows
authentication I was able to create a database but How to create
table...

Please help me out
Thanks in advance...

View 4 Replies View Related

How To Create Multiple Tables In A Database

Mar 6, 2008

I am trying to create multiple tables in a database using a SQL Script. First i want to find all the tables that have an Identity Column as a primary key in the database. Then for all the tables that meet the Identity Column and primary key criteria, I want to create a New Table.

For example if the Orders table has a Identity Column as a primary key, I want to Create a New Table called ProdID_Orders. The ProdID_Orders will have 1 column called ProdID.

If the OrdersDetails table has a Identity Column as a primary key, I want to Create a New Table called ProdID_OrdersDetails. The ProdID_OrdersDetails will have 1 column called ProdID.

If the Employee table has a Identity Column as a primary key, I want to Create a New Table called ProdID_Employee. The ProdID_Employee will have 1 column called ProdID.


so the Create Table Statement for the ProdID_Orders will look like this:


CREATE TABLE [dbo].[ProdID_Orders](

[ProdID] [int] NULL

) ON [PRIMARY]


The Create Table Statement for the ProdID_OrdersDetails will look like this:


CREATE TABLE [dbo].[ProdID_OrdersDetails](

[ProdID] [int] NULL

) ON [PRIMARY]

This sequence will continue for all the tables in the database that have an Identity Column as a primary key. Thanks. I will appreciate some assistance with this.

View 1 Replies View Related

Stored Procedure To Create A New Database With Tables

Feb 28, 2007

I want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.

View 6 Replies View Related

Running Create Scripts For Database, Tables And Procedures

Jul 7, 2005

Hi there,I am trying to create a Sql Server 2005 Express database by running Sql Scripts. I have a script that has CREATE statements for tables and procedures. What I'd like it to do is create a new database with a name and a file location I specify.I searched high and low, tried lots of options with the SqlCmd tool but I keep getting authentication or other errors.Is this even possible? If so, does someone know of a good reference? I know I can create a new database and tables from within VWD but I need to create this database based on a .sql file....Thanks in advance....Imar

View 22 Replies View Related

SQL Server Express Is Working - What Tool Do I Use To Create Database And Tables?

Aug 16, 2006

Hello,

I have SQL Server Express working however I don't see any tools to create a database and tables.

I am trying to create a database to store stock quotes that I will input from a c# program.

What tool should I use to design the database?

Thank you!
Toben

View 4 Replies View Related

SQL Security :: Create Database User And Give Grants To Few System Tables

Aug 12, 2015

Need to create a user in sql server provide grants to few system tables to the above user.

View 10 Replies View Related

Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource

Jun 5, 2007

Hai,

I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.

The error message is "Invalid Argument".

I use the following code for creating the linked table

Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table

Dim ADOConnection As New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

With tblLink

' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB

' Set the properties to create the link.
Dim adoxPro As ADOX.Property

adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True

adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString

adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl


End With

'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")

'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)

'Append the primary index to table.
catDB = Nothing

End Sub

If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.

Thanks in advance
Sudeep T S

View 4 Replies View Related

Delete Record Where Values Exist, Else Create Them..

Oct 3, 2000

Dear friends,

I am thinkking there has to be away to do this in a SQL
statement.

can't you do some kind of...

Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int

AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)

View 3 Replies View Related

Help On CREATE PROCEDURE Delete + Insert Where Not Exist

Apr 30, 2008

help on CREATE stored procedure delete and after insert where not exist
in one stored procedure
in table_B



Code Snippet
CREATE PROCEDURE [dbo].[delete_from_table_B]
@empID varchar(500)
as
DELETE FROM table_B
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0

---HELP from this ponit how to insert ? after where not exist


IF @@ROWCOUNT > 0

BEGIN


insert into
table_B
set (empID,ShiftDate,shiftType)
where not exist

select
empID,ShiftDate,shiftType
from
table_A





table_A

empID fname ShiftDate shiftType
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
999 qqq 21/03/2008 9
222 bbb 02/05/2008 7
222 bbb 03/05/2008 8
222 bbb 04/05/2008 9
222 bbb 05/05/2008 7
222 bbb 06/05/2008 9
222 bbb 07/05/2008 3
222 bbb 08/05/2008 4
222 bbb 09/05/2008 5
333 ccc 03/04/2008 9
333 ccc 04/04/2008 2


TABLE B

empID fname ShiftDate shiftType
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6


TNX for the help

View 1 Replies View Related

Integration Services :: Automate Process Using SSIS To Create Tables In Corresponding Database And Load Data

Oct 6, 2015

We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.

Accounting2014, Accounting2015 in AccountingDB; 
Sales2014, Sales2015 in SalesDB; 
Products2014, Products2015 in ProductsDB; 
Inventory2014, Inventory2015 in InventoryDB etc....

These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....

View 6 Replies View Related

SQL Tries To Drop Tables That Don't Exist

Mar 14, 2008

When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?

Thanks!

View 10 Replies View Related

Tables Exist But Wont Drop

Dec 11, 1998

I have two tables that will not drop in a Database. I'm running SQL 6.5 on a Compaq Proliant.

Symptoms: Both tables show up in sysobjects, sysindexes, and syscolumns. Both allow SELECT, sp_rename, and truncate, but just hang ISQL or the Enterprise Manager when I try to drop them. I ran a DBCC CheckTable on them with no errors reported. Any ideas??

View 2 Replies View Related

Join 2 Tables Exist In Two Databases

Nov 23, 2005

Hi,
I want to get the results of a query between 2 tables that exist in the same server but in 2 different databases. I want this query to be executed in a stored procedure (use the reults in a cursor), or if it's possible to be used in an ado dataset or a simple dataset in Delphi. Can this be done also if the two datases (MSSQL) exist in different servers??
Can anyone show an example of how to accomplish these tasks??

Best Regards,
Manolis Perrakis

View 9 Replies View Related

Transact SQL :: Query 2 Tables Using Where Exist

Jun 2, 2015

I need to show maxInspectionDate in my result but I can't figure out how to do it.

SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits
FROM Equipment E
WHERE EXISTS
(SELECT t.equipmentID, r.maxInspectionDate
FROM (
SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
FROM equipmentInspection
GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate)

View 9 Replies View Related

Delete A Record That Exist In 6 Tables At The Same Time

Sep 28, 2007

Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!

View 6 Replies View Related

Select From Table Only Columns That Exist In Both Tables

Mar 28, 2008



Hi everyone.

I am stuck on this for quite a while. Lets say i have 2 tables.

Table 1 with these columns:

Serial_Num
Product
Price

Table 2 with this column only:
Product


I need to create a VIEW that will show me all the the data in Table 1 but only for the column that exists in table 2. Example:

Something like this:

select (all the columns from table 2)
from table 1


I need it to be dynamic because columns could be added to both tables anytime.
Thanks.

View 5 Replies View Related

Unable To Create A New File Data Source: SQL Server Does Not Exist Or Access Denied

Jun 22, 2004

Hi ,

I have just downloaded an evaluation version of SQL server from the microsoft website and installed it in my PC.

Now I want to create file data source.

I followed these steps:
-From the control panel, ODBC datasource admin
-click the DSN tab
-click the add button
-select the sql server driver, enter the data source file name
-enter "local" in the server text box as I installed the sql server in my standalone PC.
-click next
After that I had this error.
"Connection failed. SQL server does not exist or access denied."

But The sql server service is running.

Can anyone help me where did my configuration go wrong??
Thanks...

Nov

View 2 Replies View Related

SSIS 2005: Dynamically Create Data Flow Dest Table If It Does Not Exist?

Sep 20, 2007

Hi all,

I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.

I searched on this in the forums but based on the question it's difficult to kow if it has been answered or not.

Details:

I am writing some SSIS packages that need to be executable on another server. Many of the Data Flow Tasks copy data (such as from a Fuzzy Grouping transformation, and lots of other stuff) into a new table. But the other server will not have these tables set up for the first run.

My current solution is to check information_schema.tables and drop IF EXISTS. But, then the Data Flow Task will not work (becase table does not exist). So, I script to new window a create table statement based on the existing table that I use in my dev environment. This is a hack and I want to find a better method.

It is quite possible (although unlikely) that the source columns could be changed in the future, or some query used to pull the data might be modified. If this happens, then I would need to change the CREATE TABLE Execute SQL task. I want my package to accommodate without having to modify it.


When I use the Import/Export Wizard, I can select a table name from the drop down list OR type in a new name. When I type in the new name, it assumes I want to create the table. NOW, is there a way to mimic this in BI Developer Studio? Yep, I saved the Wizard version of the SSIS package and all it does is run a CREATE TABLE statement first.


I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.

Any ideas?

Brian Pulliam

View 12 Replies View Related

Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)

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







Copyrights 2005-15 www.BigResource.com, All rights reserved