|
I am trying to call Oracle store procedures from MSSQL via linked server connection.
For every kind of oracle store procedure, I am receiving several error messages coming from oracle OLEDB or ODBC provider telling that there are syntax errors.
I am looking for a solution.
Thanks.
|
|
|
|
|
Show the complete code that you're using to call the Oracle stored procedure, please, including OPENQUERY or whatever else, the error text, etc.
Thank you.
Jeff Varszegi
|
|
|
|
|
I am trying to connect to an excel file to read the data in ASP using ADO. I have established a connection but I am not sure why the following SQL statement does not work:
strCmd = "SELECT * from Prices"
I get an error saying:
The Microsoft Jet database engine could not find the object 'Prices'. Make sure the object exists and that you spell its name and the path name correctly.
Why is this error being caused?
'Prices' is the sheet in the excel file that the data is in, is this the correct thing to enter?
Thanks
|
|
|
|
|
According to the MSDN documentation you either need to name the range in the Excel file or use the $ after the object you reference in the spreadsheet. Try something like:
strCmd ="SELECT * FROM [Prices$]";
I can't remember if you need the square brackets but I know the $ helps. If you want more info on naming the range in EXCEL, look at the help for Insert/Name command.
Hope this helps.
Jeff
|
|
|
|
|
Actually, "prices" would look for a named range of that name, while "prices$" looks for a sheet named 'prices', which is why his query fails.
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
1) What is diference between varchar and nvarchar?
2) Anyone knows what is maxlenght of a E-Mail and of a URL?
Thanks for all
|
|
|
|
|
|
Hopes this helps...
Question 1
Char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
*Use char when the data values in a column are expected to be consistently close to the same size.
*Use varchar when the data values in a column are expected to vary considerably in size.
nvarchar(n)
Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
Question 2
MAXLENGTH sets the maximum number of characters for text or password fields.
I hope that answers question 2... if not please explain?
Scratch... Thanks Jon I misunderstood his question...
|
|
|
|
|
Thank you John and Bryan 
My question is because I need create Email and URL fields in my Database and I would like sugestions to size of this fields...
|
|
|
|
|
An nvarchar column is interpreted and stored as UTF-16. If your source language uses UTF-16 for native strings (e.g. VB6, C#, Java, VB.NET), use nvarchar to save all the overhead of translation to and from byte-oriented character sets, and any possibility of trying to store characters not present in the configured collation's byte-oriented character set (which can lead to round-tripping problems). If your source data is in a byte-oriented character set, it may still be worth using nvarchar to avoid problems in conversion between different character sets, if your database is configured with a collation that uses a different character set to your source data.
There is no maximum length for email addresses or URLs. They could be any length. To store these in SQL Server, use a text or ntext column.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi Mike, thank you for the response
What you sugests? I wonder between 50 and 255 length to e-mails and URLs...
URL and E-mail sizes appears vary greatly and I do not wish waste space setting a field too large and do not wish lost important data because of a small field, understand?
Again, thank you very much!
|
|
|
|
|
Hello
I have a MS Access DB
with a table
[user_stories]
which looks a bit like
story_id(autnum), story_title(text[200] , story_text[memo], user_id[int], date_posted[datetime], date_last_edited[datetime], publish[yes/no]
How may i select 10 (or N) random recordsets from this table in sql?
regards
Bryce
---
Publitor, making Pubmed easy.
http://www.sohocode.com/publitor
|
|
|
|
|
Hi Bryce,
I do not use Access, so I can't comment on Access SQL other than to say that it would be a very odd thing for a RDBM to do..
You might want get a list of story_id's, pick ten at random in your code and build a comma delimited string (like strIDList= "id1,id2,id3,...,id10"; ), then run a query like:
<br />
"select * from [user_stories] where story_id in (" + strIDList + ")";<br />
Bill
|
|
|
|
|
Anyone knows a SQL Script Editor? My webpage has scripts in .sql files, what create tables, stored procedures and sample data.
But edit this in notepad is very bad... Query Analiser hels a bit... but does not exists anything with "code completion", "syntax highlight", etc.
If possible, "free", "opensource" or "multi-database" (SQL Server, MySQL, Interbase, etc...)
Thanks for all
|
|
|
|
|
There is a tool called SqlBuddy that fits your request nicely...
SqlBuddy is a tool for use with Microsoft SQL Server and MSDE, written in csharp/ DotNet (.NET), to facilitate SQL script writing. It is serves a slightly different purpose that of Query Analyzer, in that it is aimed to *help* the user write SQL.
http://sqlbuddy.sourceforge.net/index.shtml - SQL Buddy (Screen Shots)
http://sourceforge.net/projects/sqlbuddy/ - Download
Note: you must connect to your SQL or MSDE database before you can view a file or Sql script already created. It is just like query analyzer... only better!
Have a great one!
|
|
|
|
|
Thank you very much, Bryan! I go check it!
|
|
|
|
|
The only problems what I find in SqlBuddy are does not search words in script and does not support the GO statement!!!
I will see the sources and try fix this
Thank you!
|
|
|
|
|
Hi all,
I am thinking about writing a program to compare two databases. Basically I need to know what tables are missing or added and what the differences between them are. Has anyone done anything like this before that could give me some pointers? I will be trying to do it using C# and hoping it won't be database specific (if possible).
Thanks
Ash
|
|
|
|
|
Hi Ash,
Assuming you have the privs., you can run this query:
<br />
select * from sysobjects<br />
against each database. That will give you a list of everything in the DB: tables, views, sprocs, triggers, constraints, etc. Use the xtype column to identify what each object is.
hth,
Bill
|
|
|
|
|
Thanks Bill,
I have done that before but unfortunately it makes it database specific. eg have to use all_tables in Oracle.
I was hoping maybe ADO.net had a generic method of getting the structure of the database similar to how you create a XML schema by dragging over the tables from the database explorer.
Ash
|
|
|
|
|
Yes, you can probably write it yourself but when you browse to www.planetsourcecode.com you can probably find a solution there.
Grtz,
Guus
|
|
|
|
|
Hi,
I am currently working on a project recording names of toxins. Some of them have greek lettering and i have found a major problem with MSSQL.
When i came across the character 'ω' which is omega MSSQL inserts it as a ? where it occurs.
I have read around and found it seems to be with collation settings.
If i copy and paste a 'ω' into a asp.net page it says i need to save the page with encoding unicode 1200 which works fine.
When setting collation with MS SQL there is no code 1200 option so i dont know what to do 
Even microsoft access by default allows me to use 'ω' but surely there there is a way to do this with an enterprise class DB as MS SQL ?
Please help i'm pulling my hair out!
 
|
|
|
|
|
I still haven't worked with other code pages in MSSQL yet, although I want to sometime. Have you tried playing around with NVARCHAR yet? Sorry, it's the only thing I can think of off the top of my head.
Thank you.
Jeff Varszegi
|
|
|
|
|
i found my answer
use
N'stringwhichhasunicode'
instead of just using 'stringwhichhasunicode' whenever you use an instance of it.
EG:
INSERT INTO TABLE VALUES(N'value1','value2'
SELECT * FROM TABLE WHERE Column = N'unival'
Atul
|
|
|
|
|
I thought I would post this question here since it's a pretty good community of experts.
The application I am in charge of architecting is an ASP.NET (intranet) web application running on IIS 5 or better - targeted towards .NET environments on Windows 2000 Server and/or Windows 2003 Server. It needs to be able to scale to meet the demand of hundreds or thousands of users (ie. work in a “web farm” environment). I have to be able to support both Oracle and SQL Server as the database that stores the data for the application. Security is an important concern since the application would be getting deployed within organizations that take security fairly seriously. I can count on at least Windows 2000 and at least IE 5.5 being on the desktop, though I’m not sure if the particular browser used to access the application should be much of a concern or not in terms of security.
Ok, so here’s what I had in mind:
Browser --> IIS --> ASP.NET --> Enterprise Services/COM+ --> SQL Server/Oracle
(my thinking is that my data access code would run in the context of a COM+/ES component)
I would like to be able to use Impersonation so that I could run the COM+ component under a certain user account (for my app only) and use Trusted_Connection in my connection string to SQL Server/Oracle (with OSAuthent=1; for the Oracle OLEDB driver). My reasons for wanting to do this are that 1) it seems to me like the most secure way to implement the connection and 2) I could use Windows to handle authentication instead of writing my own code to do so.
Some questions/concerns I have:
- I don’t hear much mention of using this impersonation technique with COM+/ES components. Is it just not well known or are there drawbacks I don’t know about?
- I’ve been reading some newsgroup posts and it sounds like when using Impersonation you lose performance gained by connection pooling. Does anyone know if this is true or false?
- If the Oracle database is hosted on Unix or Linux, is it still possible to connect using Impersonation?
- Should I look at implementing transport-level security (like SSL) when the users authenticate to IIS?
- Anything else I should be thinking about in regards to the tiers/architecture of this application?
Thanks in advance for any time & energy you can lend to me on this. Feel free to use this in your blog (if you have one) if you think it would help get more suggestions from readers or anything like that.
Thanks,
Jason Mauss
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
