MS SubQuery Changes??

Has anyone seen where subqueries collapse into a sum? I have code like the following, which has been running fine for over a year:
UPDATE Reports..DataStats
SET Vendors_Cnt = (SELECT COUNT(*) FROM vVendors__AllRecords),
Vendors_Audit_Cnt = (SELECT COUNT(*) FROM vVendors_InvAudit),
Vendors_Rpts_Cnt = (SELECT COUNT(*) FROM vVendors_Inv12mo),
Vendors_InvUnused = (SELECT COUNT(*) FROM vVendors_InvUnused),
Vendors_InvOne = (SELECT COUNT(*) FROM vVendors_InvOne),
Vendors_InvMulti = (SELECT COUNT(*) FROM vVendors_InvMulti),
Vendors_InvUnpaid = (SELECT COUNT(*) FROM vVendors_InvUnpaid),
Vendors_InvNewer = (SELECT COUNT(*) FROM vVendors_InvNewer),
Vendors_Inv12mo = (SELECT COUNT(*) FROM vVendors_Inv12mo),
Vendors_InvPrior = (SELECT COUNT(*) FROM vVendors_InvPrior),
Vendors_InvSkipYear = (SELECT COUNT(*) FROM vVendors_InvSkipYear),
Vendors_Known = (SELECT COUNT(*) FROM vVendors_Known),
Vendors_Orphaned = (SELECT COUNT(*) FROM vVendors_Orphaned),
Vendors_Active = (SELECT COUNT(*) FROM vVendors_Active),
Vendors_Inactive = (SELECT COUNT(*) FROM vVendors_Inactive),
Vendors_Excluded = (SELECT COUNT(*) FROM vVendors_Excluded)
WHERE (AuditName = @.AuditName)
But now it is generating overflows...and is not equivalent to (ignoring the obvious UPDATE vs. return differences for illustration):
SELECT COUNT(*) FROM vVendors__AllRecords
SELECT COUNT(*) FROM vVendors_InvAudit
SELECT COUNT(*) FROM vVendors_Inv12mo
SELECT COUNT(*) FROM vVendors_InvUnused
SELECT COUNT(*) FROM vVendors_InvOne
SELECT COUNT(*) FROM vVendors_InvMulti
SELECT COUNT(*) FROM vVendors_InvUnpaid
SELECT COUNT(*) FROM vVendors_InvNewer
SELECT COUNT(*) FROM vVendors_Inv12mo
SELECT COUNT(*) FROM vVendors_InvPrior
SELECT COUNT(*) FROM vVendors_InvSkipYear
SELECT COUNT(*) FROM vVendors_Known
SELECT COUNT(*) FROM vVendors_Orphaned
SELECT COUNT(*) FROM vVendors_Active
SELECT COUNT(*) FROM vVendors_Inactive
SELECT COUNT(*) FROM vVendors_Excluded
This appears to have started around the beginning of May. Anyone else suffer after patches?

I am not sure but all SQL Server aggregate functions ignore NULLs except COUNT(*) so it maybe an unknown which is ANSI NULL is creating the overflow. Try the link below for dealing with SQL Server NULLs. Hope this helps.
http://www.akadia.com/services/dealing_with_null_values.html|||How high do those counts go? What are the datatypes for thosecolumns storing the counts? Have you exceeded the integer limitof 2,147,483,647?|||The failed SUM appears to be 44 million. The correct values should each be under 100 thousand. Really wierd!|||Try SELECT SUM instead of COUNT(*) see if it makes a difference. Hope this helps.|||

RLyda wrote:

The failed SUM appears to be 44 million. Thecorrect values should each be under 100 thousand. Reallywierd!

What is the data type for, say, the Vendors_Audit_Cntcolumn?sql

MS sql sever connection string

Hi everybody
Can anybody write a code and tell me how can i access a table called
KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields called
KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those fields
--
Message posted via http://www.sqlmonster.comHi,
Access the table:-
1. Login to QUERY analyzer by providing the user name password
2. From the database pane, select the database where the KF_STATUS table
resides
3. Write the below query
select KF_ID ,KF_DATE, KF_STATUS FROM KF_STATUS
4. Execute the query by pressing Control and E simulteneously. This will
give u result.
THnaks
Hari
SQL Server MVP
"gurvinder gill via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:f68623ec640847c497a4e72817032580@.SQLMonster.com...
> Hi everybody
> Can anybody write a code and tell me how can i access a table called
> KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields
> called
> KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those
> fields
> --
> Message posted via http://www.sqlmonster.com

MS sql sever connection string

