|
Relational databases don't act like that. You will need to process the returned data using a language like C# or VB, or a stored procedure.
|
|
|
|
|
Hi!!
I'm trying to connect to a remote server using SQL Server Management Server. I'm from South Africa and the website is hosted in the States. We have 2 databases hosted there, apparently on the same server. I can connect to the one, but the other server keeps on telling me "Microsoft SQL Server, Error 53". It just fdoes not want to connect. I can connect from the website http://sql.glodns.net.
I need to export a list of users from the database to my boss, but I'm struggling to log in. I tried exporting the data from glodns but it won't work, it times out the whole time.
I desperately need help.
Regards,
ma se
|
|
|
|
|
Hi!
Make sure that you are using TCP/IP as your network protocol. You can configure that using SQL Server's Client Configuration Tool (cliconfg) or by specifying the protocol in your connection string. In SQL 2005 you can specify the protocol when connecting using management studio.
As the server's admins for the correct IP port on which their server is listening. Default is 1433 - maybe they have changed it. It could also be possible that they block external access to their sql servers.
Last but not least make sure that the support sql server authentication. If they restrict access to windows authentication you will not succeed.
Regards,
Rainer.
Rainer Stropek
cubido business solutions gmbh
Email r.stropek@cubido.at
Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx
|
|
|
|
|
can u tell maximum length of
varchar()
nvarchar()
char()
nchar()
text
-
|
|
|
|
|
Assuming SQL Server 2000
varchar(8000)
nvarchar(4000)
char(8000)
nchar(4000)
text = 2Gb
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
hi, i have a program that loads an MS Access in the program. i have a country combobox and a list of countries are there by me from the database. now .. i want that the user can enter a new country if he want to from a combobox. can this happen or can i do something else?? i have a country in another table apart from the contacts table.
help me please
Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.
|
|
|
|
|
i have 2 tables. TABLEA and TABLEB. the structures of both the tables are same. the columns are "branch_name" , "book_ISBN" and "book_name".
branch_name is the primary key in both the tables
the data in the tables are
TABLEA has data as
branch_name book_ISBN book_name
book1 123 vishu
book2 456 vishu
TABLEB has data as
branch_name book_ISBN book_name
book1 123 vishu
book2 567 vishu
as you can see, the 2nd row in both the tables has mismatch in "Book_ISBN" column.
i used a query to find the mismatch in data
SELECT A.branch_name,
CASE WHEN A.branch_name=B.branch_name THEN 'Y' ELSE 'N' END as "Comp_Branch",
A.book_ISBN,
CASE WHEN A.book_ISBN=B.book_ISBN THEN 'Y' ELSE 'N' END as "Comp_book_ISBN",
A.book_name,
CASE WHEN A.book_name=B.book_name THEN 'Y' ELSE 'N' END as "Comp_book_name"
FROM Tablea AS A
LEFT OUTER JOIN Tableb AS B ON A.branch_name = B.branch_name
the result came as
book1 Y 123 Y vishu Y
book2 Y 456 N vishu Y
which shows that "456" of TABLEA is not matching with "567" of TABLEB
now if i want that only the rows having "N" (means mismatch) should be displayed, then how to do this in the above comparison query ?
Please reply ASAP
Vishal
|
|
|
|
|
You've got a number of options.
First of all, you could use the comparisons that are in your case statements as a where clause ...
WHERE<br />
A.branch_name<>B.branch_name<br />
OR A.book_ISBN<>B.book_ISBN<br />
OR A.book_name<>B.book_name
Or you could make your original query into an in-line table and select from it:
Select * FROM<br />
(<br />
SELECT A.branch_name,<br />
CASE WHEN A.branch_name=B.branch_name THEN 'Y' ELSE 'N' END as "Comp_Branch",<br />
A.book_ISBN, <br />
CASE WHEN A.book_ISBN=B.book_ISBN THEN 'Y' ELSE 'N' END as "Comp_book_ISBN",<br />
A.book_name,<br />
CASE WHEN A.book_name=B.book_name THEN 'Y' ELSE 'N' END as "Comp_book_name"<br />
FROM Tablea AS A<br />
LEFT OUTER JOIN Tableb AS B ON A.branch_name = B.branch_name<br />
) Comparison<br />
WHERE Comp_Branch = 'N' or Comp_book_ISBN = 'N' or Comp_book_name = 'N'
If you have multiple uses for this information, (e.g. sometimes you just want to see the comparisons where the ISBNs are different) then you may want to create a view from your original query and then select from the view:
Select * from COMPARISON_VIEW<br />
WHERE Comp_Branch = 'N' or Comp_book_ISBN = 'N' or Comp_book_name = 'N'
Jim Conigliaro
jconigliaro@ieee.org
-- modified at 8:11 Friday 19th May, 2006
|
|
|
|
|
If we use SQL Exception in DA Class
sqltransacrion trans=new ...
try
{
call SP
trans.commit
}
catch(sqlException ex)
{
ex.msg
trans.rollback
}
or
//SqlConnection con = new SqlConnection();
//SqlCommand com = new SqlCommand("procname", con);
//com.CommandType = CommandType.StoredProcedure;
//com.Parameters.Add(@Arg, SqlDbType.Text).Value = @Arg;
//com.ExecuteNonQuery();
create proc procname @Arg
as
insert into values..
if(@@Error<>0)
raiserror(...)
what is the difference in the errors raising in the above two calls
what is the difference between the error throwing
-
|
|
|
|
|
Hello,
In SSMSE I mistakenly ran a create table query on my database in SQL Server 7.0. I then launched SQL Server Enterprise Manager, and noticed that one of my databases are greyed out with (Suspect) labeled beside it.
I checked C:\MSSQL7\Data to see if my database was present, with the right file size.
How can I enable that database?
Thanks in advance.
|
|
|
|
|
(Is it interveal or interval or something else?)
Anyway, I have a database, and I was wandering if it is possible through SQL to substract 2 dates from the cells in the tables.
eg. I have a start time and end time, and I want to know what's the time that between... how much time did it take etc...
My teacher has showed me something with Sum and CDate SQL Methods, but I want a second opinion (and even if some better ideas).
Thanks alot...
NaNg.
|
|
|
|
|
If your database is in Microsoft's SQL Server, then look up DATEDIFF().
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I use access in the meanwhile
|
|
|
|
|
It's DateDiff() there too:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has thesenamed arguments:
Part Description
interval Required.String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek Optional. Aconstant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
Settings
The intervalargument has these settings:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
The firstdayofweek argument has these settings:
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
I am working on a project and to test I would like to make a copy of the SQL database just so I don't mess up the original data. How can I do this?
|
|
|
|
|
Do a complete backup of the database then restore it with a different name.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Create a database with new name and export all data of existing database to new one.
Saqib
|
|
|
|
|
But if you do that you don't get the stored procedures, UDFs, users, etc. In other words, it is not a copy of the database, it is just a copy of the data.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
get backup of existing database, create new database and restore that backup to new created database, this would now contain all things in it.
Saqib
|
|
|
|
|
the best thing what i suggest is , right click on the database for ehich u need a copy .. then generate the sql script then follow the steps like showall->scriptall..then u find it will create a file with all sql statements...after that u do..
1. copy the complete sql statments to query analyzer...and in first line give the command
create database (new database name )
...copy all the sql statements
then execute ( i mean f5) that's it
every problem has a solution...
|
|
|
|
|
I search some detailed information about some aspect of commands that are generated in VS8 for TableAdapters:
 How are calculated @IsNull_<columnname> parameters that are present in the update query?
