|
I finally got it resolved. I actually had the server manager running connected to the sql server. So I took and closed that and refreshed the datasource and worked fine. thanks for the help all
|
|
|
|
|
Hi,
I need to handle missing select values programattically. this is what i have
insert into A(id, name, age, value, DOB)
select 1,'Max','21','a','100928' union
select 2,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4,'xx','100938' union
select 5 'Fed','24','xx','100928'
all my insert statement are stripped off via regex. the structure of insert statement is as show above. but at times it may miss "name" and "age" and it gives me error something like
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions
now those insert scripts are quite bulky. sometime 100MB I would like to automatically handle missing name and age. I am not sure how could i do that? could you please give me some idea?
|
|
|
|
|
You could pass Null as the value if you don't know the name or age.
Wout Louwers
|
|
|
|
|
how do i pass null value without modifying below block of query
select 1,'Max','21','a','100928' union
select 2,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4,'xx','100938' union
select 5 'Fed','24','xx','100928'
Lets just assume above text is a read only text.
|
|
|
|
|
If you don't change the query, the insert will not work. The query should look like this:
select 1,'Max','21','a','100928' union
select 2, Null, Null,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4, Null, Null,'xx','100938' union
select 5 'Fed','24','xx','100928'
Wout Louwers
|
|
|
|
|
Its not the insert you need to change, its the code that generates it. Without seeing the code its difficult to help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
The application was developed in .NET and SQL 2005. Originally, we'd thought that it'd be a web-based application (i.e. the SQL database will be hosted by us and users can connect to it).
But some clients want the database to be hosted on their computer itself. Of course the application needs the database to run, however, by putting the database on the client's machine we will lose control of our data.
I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans.
Nothing is altered in the database--we are simply reading data from the SQL table, so should we explore other things (indexed flat files?) to make this into a software product without losing control over the data? The Database size is around 30GB.
G. Satish
|
|
|
|
|
You have a number of options - none of which is having 30gb of text files.
You can manage the database access by permissions so only you can get into the database. Most DBAs discourage this as they are all control freaks.
You can encrypt the data and the code (stored procs) this will probably be more acceptable and gives reasonable protection.
There are probably others that I'm not conversant with.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is possible to make a Sql Server database readonly.
ALTER DATABASE database-name SET READ_ONLY
However, this can only be done once the database has been attached to the server instance.
|
|
|
|
|
Satish - Developer wrote: I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans.
AFAIK, there is none; your application runs under the security-restrictions of the human that started the application. This gets mapped to a SQL-Login, and that determines what securables you're allowed to see, and what not.
ASP.NET has it's own Windows-identity defined; perhaps you can do something similar?
I are Troll
|
|
|
|
|
Satish - Developer wrote: I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans.
Thats because there isn't one. The best you can do is either restrict access by using sql security (plenty of details in the help files) or encrypting your database.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Your data? What do you mean your data?
|
|
|
|
|
Hi all,
I have to import a table & a Query from c:\Programfile\asd\MyProject\test.mdb to c:\document&settings\ applicationdata\test.mdb.
I have created & established the Connection string & all. the only thing i am not getting is Query needed to pass...
Please help..
Ashish
|
|
|
|
|
Open the destination database using Access, then click File-->Get External Data-->Import and select the tables/queries you want. This is the simplest way!!
|
|
|
|
|
Thanx but i need it to implement in my C# code...
So i need a query to execute this step
|
|
|
|
|
If you are doing it in the client (c#) then you need to get the data from one database, hold in in a datatable while you establish a connection to the target database and use the second connection to write the records over that connection.
You can of course open 2 connections before starting the process!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I try to access a local postgres database (actually it is a PokerTracker3 database) from Visual C++ 2008, because I'd like to do a lot of regular analysis, which takes hours of work with PT3, but if I write a standalone program, it can make it for me by a push of a button. I am a slightly advanced C++ programmer, but I'm new to databases.
I tried at first with libpqxx, but I couldn't even compile it's test programs. It had configuration problems I couldn't solve.
Then I found out about ODBC, and I try this for now. So far I updated postgres to 8.3.9 with psqlodbc 8.4 (that's what was automatically installed with it). I found an example on the net with an access database here:
http://www.dmcmsp.com/newHome/odbc/cplusplus/index.htm
I tried to amend it to work with postgreSQL. At first, I tried to access the lookup_hand_groups table. Server name is postgres, database name is PT3_2010, username is postgres, password is dbpass.
It's a console application (later I will write the reports to a csv file), that's what I have so far:
#include "stdafx.h"
#include "windows.h"
#include <sqlext.h>
int main(int argc, char* argv[])
{
printf("Starting\r\n");
SQLHANDLE hEnv;
SQLHANDLE hDbc;
SQLHANDLE hStmt;
SQLRETURN status;
status = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
status = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
status = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
status = SQLSetConnectAttr(hDbc, SQL_LOGIN_TIMEOUT,(void *) 5, 0);
status = SQLConnect(hDbc,(SQLWCHAR *) "postgres", SQL_NTS,
(SQLWCHAR *) "postgres", SQL_NTS,
(SQLWCHAR *) "dbpass", SQL_NTS);
status = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
status = SQLSetStmtAttr(hStmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_STATIC, 0);
status = SQLExecDirect(hStmt,(SQLWCHAR *) "Select * from lookup_hand_groups", SQL_NTS);
printf("After settings\r\n");
int rowcnt;
rowcnt = 0;
while (true)
{
status = SQLFetch(hStmt);
if (status == SQL_NO_DATA_FOUND) break;
rowcnt++;
}
printf("rowcnt\r\n");
printf("There were %d rows of data returned by the SQL Statement.\r\n", rowcnt);
status = SQLFetchScroll(hStmt,SQL_FETCH_FIRST,0);
if (rowcnt > 0) {
SQLSMALLINT numcols;
status = SQLNumResultCols(hStmt, &numcols);
if (status == SQL_SUCCESS || status == SQL_SUCCESS_WITH_INFO) {
printf("Number of returned Columns is: %d. \r\n",numcols);
}
else {
printf("Error returning number of Columns...\r\n");
}
# define SQL_MAX_NAME_SIZE 128
SQLSMALLINT iCol;
iCol = 3;
SQLWCHAR namebuffer[SQL_MAX_NAME_SIZE + 1];
SQLSMALLINT buffersize;
SQLSMALLINT typeOfData;
SQLUINTEGER columnsize;
SQLSMALLINT decimaldigits;
SQLSMALLINT nullable;
memset(namebuffer, 0, SQL_MAX_NAME_SIZE + 1);
status = SQLDescribeCol(hStmt, iCol, namebuffer, SQL_MAX_NAME_SIZE, &buffersize, &typeOfData, &columnsize,
&decimaldigits, &nullable);
printf("Column name of column number %d is %s.\r\n",iCol, namebuffer);
int icol = iCol;
long nchars;
char buffer[512];
memset(buffer,0,sizeof(buffer));
status = SQLGetData(hStmt, icol, SQL_C_CHAR, buffer, sizeof(buffer) - 1, &nchars);
printf("Data from Column %d is '%s'.\r\n",icol, buffer);
}
status = SQLCloseCursor(hStmt);
if (status == SQL_SUCCESS || status == SQL_SUCCESS_WITH_INFO) {
}
else {
printf("Error with SQLCloseCursor...\r\n");
}
status = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
status = SQLDisconnect(hDbc);
status = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
status = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
printf("All Done!\n\nPress Any Key To Continue...");
getchar();
return 0;
}
This tutorial mentions an ODBC32.lib, what I should add to the project link options, but I couldn't find it. The above program cannot connect to the server. At #5 status is -1. What should I write to #5? In the tutorial it says that I should define an ODBC DSN, but I don't have a clue, how to do it. Could you point me to the right direction? Maybe to a psql-odbc-VC++2008 step by step tutorial for dummies?
|
|
|
|
|
hi all
1) how can i get the sql server version programaticaly (sql transaction)
2) how can i tell a connection (sqlConnection in .net) to connect with the specified sql instance ?
thanks so much!!!!
|
|
|
|
|
Hi
1)
SELECT @@VERSION 2) By specifying it in the connectionstring, as suggested in the C# forum
I are Troll
|
|
|
|
|
Hi all, I have a question. How do I get the table name where the data came form
if I am querying multiple tables where all the tables have the same number and type
of columns.
thanks
|
|
|
|
|
If I understand you correctly, you would have to make your query give an indication where the data came from, such as:
select e.description as EmpDescription, t.description as TerritoryDescription
from employee e, territory t
where e.terrID = t.terrID
This shows that the 2 tables, employee and territory both have a column, "Description" and you have renamed the columns in your select statement by using the "as ..." qualifier.
Hope this helps.
|
|
|
|
|
Hi, David thanks for you answer. I am trying what you just said. I will let you know
the result.
sorry for delay.
thanks again.
|
|
|
|
|
Hi Experts
i am using sql server 2005. i Have a table with Five Field structure Give Below
Col Name Data type
Code int
Name varchar
Address varchar
Fee Decimal
AddDate DateTime
Now Suppose i change the Structure of the table
like
Col Name Data type
Code int
Name varchar
Fee Decimal
AddDate DateTime
Address varchar
and Save It
now i want to Compare Stucture of Last modified and Current Table
and know the alter position of the column name
Dinesh Sharma
|
|
|
|
|
Take a look at the system views, they have all this information. Columns have an ordinal position!
If you are looking at synchronising the databases then look at the Red-Gate tools
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm looking at an embedded database requirement, basically users need to operate unconnected so I was intending to supply SQL Compact but it seems it does not support stored procs. There goes 99% of my DB code.
Is there a single user SQL database that supports procedures (forget Access I get hives just thinking about it). I would like to have a single codebase for both the main database and the local DB and I really didn't want to install a server version.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|