Hi everybody
Can anybody write a code and tell me how can i access a table called
KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields called
KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those fields
Message posted via http://www.droptable.comHi,
Access the table:-
1. Login to QUERY analyzer by providing the user name password
2. From the database pane, select the database where the KF_STATUS table
resides
3. Write the below query
select KF_ID ,KF_DATE, KF_STATUS FROM KF_STATUS
4. Execute the query by pressing Control and E simulteneously. This will
give u result.
THnaks
Hari
SQL Server MVP
"gurvinder gill via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:f68623ec640847c497a4e72817032580@.SQ
droptable.com...
> Hi everybody
> Can anybody write a code and tell me how can i access a table called
> KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields
> called
> KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those
> fields
> --
> Message posted via http://www.droptable.comsql

MS sql sever connection string

Hi everybody
Can anybody write a code and tell me how can i access a table called
KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields called
KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those fields
Message posted via http://www.droptable.com
Hi,
Access the table:-
1. Login to QUERY analyzer by providing the user name password
2. From the database pane, select the database where the KF_STATUS table
resides
3. Write the below query
select KF_ID ,KF_DATE, KF_STATUS FROM KF_STATUS
4. Execute the query by pressing Control and E simulteneously. This will
give u result.
THnaks
Hari
SQL Server MVP
"gurvinder gill via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:f68623ec640847c497a4e72817032580@.droptable.co m...
> Hi everybody
> Can anybody write a code and tell me how can i access a table called
> KF_STATUS which is stored in MS SQL server2000. and it thas 3 fields
> called
> KF_ID ,KF_DATE, and KF_STATUS and how can i display contents of those
> fields
> --
> Message posted via http://www.droptable.com

MS SQL sever 2000 string connection problem

Hi, i'm very new to Web Matrix. I've been following the C# end-end application tutorial exactly but i can't seem to run my application.
The code given in the link below is for MS Access but I'm using MS SQL 2000, so i'm not sure of the syntax for the code provided below (string connectionString...)
Please help..thank u!!

http://www.asp.net/webmatrix/guidedtour/section91/bookrequestcontrol.aspx

string connectionString = @."Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\TempProjects\LibraryDatabase.mdb";

public void LoadRequestedBooks(){

try

{

string mySelectQuery = string.Format("SELECT books.* from books, requests where books.bid = requests.bid and requests.mid = {0}", Session["userid"]);

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectionString);

System.Data.DataSet myDataSet = new System.Data.DataSet();

System.Data.OleDb.OleDbDataAdapter myOleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(mySelectQuery, myConnection);

myOleDbDataAdapter.Fill(myDataSet);

DataGrid1.DataSource = myDataSet.Tables[0];

DataGrid1.DataBind();

myConnection.Close();

}

catch(Exception exp)

{

Label2.Text = "<br>Error: " + exp.Message + "<br>";

}

}Hi,

You may take a look at another section of the Web Matrix tutorial,
which is primarily focus on data access with SQL/MSDE Server:
http://asp.net/webmatrix/tour/section2/newconn.aspx

Colt|||hi again. thanks for the site, but my problem is the database is not storred locally. it's on the server. How should i change the syntax in the following code to suit MS Sql Server2000? Your help will be very much appreciated. Thank you.

string connectionString = @."Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\TempProjects\LibraryDatabase.mdb";
public void LoadRequestedBooks(){

try
{

string mySelectQuery = string.Format("SELECT books.* from books, requests where books.bid = requests.bid and requests.mid = {0}", Session["userid"]);
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.DataSet myDataSet = new System.Data.DataSet();
System.Data.OleDb.OleDbDataAdapter myOleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(mySelectQuery, myConnection);

myOleDbDataAdapter.Fill(myDataSet);
DataGrid1.DataSource = myDataSet.Tables[0];
DataGrid1.DataBind();
myConnection.Close();

}
catch(Exception exp)
{
Label2.Text = "<br>Error: " + exp.Message + "<br>";

}

}|||Hi,

I see.. You can connect to a remote DB server by using its IP address,
E.g. "Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
FYI:http://www.connectionstrings.com

Colt

MS SQL server handling unicode data

Hi,

I want to fetch Unicode data from MS SQL server 2005.I have created one user with russian language characters.

Following is my code snippet:
-----------
wchar_t wchar1[55];
char VALUE[255];

while (rc != SQL_NO_DATA)
{
rc = SQLFetch(hstmt);
if (rc != SQL_NO_DATA)
memset(VALUE,0,255);
int length=wcstombs( NULL,(wchar_t *)&wchar1, wcslen(wchar1));
char* strChar = (char *) malloc((length +1 )* sizeof(char));
if (strChar != NULL)
{
count++;
memset(strChar,0, length + 1 );
int i =wcstombs(strChar, wchar1, wcslen(wchar1) );
printf( "i is %d\n",i);
}
printf( "The length is %d\n",length);
wprintf(L"\n <%s>", wchar1);
printf("\n simple char <%s>", strChar);
wprintf(L"\n simple char <%S>", wchar1);
}