Does anybody know something about it?
I understand the use of these parameters but I can't find where the parameters are calculated.
Why this question?
I need to change the automatically generated query because in the table that I use there is a calculated column.
The automatic gerated UpdateCommand include the calculated column in the set section of the query so this doesn't work.
So I changed the query of UpdateCommand using the Query Builder.
When I exit from the query Builder the parameters collection all parameters of type @IsNull_<columnname> change, DbType change from Int to AnsiString and ProviderType is cleared (then the Update method doesn't work). Probably this is a bug.
I can define manually the update command, but I would like to know more about the method used by the DataAdapter to initialize the various @IsNull_<columnname> inserted automatically.
Environment: VS8, SQL Server 2000, .Net Framework 2.0
Thanks in advance
Roberto Ferraris
|
|
|
|
|
Searching in MSDN and in the automatic generated code I find some more info, that I attach to the original question, wishing this could help someone else.
The automatic generated code for the parameters of update command is like this:
new SqlParameter(
"@IsNull_DtClassification",
System.Data.SqlDbType.Int,
0,
System.Data.ParameterDirection.Input,
0,
0,
"DtClassification",
System.Data.DataRowVersion.Original,
true,
null,
"",
"",
"");
From SqlParameter.SourceColumnNullMapping documentation I find that the @IsNull_FieldName is used directly by the SqlCommandBuilder (that I think is the same used by the TableAdapter Configuration Wizard.
Here I find "@IsNull_FieldName contains 1 if the source field contains null, and 0 if it does not. This mechanism allows for a performance optimization in SQL Server, and provides for common code that works across multiple providers."
The problem at this point is that if I try to modify the query manually removing the calculated field from the query, the resulting command generate parameters like the following, where there is no source column and data types is wrong:
new System.Data.SqlClient.SqlParameter(
"@IsNull_DtClassification",
System.Data.SqlDbType.VarChar,
1024,
System.Data.ParameterDirection.Input,
0,
0,
"",
System.Data.DataRowVersion.Original,
false, null, "", "", "")
I thing this is a bug of VS
Bye
Roberto Ferraris
|
|
|
|
|
Hi ppl,
I have created an ODBC connection using the ODBC wizzard.
I am using VB.NEt and SQL server
The DSN name : Rch1
Description of Data source : SqlRch
SQL Server Connection "MT1"
This code is not working
Public conn As New OdbcConnection("Provider=sqlodbc;server=Rch1 ;Database=Rch;Trusted_Connection=YES;")
But wont connect.
I get the following error:
An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll
Additional information: System error.
Tnx
-- modified at 11:01 Wednesday 17th May, 2006
|
|
|
|
|
hi
i want to add a column to table using alter statement and i want it to be added after a particular column.
this is done using after clause in oracle but i want to know its eqivalent in sql server
Sanjeev
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
