Database & SQL Forum RSS Feed

Cannot open Excel File from VB2005 using SQL Server Stored Procedure

Tue, 07 Feb 2012 17:27:46 -0800

Hi, I need help!!!

I developed a program in VB 2005 which opens an Excel file using Interop, and I call it from an SQL Stored Procedure using xp_cmdshell. Everything works fine when I am working under Windows 2003 Server. The problem is when I try to call my program the same way, but under Windows 2008 Server. It throws an exception at the moment my programs tries to open the Excel file. I assumed it was an authentication issue, so I changed the login for the SQL Server Service to an administrator account, and I granted Full Control to Everybody over the folder where the Excel file resides, but the problem persists.

The error message says that the Excel file cannot be accessed because either the file is in use by another program (which it isn't) or the user doesn't have enough privileges.

Same problem happens under Windows 7.

I am using "Microsoft Excel 12.0 Object Library" in my Project.

By the way, I tried to run my program with a double click in windows explorer and it opened the Excel file with no problem. The problem is when I call my program from an SQL Server Stored Procedure using xp_cmdshell.

Any ideas?

Thanks in any advance for your help.

Giovanni @ Guatemala.

Help..church offering software

Sat, 04 Feb 2012 05:08:33 -0800

anybody want to share their talent to our church? we need simple program for our tithes and offering. Thanks.

Browse an Array In Visual FoxPro

Tue, 31 Jan 2012 09:29:19 -0800

Greetings all,
I'd like to know if there is any way to BROWSE an array in FoxPro like is possible when creating a cursor from sql?

Amazing article about SQL!

Wed, 25 Jan 2012 06:27:21 -0800

Hey, fellas!

Found out this SQL article: http://en.sdjournal.org/nosql-to-newsql-fast-changes-in-database-world/
It's worth reading. I strongly recommend it just as the whole SDJ magazine.

Cheers!

This post has been deleted.

Sat, 03 Dec 2011 11:47:29 -0800

This post has been deleted.

DTD Bounds

Sat, 03 Dec 2011 11:45:38 -0800

I'm creating a DTD and one of the restrictions I need to implement is that the value of an element has to be bounded by 2 numbers. For example:

<!ELEMENT Value (Number)>
<!ATTLIST Value
max CDATA #IMPLIED
min CDATA #IMPLIED
>
<!ELEMENT Number (#PCDATA)>

And the value of number has to be between min and max. Is this possible with a DTD?
I know this is possible with xmlschema but I'm wondering if this can be done with a DTD. Thanks.

Compare list in PL/SQL

Mon, 14 Nov 2011 15:48:44 -0800

Hi all,
I'm a java programmer but currently have a task to compare different list and output their common element in PL/SQL. In java this is very simple but i'm not sure how i would handle this in sql since i'm fairly new to it.

ex
l1='A,B,C,D'
l2='B,C,D'
l3 ='A'

l1+l2 = (B,C,D)

l1+l3 = (A)

l2+l3 = 0

Has anyone ever done anything similar in sql? Would really appreciate any help on this.

Need General Newbie Guidance

Wed, 21 Sep 2011 10:16:49 -0800

Hello;

This is my first post, so bear with me! Wasn't sure which forum to post this to, but thought this might get me closest to my answer.

Looking to build a website application. I want to build an online scheduling application for a restaurant.

I have some very rudimentary programming and database skills. (I mean extremely rudimentary!!)

What Im wondering is, what language should I use to program this in? I am a blank slate, and will be learning this language from scratch.

My parameters are to keep the cost down to as little as possible. Free and opensource is fine with me. But if I needed to spend a little bit of money to have it current, I would do that.

Questions:

1) Where should I host my website?
2) What database should I use?
3) Which web programming language should I use?
4) Reasonable time frame to learn and get this online, considering I have average intelligence?
5) Am I crazy?