-----------

CAn anybody help me ?
I think problem is in fetching of name.
Does anybody have working code for fetching unicode data from SQL server 2005.

Also I want query that will tell the locale setting of the server and also client?

Thanks--the sql server datatypes nchar/nvarchar/ntext are for unicode strings.

if you are using char/varchar/text in your columns, they won't be unicode.|||I know that nchar,nvarchar dayta types are unicode datatypes.
But C compiler is not able to resolve them.
It gives me error unkhown identifier.
Do u know which header files to be added for datatype nchar?
If you have any program for fetching unicode data,Please can you sned to me.
Its urgent.:eek:
I will be very thankful to you...|||Use the T-SQL COLLATE function and supply the corresponding collation for Russian.|||My problem is different.Actaully i want to support internationalization in my product which means product should support all the languages.
My product is for user and password management for MS SQL server 2005.
I want to fetch userdata in all languages.Not only in English(ANSI)
SO every time i will niether be aware of the collation setting of the database nor the locale setting when user is created.(language in which user has been created)
So in this situation I need generalise solution.
for example:
--------------
In Oracle using OCCI,we can set environment to OCCIUTF8.
Code for this is
Environment *env = Environment::createEnvironment(OCCIUTF8,OCCIUTF 8);
{
Connection *conn = env->createConnection(userName, password,
connectString);

This will work irrespective of NLS setting of server.

--------------

IN the same way is there any environmenet setting which needs to be done for supporting internationalisation?

I hope this will explain you all my realy problem.
Please help me....
I am using ODBC API for this.
If possible if anybody is having working code in c .........;)
that will be very helpful

waiting for your help...

Regards
Sam

MS SQL Server Database Connection Problems

Hi

I can connect successfully to the Microsoft SQL Server "Beauty" (get also nonzero for successfull connection). If i run the application (see code below). I get following Error Message "Illegal Objectname 'TestTabelle' ". I double checked the SQL statement and it should be correct. Even the spelling of TestTabelle is right. For testing purposes I've also included 5-6 data records in the tabel.

I try to connect via ODBC. I've created the database with MS SQL Enterprise Manager. Right Click -> Create New Database. I called it "Test" and created in "Test" a new Table, which is called "TestTabelle".

I found information how i can connect to a ms sql server "Beauty", i've also found how i can connect to a table, BUT where is the information which Database ?`in my case it should be Test.

how can i setup a default database... Cause in my application i can't access my Test Database.... See code below :(

Why is this code BOLD not working if i try to select a database ?

Thank You
Indian

{
CDBVariant value;
char sql_statement [2048] = "";

//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty;UID=Administrator"), CDatabase::noOdbcDialog);

//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle")); <- NOT WORKING... GETTING ERROR MESSAGE if i use this sql_statement " USE TEST; SELECT * FROM TestTabelle"
rs.Open(CRecordset::forwardOnly,sql_statement);

//Get quantity from Database
int n = rs.GetODBCFieldCount( );
while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )
{
rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_pstring));

rs.GetFieldValue("product",value);
m_Buy_List.SetItemText(i,2,LPCTSTR(value.m_pstring ));

rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_pstring ));
}
rs.MoveNext( );
}

//Close records access.
rs.Close( );

//Close database connection.
db.Close();

}double-click on Administrator (login) and select the default database from the drop-down box at the bottom of this dialog box.|||Better yet, use a more explicit query. Try:

strcpy(sql_statement,_T("SELECT * FROM Test.TestTabelle"));|||Better yet, use a more explicit query. Try:

strcpy(sql_statement,_T("SELECT * FROM Test.TestTabelle"));|||Sorry. Just replace the line in your code.

You could also skip ODBC, and specify the database in your connection string by using OLEDB.

Try this line in your code to connect to the server...

//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("Provider=SQLOLEDB.1;Data Source=Beauty;Initial Catalog=Test;UID=Administrator"), CDatabase::noOdbcDialog);|||i figured it out

thx

MS SQL and ASP.NET connection problem

Hi,

Im trying to set up a ASP.NET webform to a MS SQL database. Im currently databinding to a datagrid. My code seems to compile correctly, no errors, however the data does not display.

I have tried to connect to the database server using the data adapter preview and works perfectly. But once i get the webpage running nothing displays from my VB code.

Does anyone have any clue to a resolution? THANK YOU ALL!I don't know if this is the problem, but I think after setting the datagrid's datasource property to the dataset, you have to type this:
"datagrid1.bind" or maybe "datagrid1.databind" (one of them: I can't quite remember)

Hope this helps.
Wes