Good Morning
Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:
SELECT name
FROM sysobjects
WHERE (xtype = 'U')
Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?
Thanks,
Ed
Can some one please tell me whats a best way ot get a list of all the tables that are present in a particular database.
Thanks
I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?
View 4 Replies View RelatedSql2005??? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?
View 1 Replies View RelatedSQL query to understand the names of all the available tables in the database , number of records in these tables and size of these tables ?
View 1 Replies View RelatedHi,
I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
Thanking you.
tats
Hello,
How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.
Thanks in advance,
Uday.
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View Relatedi need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View RelatedFrom 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
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View 3 Replies View RelatedDoes anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedGreetings,
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Thanks in advance for your replies.
Ok say I would like to build a table for of the following questions(say 6 questions for the sake of argument):
Do I just stored the index of the radiobuttonlist. What are some resources that I could look at. Should I make a look up table.
5) If money were no object, I would live . . .
Prefer not to say
On a tropical island
In a New York penthouse
In an English castle
On a Texas ranch
In a Malibu beach house
In a mountain retreat (Selected)
On the moon
None of the above
1 --->
2 --->
3 --->
4 --->
5) --->6 This is the question we are looking at.
6 --->
How should I create the database table for the above example.
I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.
I have performed the following tasks **
1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)
3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.
4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.
What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
View 8 Replies View RelatedI am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download.
When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end.
Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time?
Hi,
In a stored procedure is there a way of accessing a table presend in another database.
Please provide pointers on how i can do this.
Thanks,
Raj
hi,
I have a access database. In this database there are 10 tables and this tables are related to each other.
I want to move these tables to another database according to a field in XYZ table (vertical fragmentation).
for example there is name coloumn in the XYZ table and I want to take only joe lines and other tables related to that from database A and move it to database B
how can I do this?
thanks..
I have two Databases with the same name that reside in different servers. The two servers are linked servers. Each database consists of 5 Tables. The tables have identical names in each database.
The database in ServerA has 5 tables with data, the database in ServerB also has 5 tables with the same schema as ServerA, however the 5 identical tables in ServerB contain no data.
I need to populate the 5 tables in ServerB with the Data from the 5 tables in ServerA.
What SQL code or script can i use to populate the 5 tables in ServerB
Below are the names of the 5 tables:
TABLE1: [ServerB].[ProdDB].dbo.[Orders]
TABLE2: [ServerB].[ProdDB].dbo.[Sales]
TABLE3: [ServerB].[ProdDB].dbo.[Employee]
TABLE4: [ServerB].[ProdDB].dbo.[Customer]
TABLE5: [ServerB].[ProdDB].dbo.[Region]
Does any one have a script that could help me with this task. Thanks
I am using SQL Server 2005
Hi,
In a stored procedure is there a way of accessing a table present in another database.
Please provide pointers on how i can do this.
Thanks,
Raj
i am using visual web developer 2005 and SQL Express 2005 with VB as the code behindi have two databases . i want to copy all the tables with all the contents from one database to another database programaticallyhow to achieve this ?please help me
View 1 Replies View Relatedhi,I have two tables, t1 t2col1 col1 col2 1 1 0 2 1 0 3 1 1 4 2 0 5 2 0 6 3 1 7 3 1 8 4 1 9 4 1 10 4 1 11 4 1t2.col1 is the key from t1.col1I want to retrieve all t1.col1 records which equal to t2.col1 and tb2.col2 has ONLY "1"the result should be: 3, 4I try:select tb1.col1 from t1 as tb1 where tb1.col1 in (select col1 from t2 where col1=tb1.col1 and col2=1 and ...???.)any help?
View 8 Replies View RelatedHello all,
If I write this query :SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandes the return is good.
But If I the query is :SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, Commandes I have an error "Invalid column name.
The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table.
How to make a good query with an aliased column without error ?
Thanks for reply.
Regards.
PAB.
I have two tables: 1 with data, the other 3 columns from the first table with rows filled with data that needs to be ommitted when a query is made on the first table.
For example, say I have a column named "meat" and there are rows in it with "beef" as the data. In the other table there is also a column with meat and beef as the data. I would need to query both tables to first get the data to be ommitted and then use that result to query the first table. I hope I'm making sense, any help would be appreciated
Hi
I need to be able pull certain data from our database. I need to find all stockitems (itemid column) that are a T item (binname column) and the memo to be created before the 01/02/2007 (timeanddatecreated column)
To get the data I need - I need to query three tables.
Stockitem - This has the column "itemid"
Stockitemmemo - This has the column "itemid" and "timeanddatecreated"
Binitem - This has the column "itemid" and "binname"
My results must be based on the following criteria......
All the itemid's have a 'T' in binitem.binname and the memo must have been created before 01/02/2007.
I do have two questions based on the above....
1. Does it make sense what I need?
2. Is it possible
:confused:
Any help would be gratefully received.
Thanks
Simba
I want to get the list of tables which an SQL query is using. It could be through some query or parser written in jruby or R or any other language,any thrd party tool(freeware)or any scripts.
View 1 Replies View RelatedHi,
I know it is simple query but I am confused.
I need to make a query from 3 tables in the same database. There are no relations between these tables.
Here is result I need.
KE LE JE
15 775.5 398
18 192.23 399
Please help me.
Sep
I'm doing a query from two tables. the table "pr" and the table "prre" with the following data: pr.no, pr.nome, pr.data, pr.recibo, pr.ettsuj, pr.ettdesc, prre.cr pr.eliquido, prre.rqtt , prre.ervu, prre.ere
I'm doing the following query:
select cast(pr.no as varchar) as 'Numero',pr.nome as 'Nome',pr.data as 'Data',cast(pr.recibo as varchar) as 'Recibo',
cast(prre.rqtt as integer) as 'Qt Dias', prre.ervu as 'valor unitario',prre.ere as 'Total Subsidio',
pr.ettsuj as 'Total iliquido', pr.ettdesc as 'Total Descontos', pr.eliquido as 'Total Liquido'
from pr
inner join prre on prre.prstamp=pr.prstamp
for this query is a list of salaries, with the fields, official number, expiration, name, date, receipt number, net total, the total gross, days subsidy amount, subsisidio.
I've got a problem with my query : [URL] ....
I want to say:
Pull all users whos status is New and they have at least one Completed delivery
The problem is, I am getting multiple delivery records form one patient, but I would like to see only newest one. So instead of this result:
2014-05-10 00:00:00.000CompletedReed BrewerNew830
2014-06-10 00:00:00.000CompletedReed BrewerNew830
2014-07-10 00:00:00.000CompletedReed BrewerNew830
2014-07-02 00:00:00.000CompletedColton DukeNew920
I would like to get this:
2014-07-10 00:00:00.000CompletedReed BrewerNew830
2014-07-02 00:00:00.000CompletedColton DukeNew920
I have tried to use Max , but it does not work ....
select
Max(delivery.deliveryDate),
delivery.deliveryStatus,
patient.userName,
patient.userStatus,
delivery.accountid
[Code] ....
I all,
i have a problem with a query.
I have two tables (Software and Licenses), and one software can have one or more licenses.
So what i want to get from this query is:
- all info of the software table
- and, if the software have or not, attributed licenses.
How can i do that?
hi,
i have two tables generally:
bB_posts
- postid
- title
- body
- posttime
- commentcount
bB_comments
- commentid
- postid (FK)
- commenttext
*/
Select t1.title, t1.body, t1.posttime, t2.commenttext
from bB_posts as t1
join bB_comments as t2
on t2.postid = t1.postid
(or t1.commentcount = 0)
This query prints out all the posts which have 1 or more complementary comments. But i would aslo like to print the posts which have 0 comments (seen in brackets).
how should i do this?
thank you