I am using JDBC DatabaseMetaData's getColumns() to get the table names, column names and datatype of the columns. As a result, I am getting user tables as well as the system tables. How to fetch only the user tables excluding the system tables like sysindexes, etc. I know this is possible by executing a select query (using SYSOBJECTS and xtype = 'U'). But, is there anyway to fetch only the user tables using the databasemetadata API.
SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);
I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.
SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
Hi, I am working with the followings: jdk5 in eclipse3.3 and Microsoft sql server. For the jdbc application, i used to connect the database with the following code: Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); con=DriverManager.getConnection("jdbc:microsoftqlserver://NODE1:1433","sa"," "); The above code woks fine and I able to connect the database.
Here is my problem: The above code only connect the master database. But I want to connect the user defined databse(that is., other than master database). Can anyone tell me the solution to solve the issue.
I'm having trouble creating and using temp tables over JDBC.
Actually, it's not so much creating as referring to a created table. What appears to be happening is that the table is created and immediately dropped, so a subsequent statement cannot refer to the newly created table.
I've already done a lot of research and experimentation and found the selectMethod=cursor connection property. Setting this property seems to have no effect.
Example queries:
1) select some.columns into #diffComp from some.table where ..... 2) select * from #diffComp join foo on ......
The second query depends on the temp table #diffComp created by the first query. When I trace this in Eclipse, the value returned by getUpdateCount() on the first query is the correct number of rows. The second query is executed immediately aferwards, and ALWAYS throws :
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '#diffComp'.
I'm also able to look inside the connection objet in Eclipse, and can confirm that the activeConnectionProperties member DOES contain selectMethod=cursor.
We are seeing a regression bug with the Microsoft JDBC driver 1.2 CTP.
Using this driver, we don't seem to be able to call stored procedures which return a result set, if those stored procedures use temporary tables internally.
The 1.2 CTP driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases. The previous 1.1 driver, suceeds in both cases.
Here is a test case which demonstrates the problem (with IP addresses and logins omitted). The prDummy stored procedure being called is quite simple, and I've copied it below:
Code Snippet
public class MicrosoftJDBCDriverCallingStoredProceduresTest extends TestCase {
// CREATE PROCEDURE [dbo].[prDummy] // AS // // CREATE TABLE #MyTempTable ( // someid BIGINT NOT NULL PRIMARY KEY, // userid BIGINT, // ) // // SELECT 1 as TEST2, 2 as TEST2 // GO
public void testStoredProcedureViaDirectJDBC() { Connection conn = null; String driverInfo = "<unknown>"; String dbInfo = "<unknown>"; try { // Set up driver & DB login... Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String connectionUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433"; Properties dbProps = new Properties(); dbProps.put("databaseName", "xxxxxx"); dbProps.put("user", "xxxxxx"); dbProps.put("password", "xxxxxx"); // Get a connection... conn = DriverManager.getConnection(connectionUrl, dbProps); driverInfo = conn.getMetaData().getDriverName() + " v" + conn.getMetaData().getDriverVersion(); dbInfo = conn.getMetaData().getDatabaseProductName() + " v" + conn.getMetaData().getDatabaseProductVersion(); // Perform the test... CallableStatement cs = conn.prepareCall("{CALL prDummy()}"); cs.executeQuery(); // If the previous line executes okay, the test is passed... System.out.println("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" calls the stored procedure successfully."); } catch (Exception e) { // Fail the unit test... fail("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" fails to call the stored procedure: " + e.getMessage()); } finally { // Close the connection... try { if (conn != null) conn.close(); } catch (Exception ignore) { } } } } The output of this test under both drivers and accessing both databases is as follows:
Code Snippet
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
I have read similar posts to this, but I am still having problems.
I am trying to use connection pooling to connect to a local SQL Server 2005 database. I am running my application using MyEclipse Enterprise Workbench. I have verified that sqljdbc.jar resides in "WebRoot/WEB-INF/lib/"
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.
The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.
If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?
I've got an import app written in Java. One table I'm importing from contains 22 million records. When I run the app in a 2000 environment, I have my max heap set at 512, and the table gets imported. When I run in a 2005 environment, I have to change the max heap to 1152 or it will error out with a similiar error:
com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:854,269,999. JVM total memory size:1,065,484,288. (<--this is with max heap at 1024)
what is the difference between the 2000 and 2005 JDBC that I have to set max heap in one and not the other?
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-)
I want the SQL command that lists the names of all user tables.
Alternatively, I have the following problematic Access 2000 code:
Public Sub ListAllTables() Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim cnn As New ADODB.Connection Dim i As Integer, j As Integer Dim vgRet As Variant Dim intPrefixLen As Integer Dim strAppend As String
For j = (cat.Tables.Count - 1) To 0 Step -1 Set tbl = cat.Tables(j) With tbl Debug.Print tbl.Name vgRet = SysCmd(acSysCmdSetStatus, tbl.Name) End With Next
Set tbl = Nothing Set cnn = Nothing Set cat = Nothing DoCmd.Hourglass False vgRet = SysCmd(acSysCmdSetStatus, " ") End Sub
This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine!
I need to duplicate all the tables owned by dbo to another object ownername in a database within the same database.That means,if there are 100 table owned by dbo in a 'Database A'. I need to have a total of 200 tables in the same database 100 each for dbo and 100 for another user.They should have same schema including the keys etc and data.How is this possible?Through DTS we can't copy them to the same databse.We do not want to have a temp db inbetween to complete this process.Any ideas on how to do this? NOTE:This is not a one time job. Thanks. Sheila.
There are more than 20 tables and 100s of users access the database. what is the quickest way check what permission users have on the tables. Could anyone help me
I administre a DB that has about 2000 user tables, unfortunatly I haven´t documentation, and the people that create thouse tables are no more in the company.
How can I identify the user tables that really are used by SQL today?
I used the Profiler to identify the Stored Procedures that are called recently, but when I try to use Object:Opened event class to look the tables that are opened, it dosn´t display anything.
Im new to SQL Server and Im having trouble to decide wich is the best way to filter the tables that one user can read and write.
For example the table Sales as a field named Branch (SMALLINT) with values 1 or 2 (the store that made the sale). User "A" can see and write only records where Branch = 1.
Im developing a windows program that shows all the purchased orders in a DataGridView. So should I make the filters (permissions) in the program or in the SQL Server?.
I hope I made my self clear (my english is not so good)
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
The following procedure will display the size of all the user tables in a database.
CREATE proc sp_tablesize as if exists (select * from sysobjects where name = 'sp_tablesize') begin goto calculate_tablesize end else begin CREATE TABLE #SpaceUsed ( TableName sysname, TableRows int, TableSize varchar(10), DataSpaceUsed varchar(10), IndexSpaceUsed varchar(10), UnusedSpace varchar(10) ) goto calculate_tablesize end
declare c1 cursor for select name from sysobjects where xtype='u' open c1 fetch c1 into @tablename while @@fetch_status = 0 begin set @cmd='exec sp_spaceused['+@tablename+']' insert into #SpaceUsed exec sp_executesql @cmd fetch next from c1 into @tablename end
We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.
For example, to query a table called Page we would nee to do this on the new box.
SELECT * FROM webdev.page
unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?
Thank you for your help...sorry for the newbie type question.
We have a person who CAN connect to a named instance in SQL Server Management Studio. There is nothing in the log for a failed login for him. However, when he tries to expand the Tables folder under the only user database in the instance, he gets the error: Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum). Additional Information: An exception occured while executing a Transact-SQL statement or batch. Select permission denied on object 'extended_properties',database 'mssqlsystemresource', schema 'sys'(Microsoft SQL Server, Error:229).
He is trying to expand the Tables folder under a database named ADSALLDB. He can see the folder as well as the other folders (i.e. Views, Synonyms, Programmability,etc.), but can't expand any of them. He can expand the folders under the system databases.
Other uses set up just like him can connect. He is set up with read/write access to the database ADSALLDB. He can expand the system databases.
This is a named instance in a 6 node cluster. SQL Server 2005 SP1 Build Level 2221.
How can I prevent a colleage to delete tables in a specific database. Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases.
Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.
With MS SQL 2000 Enterprise Manager, is there a way to allow a user accessto only a few tables, but deny the user access to the rest without having togo to all of the tables and denying access? The database has roughly 50tables, but only 3 should be granted to the new user, so as you can see itwould be a painstaking task to manually do this with the *cough* mouse. Or,if I can run some sort of grant script, that would work too. Thank you!
Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
I'm running asp.net on an XP machine with MSDE 2000 as the database. I want to create a user table with a userid and password. I have a second table that contains details about the user such as home address and phone number etc. When the user first signs up, I want their userid to go into the user table and the user detail table. In my SQL insert command, I can't get @userId to go into both tables. So my question is how do I get the userID to go into both tables or is there a better way of doing this altogether?
