Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.
Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.
Any Help would be appreciated!
Dave.
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/"
"WebRoot/WEB-INF/web.xml":
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsichemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<resource-ref>
<res-ref-name>jdbc/DefaultDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
"WebRoot/META-INFcontext.xml":
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/DefaultDS"
auth="Container"
type="javax.sql.DataSource"
username="tec"
password="tec"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive"
url="jdbcqlserver://localhost:1433/tec;databaseName=tec;user=tec;password=test;"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>
Classpath:
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
<classpathentry kind="src" path="src"/>
<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
<classpathentry kind="con" path="com.genuitec.eclipse.j2eedt.core.J2EE14_CONTAINER"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/dom.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxen-full.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jaxp-api.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jdbc2_0-stdext.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sqljdbc.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/jstl.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/mail.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/sax.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/saxpath.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/standard.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xalan.jar"/>
<classpathentry kind="lib" path="WebRoot/WEB-INF/lib/xercesImpl.jar"/>
<classpathentry kind="output" path="WebRoot/WEB-INF/classes"/>
</classpath>
Code to connect:
import java.io.Serializable;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public int testConnect(){
Connection conn = null;
InitialContext ctx = null;
java.sql.Statement stmt = null;
try {
ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/DefaultDS");/*This is generating the Cannot load JDBC driver class... error*/
conn = ds.getConnection();
stmt = conn.createStatement();
return CONSTANT.SUCCESS;
} catch (Exception e) {
return CONSTANT.FAILURE;
}finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
if (ctx != null)
ctx.close();
} catch (Exception ex) {
// do nothing
return CONSTANT.FAILURE;
}
}
}
Any ideas would be greatly appreciated.
Hi all,
We've just stumbled on a 1.0 version incompatibility with the JDBC specs.
Problem: A table with SMALLINT column. According to JDBC specs version 3.0
(p.B-179) and 4.0 (p.221)), the value should be converted to Integer type.
Unfortunatelly we get a Short object :(
Now, I remember, this case was also affecting old JSQLConnect driver from
DataDirect. Could that problem sneak to new MS driver too?
Please let me know any resolution to this problem if exists.
The issue has not been fixed in CTP 1.1 version. Any ideas if it can be fixed??
Cheers,
Piotr
From what I'm seeing in MS documentation I would like to use SYNONYM to obtain information from a remote servers tables. I've tried to find some decient documentation on using it, but can't see to find a clear example for remote servers and in my testing I can make the SYNONYM but the conneciton is failing.
Any helpful hints or references would be welcomed.
Thanks,Ken..
Hi Experts:
I am writing a general API which would fetch synonyms from any database
providers (and would filter for a given schema).
I am using GetSchema method on DBConnection object and it works fine for
oracle.
SQL Server 2005 has a support for synonyms, but the above piece of code does
not work. Is there any API by which i can get the list?.
Thanks
AK
Hi all,
Just as a curiosity (and thoroughness) thing, why are there synonyms in SQL Server? When I say synonyms, i mean INT and INTEGER, OUT and OUTPUT: These word pairs mean the same thing!
And in some examples they chop and change between the use of these words... It adds to the confusion.
Anyhows I'm curious as to the "why" behind this.
Thanks
Chris
Hello ,
Where can I download synonyms database ?
Thank you !
I currently have a 2 node Availability Group that houses some big warehouse type of DB’s. It’s not uncommon to see DB1 populate DB2:
Use db1
Go
Insert into db2.dbo.table_1 values ‘ssfsfsfsf’
This all works as expected. However, we want to split these DB’s away from each other to reduce CPU impact on any one instance. Of course though we don’t want to have to go and change all the connection strings in our SSIS Packages. By default this would now require going to a 4 part naming convention like so: Insert into newServerName.db2.dbo.table_1 values ‘ssfsfsfsf’Again though, we are trying to avoid this. That said I was thinking to use synonyms. This works as expected, until a failover occurs, and the synonym is redirected to the local Read Only version of DB2.So in other words, SQL Server is behaving exactly as it should, but I need a way to work around it.
Example below:
USE [master]
GO
/****** Object:Â LinkedServer [DB2]Â Â Â Script Date: 10/28/2015 11:43:46 AM ******/
--note this is pointed to the AG Listener name, for failover purposes.
EXEC
master.dbo.sp_addlinkedserver
@server = N'DB2', @srvproduct=N'sqlserver', @provider=N'SQLNCLI',
@datasrc=N'myAGListenerName', @catalog=N'DB2'
[code]....
Again, SQL Server is behaving exactly as it should. However, this synonym is now fairly worthless, as it can't handle failing from one node to the other in the AG
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?
we now used synonyms on the version 6 of our peoject, at first it turns out good. Then we migrate a database to the new database, everythings fine, its just a data migration anyway. The target database' structure and DML's wasn't change during migration. then intermittendly, we're encountering "A severe error occurred on the current command. The results, if any, should be discarded". I said its intermittent because sometime its ok, mostly its not functioning.
This is very painfull because all our scripts has this synonym. All our scripts uses an sp execution logging stored procedured of another database so we created a synonym for those sp's instead of fully qualifying the procedure name.
This is a major issue for us, please advise. You may response here, send me email or email me. Many Thanks.
Rodel E. Dagumampan
Email: dehranph@gmail.com
Yahoo: dehranph
Blog: http://community.devpinoy.org/blogs/dehranph
Hello
I'm looking for a way to store a large chunck of text (200 lines) in a variable which can be called in different objects within the database.
For example: I have several stored procedures that create the same temp table which exists of 200 column names over and over again.
It would have to look like this
Import myScript (I keep thinking of Import like used in .NET)
and myScript could exist of:
CREATE #MyTable(
...)
or even some simple text or a part of a sql statement.
I'm familiar with synonyms but you can't use that in this scenario.
Then I thought of functions. The scalar function returns a result, not what I want here. Table valued function return tables, not what I want.
Many thanks in advance!
When you create a stored procedure and give the user execute permission, you don't need to give the user select permission on the table used in the stored procedure.
If one of the tables in the stored procedure is a synonym referencing a table in another database, and the user is already in the other database, you get a select permission denied on that table and I could only get it to work if I gave the user select permission on that table.
Is there a way around that, since I hate giving select permissions on tables?
Thank you,
Wissam
I am using a sql 2005 database that contains a combination of local objects plus synonyms for objects from a second database. I have had no problems using synoyms for tables and views in reporting services but can't get synonyms for stored procedures to work as a report data source. All databases are on the same server and we have installed service pack 2.
Any suggestions?
Hi!
I am evaluating an architecture for one of our project... a SQL database containing the data (backend) and a second database containing the development code (frontend) linked to the backend with synonyms.
It enables to upgrade the code without touching the data. Or to change the backend / use a different set of data at will.
Everything was going fine, the behavior was expected to be EXACTLY the same with synonyms as with real tables. But I came accross a problem:
Let's say we have a synonym (frontend) dbo.TABLE1 that points to a table (backend) with a IDENTITY column.
I have a sp (frontend) with the following code:
INSERT INTO dbo.TABLE1...
SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()
Well in that case, @SCOPE_IDENTITY is NULL!
Anyone has ever faced that problem? Should I use another function to return the last ID inserted? Or is it the backend/frontend architecture that is completely flawed? I also heard there's a way, by creating the tables and the code on different filegroups, to restore only the tables or the code...
Thanks for your input!
Frantz
Hi All,
Been doing some testing with SQLCMD and variables for installation scripts, enclosed below is some source code.
The one problem, I've been getting is the following error:
Incorrect syntax near 'certification'
This has been tested only in SSMS utilising the run in SQLCMD mode.
This error only occurs on the SQLCMD variable replacement, where it replaces $(Cert_Schema) for the word Certification.
Running the CREATE Synonym with the word Certification hard-coded into the script works.
Any ideas?
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Best Regards,
D
USE [Testing]
GO
/****** Object: Table [certification].[Table_1] Script Date: 01/03/2008 11:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [certification].[Table_1](
[PKId] [smallint] NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[PKId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--- SQLCMD Script below:
-- line below should read, colon setvar cert_schema 'certification'
etvar Cert_Schema 'certification'
-- The line below fails to work for some reason
CREATE SYNONYM crt_asa FOR $(Cert_Schema).Table_1
DROP SYNONYM crt_asa
-- Hard-coded equivilant line works
CREATE SYNONYM crt_asa FOR certification.Table_1
DROP SYNONYM crt_asa
Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time
View 1 Replies View RelatedI'm looking for a "God-script" to search for a given string (case invariant) in all DB objects (esp synonyms) in all DBs on a server?
View 9 Replies View RelatedWhat is "MetaData" in "Data Transormation service" and what is"MetaData services" ?and in which field I can use them?Thanks
View 1 Replies View RelatedHi All!
I recently added a column to an existing table with a getdate default. When doing a query from that server everything works fine. When a query is ran from a remote server I get an SQLOLEDB error message saying 'inconsistant metadata'. I've tried dropping the remote server and reconnecting but that didn't seem to resolve the problem. Can anyone tell me how to resolve this error. I believe the error number is 7353.
Any suggestions appreciated!
Thanks Jeff!
Hi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.
View 2 Replies View Relatedhi,
Is there a way to find out which user defined procs/child packages etc are been called in SSIS packages using some metadata. The idea is to have a document which lists the number of packages called, whats sprocs and child packages are executed by those pkgs..
I have checked the SSIS metadata whitepaper but that is too generic.
What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
I'm working on capturing metadata from my SSIS packages. I have found multiple postings and a white paper that
reference a download toolkit for just this purpose. The link is dead and a search renders no results - can anyone help?
SQL Server 2005 Business Intelligence Metadata Whitepaper
-We are using SSIS packages for various kind of data load from excel source.
-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.
-During design time if you accept the metadata changes, all things work fine.
But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.
Do you have any suggestions for the above problem?
Thanks, Vijay.
i am using icolumnsrowset interface to get some metadata about the columns in a rowset, but i never got unique and primary key columns in the columnsrowset, they always return null, when using sqlcedataadapter i simply add addwithkey option to the adapter to determine it, but i dont know how to do it by using ole db interfaces, i have tried to set DBCOLUMN_KEYCOLUMN flag to true on ccommand<cynamicaccessor, crowset> but it seems it rejects it, generating an unknown error, error object says almost nothing except that 'errors occured[,,,,,]' text
can someone tell me, how can i retrieve columnsrowset with that unique and primary key sections filled?
Hello, i would like to know if it's possible to generate automatically a word document or an excel document that will contain all the metadata definition, for example containing the source columns names, their datatype, and the destination with their datatypes, so that it would easy to create a data dictionnary .
Thank you in advance.
Hello everybody,
It's now quite some time that one particular behaviour of SSIS is really frustrating me and I would like to know if I'm the only one experiencing this problem or if other people have the same problem.
The issue I'm talking about is SSIS 'dependency on what is written in the XML files describing the flows.
Particularly with the Data Types of columns.
I'm explaining myself: Imagine your are developping a flow containing several numeric(18,0) columns...
During the flow you have to perform a lookup on an Integer Field.... Of course this operation is not allowed as a numeric is not mappable with an Integer... (This is, in my opinion, a nonsense as an implicit conversion has to be possible).
as a result of this behaviour, I decide to change the datatype (numeric) from my source query to an integer and use it in the Lookup which of course succeeds but now I have a second problem: each lookup in my flow has an error handling branch which I'm joining back using a Union transform. and there we have the second irritation: the Union transform doesn't replicate the Data Type changes that occured upwards in the flow... worse: it even has no interface to let you modify the data types like the advanced editor of some transforms or data sources. (I've just lost a complete dataflow while trying to modify it manually in the xml file directly :-( for those who are considering modifying directly the XML, don't!! You are asking for trouble and a lot of frustration when you'll switch back to the designer to see the effects )
My question is now: Am I misusing SSIS?? Is there somewhere an option to activate in order to get this behaviour fixed??
Has anyone else experienced this problem?? How are you solving this??
Are there any plans in the future to loose this dependency on the datatypes or at least add some implicit conversions??
Thanks in advance for your replies, suggestions,questions and other thaughts about this subject :-)
Alain
Hello-
I have a question regarding my metadata information. I finally setup my fixed width file which took some time. Is there a way that I can backup my metadata so I wont have to recreate these setting again. I'm thinking the format of the file is stored in the metadata so if I have a user running the SSIS package from the Business Intell Studio they wont reset all of my columns. Is there a file I can restore or backup if this should happen
Shanon
Hi all of you,
I'm focused on writing a SSIS metadata application. I'm trying to get all the metadata for all the DTSX packages from a concrete server.
I'm loading successfully tasks as Execute Sql Task, ForEach task and so on but how could I do the same for
Data Flow Task and its components. ?¿¿
For a Data Flow I don't know how to begin...
If TypeOf tmpTaskHost.InnerObject Is ????????????????? Then
My central code is the following:
sServer = "TEST1"
pkgIn = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
'''''''''''''''Para cada carpeta creada a partir de MSDB
For Each pkgCarpeta In pkgIn
If pkgIn.Item(y).PackageDataSize <= 0 Then
pkgCarpetaSSIS = app.GetPackageInfos(pkgCarpeta.Name, sServer, Nothing, Nothing)
Else
pkgCarpetaSSIS = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
End If
If pkgCarpetaSSIS.Count >= 1 Then
While i < pkgCarpetaSSIS.Count
'cargar en memoria el SSIS en el objeto PAQUETE
pkg = app.LoadFromSqlServer(pkgCarpetaSSIS.Item(i).Folder & "" & pkgCarpetaSSIS.Item(i).Name, _
sServer, Nothing, Nothing, Nothing)
Dim task As Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask
Dim task2 As Microsoft.SqlServer.Dts.Runtime.ForEachLoop
Dim exe As Executable
Dim tmpTaskLoop As ForEachLoop = CType(exe, ForEachLoop)
Dim tmpTaskHost As TaskHost = CType(exe, TaskHost)
For Each exe In pkg.Executables
Select Case exe.ToString
Case "Microsoft.SqlServer.Dts.Runtime.ForEachLoop"
tmpTaskLoop = CType(exe, ForEachLoop)
Case "Microsoft.SqlServer.Dts.Runtime.TaskHost"
tmpTaskHost = CType(exe, TaskHost)
End Select
issue raises here.
If TypeOf tmpTaskHost.InnerObject Is ExecuteSQLTask.ExecuteSQLTask Then
n1 = tmpTaskHost.Properties.Item(33).GetValue(tmpTaskHost)
For y = 0 To 42
Console.WriteLine(tmpTaskHost.Properties.Item(y).Name)
Next
End If
Next
'contador de paquetes
i += 1
End While
y += 1
End If
Next
..
..
..
..
..
Thanks a lot for ideas and thoughts!!!!
As title, such as the PK, data type of each column, etc.
Thanks,
Ricky.
Hi,
I'm running the following query in SQL Server 2005:
select name from master..syslogins;
It is being executed from within a stored procedure.
For user 'sa' - I get the complete list of users.
For a user (say 'user1') with NO sysadmin privilege - I get only two names: 'sa' and 'user1'
Is there a way for me to retrieve the complete list of users even for 'user1' without making any changes to his profile (or making very MINIMAL changes to profile)?
I don't want to give sysadmin profile to this user.
I know 'GRANT VIEW ANY DEFINITION TO public' works, but don't want to do that either.
-Anshul
I wanted to see if anyone has explored posting a Report KB based on metadata in the RS portal? If so, have you found a way to post the information on the RS portal?
I really am looking to add additional properties to a report. Other than author and description. I would like to add 2 to 3 more fields that would feed over to the catelog table on the report server. Then write a few reports that will allow for definitions, metadata, and links to the reports.
Does anyone have any ideas for something like this?