Basic Functionality:
1) Back end administration for the schedule maker
2) Employees should be able to log in and submit time off requests, see schedule.
3) Enter and update shifts to be filled.
4) The program to run a matching scheme, where by the employees get married up to the shifts based on their skill vs importance of shift.
5) Produce a graphical output of schedule, which then could be emailed to the employees.

Thanks to all replys! ps..if this should be in another forum, please let me know!

Jason.

Calculated field producing possible 4 different results

Fri, 12 Aug 2011 09:14:22 -0800

I need help with the syntax in a calculated field. I will give you an example of what I am trying to accomplish.

Yes and Out of State $9,000 result in field $9,000
No and Out of State $6,500 result in field $6,500
No and In State $5000 result in field $5,000
Yes and In State $7,500 result in field $7,500

Result: IIf([y/nField]=-1,[InStateAndYes],[InStateAndNo]) And (IIf([y/nField]=0,[OutStateYes],[OutStateNo]))

The [InStateAndYes] is a field in the table which contains 7500, similarly the other fields contain:
[InStateAndNo]=5000
[OutStateAndYes]=9000
[OutStateAndNo]=6500

Can this be done?
Thanks for your time.

Gathering Data From Database

Wed, 03 Aug 2011 11:24:34 -0800

Hello guys,

I have an application that uses a local database (oracle 10g) and on this application I created a huge project, it's all on my local database.

This application does not export data in any way and I really need to recover my work from this app.

There is any way I can get this data from the oracle database? Maybe some sort of reverse engeneering or something?

I would really apreciatte any idea.

Thanks a lot!

Lucas

Need help with SQL query

Mon, 06 Jun 2011 12:22:39 -0800

