|
I just tried a small select, and mistakenly left the quotes off a varchar field. Instead of complaining that the column is varchar not numeric, SQL attempts to convert each value of the varchar column to an int, failing when it encounters the first non-numeric value in the column. Why doesn't it complain earlier, and why does it choose the least efficient conversion, if any converison at all?
|
|
|
|
|
Brady Kelly wrote: Why doesn't it complain earlier, and why does it choose the least efficient conversion, if any converison at all?
To make sure you won't forget next time...
|
|
|
|
|
Hello,
I have a query that select data from 2 tables which I want to print out in crystal report.
My query
<br />
ALTER PROCEDURE [dbo].[printJobSheet]<br />
@incidentID int<br />
<br />
AS<br />
BEGIN<br />
-- SET NOCOUNT ON added to prevent extra result sets from<br />
-- interfering with SELECT statements.<br />
SET NOCOUNT ON;<br />
<br />
-- Insert statements for procedure here<br />
SELECT incident.incidentID, incident.company, incident.subject, incident.contact, incident.phoneNo, incident.email, incidentTask.TaskID, incidentTask.Details<br />
FROM incident INNER JOIN incidentTask on incident.incidentID = incidentTask.incidentID<br />
WHERE incident.incidentID = @incidentID<br />
END<br />
The repeating field are in the incidentTask table (TaskID, Details) These will be in the detailed section of the report. The other fields from the incident table will be in the header.
The way I am fill my dataset which is a typed dataset is as follows:
<br />
Try<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.CommandText = "printJobSheet"<br />
<br />
Dim pIncidentID As New SqlParameter()<br />
pIncidentID.ParameterName = "@IncidentID"<br />
pIncidentID.DbType = DbType.Int16<br />
pIncidentID.Direction = ParameterDirection.Input<br />
pIncidentID.Value = IDNumber<br />
cmd.Parameters.Add(pIncidentID)<br />
<br />
DS_JobSheet2 = New DataSet<br />
<br />
cnn.Open()<br />
cmd.Connection = cnn<br />
<br />
da.SelectCommand = cmd<br />
da.Fill(DS_JobSheet2)<br />
<br />
dt = DS_JobSheet2.Tables(0)<br />
<br />
report.Load(Application.StartupPath & "/rptJobSheet2.rpt")<br />
report.SetDataSource(dt)<br />
<br />
Me.CrystalReportViewer1.ReportSource = report<br />
<br />
Catch ex As Exception<br />
MessageBox.Show(ex.Message)<br />
End Try<br />
If I do a SELECT * FROM Incident
or
SELECT * FROM IncidentTask
It works, but not when I join the tables together. I have created the xsd typed datasets and have the in my report.
Can any confirm my query, is it correct.
Thanks in advance,
Steve
|
|
|
|
|
What are the purpose of SQL Server Jobs, please can nay one let me know abt it in detail.thankx in advance.
|
|
|
|
|
SQL Server 2005 Books Online
Implementing Jobs [^]
A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command-line applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks. Jobs can run repetitive tasks or those that can be scheduled, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server administration.
You can run a job manually, or you can configure it to run according to a schedule or in response to alerts.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
hello,
1. im trying to use analysis service
2. i hit the server connect button
3. i get error message : "connection cannot be made to redirector", check sql server browser
4. i checked, and it was running
5. what to do? 
thx
|
|
|
|
|
Hi all,
I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this..
Dim dataRS As New ADODB.Recordset
Dim cnnSQL As New ADODB.Connection
Set cnnSQL = New ADODB.Connection
cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;"
cnnSQL.Properties("Integrated Security").Value = "SSPI"
cnnSQL.Open
dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic
The code fails though, on the last line with message
"Invalid object name 'dbo_Table_1'"
(yes, I verified that the table actually does exist in the DB)
I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions?
Thanks in advance,
|
|
|
|
|
psasidisrcum wrote: "SELECT dbo_Table_1.* FROM dbo_Table_1
I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1? You also have a period after the first dbp_Table_1 which may be causing fits.
Jerry
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
Jerry Hammond wrote: I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1?
He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.
Jerry Hammond wrote: You also have a period after the first dbp_Table_1 which may be causing fits.
The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is:
[server].[database].[owner].[table].[column]
|
|
|
|
|
Colin Angus Mackay wrote: He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.
Cool. That is good to know. He does say, "yes, I verified that the table actually does exist in the DB)".
Colin Angus Mackay wrote: The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is:
[server].[database].[owner].[table].[column]
Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
Jerry Hammond wrote: Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?
No, it goes the other way around. The column is the mandatory bit, so it will parse as if the colum is the last thing and work back from there.
Valid sequences, when expecting a column name, are therefore:
[server].[database].[owner].[table].[column]
[database].[owner].[table].[column]
[owner].[table].[column]
[table].[column]
[column]
Valid sequences, when expecting a table name, are:
[server].[database].[owner].[table]
[database].[owner].[table]
[owner].[table]
[table]
(Note the owner name, in both cases, can be omitted when it is the dbo, so you'd get the two dots with nothing inbetween. e.g. mydatabase..mytable)
|
|
|
|
|
Cool beans, Colin. Thanks for clearing that up for me.
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
psasidisrcum wrote: Dim cnnSQL As New ADODB.Connection
Set cnnSQL = New ADODB.Connection
Are you sure the root cause isn’t the double initialize of cnnSQL?
Dim cnnSQL As ADODB.Connection
Set cnnSQL = New ADODB.Connection
psasidisrcum wrote: dataRS.Open "SELECT * FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
S Douglas wrote: Are you sure the root cause isn’t the double initialize of cnnSQL?
While the double initialisation is redundant it is unlikely to be the cause of the problem because the second connection is constructed over the first before anything is done with the first connection.
|
|
|
|
|
Colin Angus Mackay wrote: it is unlikely to be the cause of the problem because the second connection is constructed over the first before anything is done with the first connection.
CP choked on my post and I was way to lazy (well tired) to retype it all out again. I have had issues with simple errors of that nature. Nothing big just flaky behavior by the object.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
psasidisrcum wrote: The code fails though, on the last line with message
"Invalid object name 'dbo_Table_1'"
Are you sure you really have a table name called dbo_Table_1? Are you sure it isn't dbo.Table_1? (i.e. there is a dot between dbo and Table_1 rather than an underscore)
|
|
|
|
|
I have a trouble with using DiffGrams.
All I need is to update MSSQL 2000 table using DiffGram.
I wrote code but it crashes Searching MANY documents didn't gave me an answer  .
Please, could anyone help me? 
<br />
SqlXmlCommand cmd = new SqlXmlCommand(connectionStr + ";Provider=SQLOLEDB");<br />
cmd.CommandType = SqlXmlCommandType.DiffGram;<br />
cmd.Namespaces = "xmlns:x='http://tempuri.org/mydata.xsd'";<br />
cmd.SchemaPath = Application.StartupPath + @"\mydata.xsd";<br />
cmd.CommandText = "x:SampleTable"; <br />
SqlXmlAdapter ad = new SqlXmlAdapter(cmd);<br />
ad.Update(dataSet);<br />
P.S. Why Microsoft help is so small?
|
|
|
|
|
Hey people, does anybody know how to use DiffGrams?
|
|
|
|
|
I've been looking around for approach recommendations on how to best update the code for an SQL Server 2005 user defined type written in C#. You can't deploy the type if it is 'in use' - for example, if a field in the database is declared to be of the type of the UDT.
Of course, I usually write perfect code that forsees all future possibilities.  ... but on the off chance I might need to update the managed code into a database with real production stuff in it - I'm looking for the best approach to deploy updates to the UDT.
So far, the best I've figured out is that one tests the heck out of the string to type and type to string conversions, and when you want to deploy the update, you change all the UDT columns into strings (which converts the data in place, and frees the assembly from use) deploy the update, and then change all the columns back to the UDT.
Anyone have anything less brute forceish to recommend?
Mike
---------------------
www.opusedge.com
|
|
|
|
|
Hi all,
What are the Features encorparated in SQL Server2005 ?
Aditya Chitti
|
|
|
|
|
|
Go here[^] type http://msdn2.microsoft.com/en-us/sql/default.aspx into the search box. You should be able to quickly and with little trouble discover all the features of SQL Server 2005.
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
|
|
|
|
|
I suggest that you visit the MSDN site and search
You will find all features listed.
if (ToErr == Human.Nature)
{
Forgive = Divine;
}
|
|
|
|
|
Hi all,
How to install Recruitment Database in SQL Server 2000.
Please help me out.
Aditya Chitti
|
|
|
|
|
Since we don't know anything about this "Recruitment Database" we can't really help you. Perhaps you should speak to a qualified representative of the company that supplied it.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