I am very new to SQL and not a programmer by trade. I know enough to be dangerous. I need a query (I'm using Toad for SQL) that extracts information when multiple conditions from the same table are met ... essentially pull data when it equals this AND equals this. Example:

Last First Order# Item
Doe John 1111 Candy
Doe John 1111 Gum
Doe John 1111 Chips
Doe John 2222 Candy
Doe John 2222 Chips
Smith Jane 3333 Candy
Smith Jane 4444 Candy
Smith Jane 4444 Chips
Smith Jane 5555 Gum

Right now, my select statement is "Select Last, First, Order # when Item in ('Candy', 'Gum')". I'm getting six returns since it is matching entries with either "Candy" or either "Gum". However, I need a statement that pulls a value when both 'Candy' and 'Gum' are purchased by the same customer in the same order #. Given the data above, I only want "Doe, John, 1111" returned. Can anyone help? And just for the record, this is not a homework assignment ...

Data Missing after a transaction is said "committed successfully"

Sun, 05 Jun 2011 21:16:29 -0800

Hi,

My application requires several clients (can be at the same time) to write the data to the SQL Server 2008 through the WEB Service.

The Web Service is just a transaction and return "success" or "fail". I found that after a few hundreds or thousands of transactions, though the Web Service return "success", the data is missing after that transaction is said committed successfully.

The followings is the piece of code that the Web Service will call.

public static int ExecSQL(string[] SQL)
{
int I = 0;
int J = 0;
string S = "";
try {
CN = GenConnection();
CN.Open();
SqlConnection.ClearPool((SqlConnection)CN);
TR = CN.BeginTransaction();

CM = GenCommand();
CM.Connection = CN;
CM.Transaction = TR;
CM.CommandTimeout = 600;
for (J = SQL.GetLowerBound(0); J <= SQL.GetUpperBound(0); J++) {
S = SQL[J].Trim();
if (!string.IsNullOrEmpty(S)) {
CM.CommandText = S;
I = CM.ExecuteNonQuery();
}
}
TR.Commit();

CN.Close();
CN.Dispose();
GC.Collect();

return 0;
} catch (Exception E) {
try {
if ((TR != null)) {
if ((TR.Connection != null)) {
TR.Rollback();
}
}
} catch {
}
LogError(E, S);

CN.Close();
CN.Dispose();
GC.Collect();
return 255;

} finally {
}
}

I have log the data before calling this sub-routine and after this sub-routine. The data missing is found just after successfully committed. Please help.... it's been distributing me for several months.....

Best regards

ER Diagram Help needed ! Please ...

Tue, 26 Apr 2011 11:51:18 -0800

Hello ..this is my first post in her (and english is my second language so bare on me please )

i'm having some problems deciding if my ER diagram of a Dental Clinic Information system is correct
let me start of naming the Entitys that i have :
-Pateint
-Payments
-Appoitments
-Diseases
-Diagnose/Treatment
-Prescription (medicine)

i had many many relation connections between them ..really am lost
at the end i came up with a star-shaped er diagram ...pateint in the Center and all other Entitys are connected to it ...
is that correct ? in other words ..HOW can i know if my er diagram is correct ..
help apreciated
thanks in advance !

DbSchema - Database tool for managing database data and schema

Sun, 10 Apr 2011 08:21:03 -0800

Database tool for managing database data and schema

DbSchema provides you with a complete and powerful solution for databases. Our target is to bring both database schema and data in a single unified workflow. This provides an efficient and productive environment to work in, with improved awareness of application schema architecture and easy access to application data.

Support for offline work and schema synchronization, make DbSchema an excelent tool for team work as well as a great tool for converting and merging database schemas.

Here are some key features of "DbSchema":

Import database schemas:
· Load JDBC driver
· Connect to any JDBC compatible database. Several DBMS have already been tested, but more will be added later.
· Currently tested DBMS include: Oracle, MySQL, PostgreSQL, DB2 for Windows/Linux, Cache, DaffodilDB, FrontBase, HSQLDB, Informix, JavaDB/Derby, Microsoft SQL Server, Mimer, Pervasive, PointBase, Sybase SQL Anywhere, Sybase ASE.
· Schema import includes: tables, views, foreign keys, procedures, triggers, sequences and comments.

Edit database schema:
· Uses an abstraction level between internal database schema representation and any particular database.
· Edit tables, columns, indexes, foreign keys, procedures, triggers, sequences and comments.
· Edit your schemas Graphically using Layouts, including: tables, views, foreign keys, etc...
· Create and use multiple Layouts inside a project.
· Drag, pan and zoom Layouts using simple mouse movements.
· Supports Drag&Drop from project tree to graphical Layout.
· Highlight Foreign Keys for a given table, which trigger on delete events.
· Generate SQL statements directly from schema graphical Layout.
· Generate schema scripts to any particular database.
· Change schema data types from one database to another.
· Edit schema offline support.

Browse database data:
· Browse data from multiple tables simultaneously.
· Set filters over browse data.
· Order browse data on one or more columns.
· Unlimited browse depth.
· Paginates browse data.
· Single or multiple record views.
· Insert, update, and delete records.
· View Large Objects (LOBs) fields as image, html, text or using an external application.
· Saves browse sessions in project file, which can later be browsed again.

Edit and execute SQL:
· Edit SQL statements.
· Syntax highlight covering database keywords, functions, table and view names.
· Auto completion of table, view and column names.
· Quick Click&Show tables or columns in SQL pane and show them in Layout.
· Execute SQL statements individually or as scripts.
· Paginate and order SQL results, after query execution.
· Keep multiple SQL result panes, and refresh query results.
· Explain SQL execution plan.
· Saves SQL scripts working sessions in project file, which can later be continued.

Schema Synchronization:
· Synchronize project schema with another project or database.
· List schema differences.
· Select which differences to keep, either on local or remote schema.
· Generate Merge SQL scripts based on schema differences.
· Upload differences on to database, with automatic step by step execution.
· Synchronize schemas offline, which enpowers team work.

Scripts and documentation:
· Generates SQL schema creation scripts for any database.
· Document tables, columns, foreign keys, indexes, etc... by adding Comments
· Generates schema specification as an HTML document.
· Print schema Layouts with precise control on actual print size, using Zoom print feature.
· Generates schema Layout image files, in a few formats (PNG [raster] or SVG [vectorial])
· Provides a Database Template System, which allows to create, edit and maintain database templates
· Several Database Templates are already supplied, but new ones can be learned as well, by connecting to target database.

Requirements:

· Java 1.6

www.dbschema.com

postgresql: USING CURSOR in extracting data in one database and insert

Wed, 16 Mar 2011 20:45:14 -0800

here is another algorithm using cursor but im having a hard time fixing its error...

    CREATE OR REPLACE FUNCTION extractstudent()
    RETURNS VOID AS 
    $BODY$
    DECLARE
    	studcur SCROLL cursor FOR SELECT fname, lname, mname, address FROM student;
    BEGIN    
    	open studcur; 
    	
    	Loop
    	--fetching 1 row at a time
    	FETCH First FROM studcur;
    	--every row fetched is being inserted to another database on the local site
    	--myconT is the name of the connection to the other database in the local site
    	execute 'SELECT * from dblink_exec(''myconT'', ''insert into temp_student values(studcur)'')';
    	--move to the next row and execute again
    	move next from studcur;
    	--exit when the row content is already empty
    	exit when studcur is null;
    	end loop;
    
    	close studcur;    
    	
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION extractstudent() OWNER TO postgres;

im just new in postgresql and i know this code has alot of error
hopefully someone can help me figure them out

thanks for dropping by

Access 2007 - Enable Field Query

Wed, 16 Mar 2011 11:05:27 -0800

I have a form with a sub-form within.

The main form consists of 3 fields. The first is a combo box "Materials/Description of Work", the second is "Unit of Measure" and the third is "Quantity".

I would like the combo box to disable itself once I tab to the sub-form - this is to prevent accidentally changing the selection in the combo box.

When I go to a new record I would like the combo box to be re-enabled so I can make a new selection.

I am only now learning how to write code so I need all the help I can get.

Thanks in advance for your input

Please help with a small program/flash

Sat, 19 Feb 2011 11:33:15 -0800

Hi!

I need help with a certain program that does a few simple operations - it can be anything - a soft coded in any programming language, or in flash or anything you can think about.
And this is what this program does :
Input box - allows me to insert how many .txt lists I want to use, and depending on the number I chose (let's say 5), then 5 other buttons will appear (or become active) and each of them will let me browse for each list (the same list can be used for two or more of the buttons if I want).If you find too much creating an unlimited number of buttons, then I think 10 will suffice, arranged in a single row, one after another on a 1024 pixels wide window.
Each button will also have own value output box.
All the .txt lists contain values of any kind (alphabetical, numerical, special characters), and an individual value is everything contained on a single line - if the line breaks (ENTER), then that's another value and so on.The lists can contain any ammount of values (lists).
Randomize button - as you might have already guessed, this button will simply take one value at random from each list and display it in the corresponding output box for each button (list).So if a value is taken from the list asociated (browsed) with the third button, then the value will be displayed in the output box associated with that button.The output boxes should also be all arranged on a single line, just like the buttons, and the display font should be no bigger than 10 pts and not bold, as to as many characters can enter in the output box.
Generate list - this button will generate and save a list as another .txt with the name of 'Generated list' or something similar, and int this list will include combinatorics of all the terms from all the lists that I chose to include in the buttons that I chose to activate, for instance, if I have 5 buttons active, then the values will be taken from the 5 lists chosed, one under each browse button.
The lists might contain values like owl, apple, pizza, sport, fair.
And the result should be saved on a single line in the output .txt like this Owl apple pizza sport fair, then on the next line, the next combinatoric result.This is just a random example so you can understand better what I need.
That's all!
In conclusion is just a small program, or applet, or javascript, or flash that simply browses text lists with values separated into rows (each value on its own row) and then it does two things with them - creates a random output of values with one random value selected from each list at the press of a button and outputs a list of all the combinatorics from all the lists selected at the press of another button.
If you know any such program or a flash script/java script, or you can program it (it should not use more than the basics of programming in any language and certainly will not take more than 15 minutes from an experienced programmer), please let me know.
Thank you in advance!

Best regards!

NCPDP D.0 layout

Fri, 18 Feb 2011 06:58:48 -0800

I need the file record layout for NCPDP D.0 if anyone could post
or e-mail form me.
Thanks

Please Help Debug VBA

Thu, 27 Jan 2011 11:30:51 -0800

Hi Everyone,

When trying to run this vba i'm getting a Debug error. I'm new to VBA so any help is appresheated.

Eng-eng dictionary database is needed?

Thu, 20 Jan 2011 10:59:39 -0800

where i can find the eng-eng dictionary database. if anyone have then plz send me at ansriazch@gmail.com. i needed it badly

Ranking The Standard Deviation

Tue, 18 Jan 2011 09:08:10 -0800

I’m hoping someone can help me with my newest rank issue.
I’m trying to get a rank my standard deviation so it will be a number below 1, the numbers I'm trying to rank are below

Example:
CODE
Rank1: 1+DCount("*","[Table1]","[standard deviation]<" & [standard deviation])

In addition, my rank comes out to "1"

[quote]
-0.346475955413238
-0.393008929311432
-0.313430681489258
0
-0.408248290463863
-0.39972826427894
-0.379339734056029
-0.123600171413157
-0.365864893089862
-0.183973875868388
-0.371230105706986
-0.262369301094045
-0.321886521586874
-0.391106441996541
-0.137470711646278
-0.124342264640857
-0.228627545996961
-0.314589973831583
-0.217890019842972
-0.271519018007927
-0.251561710306367
-0.249946076975505
-0.269279732015007
[quote/]

Thanks for all your help

How to get the avg in two fields

Thu, 13 Jan 2011 14:38:41 -0800

I’ve been searching the web and can’t find a straight answer.

If I was to get an average of two fields in access how would I do it? I would think something like this

Avg([3MRank]),([3MRank])
or
Avg([3MRank]) and ([3MRank])
or
=Avg([3MRank]) + ([3MRank])

Could someone please just show me how I would do this?

Thanks

Attachment: CV_test.zip (42981 bytes | downloaded 64 times)

YAD - a new "NoSQL" library

Fri, 24 Dec 2010 08:44:38 -0800

YAD is a new library for creating and managing databases "NoSQL”.
It is written in C++0x and can be used by users of this language.
It is a very compact library which provides basic functionality for
managing the database.
Its main feature is that it is possible to assign a type to the
fields, so that data entry is validated directly from the functions of
the library.
Version 1.0 is currently available for Linux but can be ported to
other OS that support the C++0x.
It is distributed as freeware and is freely usable for personal work
(not commercial).

www.fjmsoftware.altervista.org

NEED HELP MySQL :(

Sat, 18 Dec 2010 21:39:25 -0800

Can anyone help me how can I connect MySQL to C# Windows form application.

Thanks in advance :)

SQL Server 2005 Express: Cannot connect to Server Name

Tue, 28 Sep 2010 02:32:58 -0800

Good day!
I am newly in SQL Server 2005 and now I encountered error when i try to login. This is some of the information:

Microsoft SQL Server 2005 Express Edition

Server Type: Database Engine
Servername:Servername\MSFW
Authentication:SQL Server Authentication
Login: sa
Password:xxxx

and when i press the connect button the error appear:

Error:
Cannot connect to Servername\MSFW

Additional Informations:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does not allow remote connections.(Provider:SQL Network Interfaces error:26-Error Locating Server/Instance Specified) (Microsoft SQL Server).

I have no idea where i can solve this problem..

Any help is highly appreciated..

Thank you in advance.