|
So flame me for asking something which is probably Google-able, but I couldn't find it!
What is the maximum string-length that can be output using the PRINT command in SQL 2005?
E.G
<br />
DECLARE @myString Varchar(max)<br />
<br />
SET @myString = 'Imagine a very looooooooong string here!'<br />
<br />
PRINT @myString
|
|
|
|
|
It was easy to find out - 8000
declare @a varchar(max)
select @a = replicate('a',7999) + 'b'
print @a
--prints 7999 a's and a b
select @a = replicate('x',8000) + 't'
print @a
--prints 8000 x's but no t
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm trying to insert a lot of data (10 million records) to my oracle server,
my original plan works but it's very slow
I’m using OracleDataAdapter with UpdateBatchSize = 100
if I’m setting this value to more than that I’m receiving oracle exception saying:
my software is to big (it compiles it to a huge StoredProcedure and send it to the oracle server)
so my question is, what can be done to speed things up?
Thanks in advance,
Yaron
|
|
|
|
|
Hie
I am new to sql 05, and am buiding a windows app for a hotel. I would like to to retrieve the number of occupants in the various types of rooms on a between a specified date range. eg from the 1st of the month to the end of the month.
For each day in the specified range I would like to retrieve the number occupants in each type of room. I have been able to do this by generating a different select statement for each day that is automatically updated by the program. I however feel that this method is very inefficient and would like to use a single sql statement to pull the data from the database and place it into a datatset.
Does anyone have any idea on hw i cld get around this. thank u.
tw5ted f8
|
|
|
|
|
Your query looks like:
select count(occupants) from tablename<br />
group by [datecol],roomtype<br />
having datecol between '2008-05-01' and '2008-05-31'
If this doesn't helps you then post data as they are saved in table and post output result which u want to have, we will see what we can do.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hello,
I'm trying to connect to an Access database using C# Asp.NET 3.5. Although i'm setting the connection and the connection is opening successfully, when trying to execute a command against a table Visual Studio throws the following error:
OleDbException
"Could not find file 'C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\CCureLiveData.mdb'."
The connection string has not changed and the database is open, it is just the command that is causing this error for some reason!
Does anybody know why VS looks in the directory in the first place? I have not told it to and all the right permissions are set on the folder where it should be looking!
Any help would be very much appreciated!
Thanks Matthew Chamberlain
|
|
|
|
|
For best results in all Windows file handling, always specify the full path to any file. Otherwise you get the 'current directory', which is potentially different for each thread (a thread inherits its 'current directory' from the process, if I recall correctly).
For ASP.NET consider using Server.MapPath to find a file. Use ~/ notation (e.g. "~/CCureLiveData.mdb") to find a file relative to the current application's root - this is easier, and will work even on medium-trust web servers.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi all,
i had one datalist which appear all the pros for all the cities in the links of blue color.
but i need to display the link color in red to those pro who r working for us. i add one column in database 'workus' a bit. if 'workus' is true then it will display in red color.else in blue color.
Protected Sub dtlstPros_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs)
Dim objAnchor As HtmlAnchor
Dim drRow As DataRow
Dim proCurrenctCityID As Int32 = 0
Dim proPrevCityID As Int32 = 0
Dim intRecCount As Int16 = 0
If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
drRow = CType(CType(e.Item.DataItem, DataRowView).Row, DataRow)
proCurrenctCityID = CType(drRow("CityID"), Int32)
If ViewState("PrevCityID") <> proCurrenctCityID Then
ViewState("PrevCityID") = proCurrenctCityID
ViewState("Count") = 0
End If
'If ViewState("RecCount") < DISP_NOOF_PROS Then
''Workaround for hard links
Dim strRedirection As String = ""
Dim strFormatCompanyName As String = ""
Dim intLen As Int16 = 0
***********workus = Convert.ToBoolean(drRow("workus "))
If zoompro = True Then
objAnchor = CType(e.Item.FindControl("ancPro"), HtmlAnchor)
objAnchor.Style.Add("cssClass", "text12red")
'objAnchor.Style.Add("style", "*")
Else
objAnchor = CType(e.Item.FindControl("ancPro"), HtmlAnchor)
End If***************
objAnchor = CType(e.Item.FindControl("ancPro"), HtmlAnchor)
strRedirection = String.Format("~/xyz/Details.aspx?ID= {0}&CName={1}&CName={2}&ID={3}&Page={4}", drRow(Cas.GUID).ToString, drRow(cas.CNAME).ToString, Request.QueryString("Name"), Request.QueryString("ID"), "City")
objAnchor.HRef = strRedirection
intLen = drRow("CName").ToString.Length
If intLen > PNAME_LENGTH Then
strFormatCompanyName = Mid(drRow("CName").ToString, 1, PNAME_LENGTH)
objAnchor.InnerText = strFormatCompanyName + "..."
Else
objAnchor.InnerText = drRow("CName").ToString
End If
''End Workaround for hard links
'End If
ViewState("Count") += 1
End If
End Sub
can any one help me how to change the color of 'anctag' to red if workus=true
Thanks in advance
Thanks
Subbu.
|
|
|
|
|
Hi,
I dont know if this is possible, but I'll ask anyway.
I need to be able to send an SQL script as a text file, or something similar. The script will be modifying a few columns, and inserting a few new rows. Nothing too complictated....
However...
I am using SqlCE 3.5, so the commandset is slightly reduced, and, worst of all, one of the fields is an Image (i.e. Binary). Its not huge, most records are about 1k, however, it has thrown a bit of a spanner in the works for my simple script idea.
So, I guess I am asking, is there a reasonably simple way to script a database which contains binary data?
Thanks,
Jason
|
|
|
|
|
Replying to my own question... I must be sad  )
Anyway, it was easy... after a lot of googling:
insert into [Table] (Field) Values (0x1232132132)
I am a bit ashamed that I missed that. Posted here in case someone else needed it.
|
|
|
|
|
I need to locate all the SQL servers and their databases on the all the computers in the domain of my company. How to do that. Plz help.
Thanks
|
|
|
|
|
i have a number of parameters based on sqlcommand which input data through sql stored procedure
if the user omit one of the parameter fields an Exception raise
i want to make parameters accept nulls without validating user input
note: i have tried isNullable and failed
please help
regards
|
|
|
|
|
The stored procedure definition should look like this:
<br />
CREATE PROCEDURE myProc<br />
@param1 INT = NULL<br />
@param2 VARCHAR(50) = NULL<br />
AS<br />
SELECT * FROM [sometable] WHERE col1= COALESCE(@param1, col1) AND col2 = COALESCE(@param2, col2)<br />
GO<br />
You will still have to call Parameters.Add() for each parameter defined by the stored procedure, but now you will be able to pass null values to the procedure w/o getting any exceptions. But you'll need to be aware that you will need to allow for null values to be passed to the query and the effects that will cause. So you'll need to use COALESCE or some other method to allow for nulls in the query.
|
|
|
|
|
Hi,
I am a little lost in this situation.
I am required to reuse SQL queries , also I am required NOT to ceate stored procesdures as they reside on SQL server, but there should be some form on the application side to store my SQL queries and call them when required.
What is the best practice to achieve this,
I am thinking of storing on .resx files
Please let me know the best Solution.
Thanks,
Happy Programming!
Regards,
ctrlnick !
|
|
|
|
|
Where you store the query text is probably immaterial, because wither you store the query in .resx or hard-code the query into the application you'll still need to recompile when you change the query. However, you should define an interface or at least a data layer class which defines the queries and their parameters and hides the implementation of how you call and execute your queries from your application.
If you are working with DataSet objects then your interface will define the return values as DataSet/DataTable objects. Otherwise, your interface will define the return values as your custom objects.
If you are working with a large number of queries you will need to define multiple interfaces which break up your queries into logical groups which match your object model.
Here's a simple implementation w/o using an interface:
implementation:
<br />
public class DataLayer {<br />
public DataSet GetSomeData(int param1, string param2){<br />
DataSet myData = new DataSet();<br />
<br />
using(DbConnection conn = GetConnectionFactoryMethod()){<br />
using(DbCommand cmd = new DbCommand(conn, "SELECT .... FROM .... WHERE col1 = @param1 AND col2 = @param2")){<br />
cmd.Parameters.Add("@param1", .....);<br />
cmd.Parameters.Add("@param2", .....);<br />
<br />
cmd.Parameters["@param1"].Value = param1;<br />
cmd.Parameters["@param2"].Value = param2;<br />
<br />
DbDataAdapter da = new DbDataAdapter(cmd);<br />
da.Fill(myData);<br />
}<br />
}<br />
<br />
return myData;<br />
}<br />
<br />
public DataSet GetSomeDifferentData(string param1, bool param2){<br />
}<br />
<br />
private DbConnection GetConnectionFactoryMethod(){<br />
return new SqlConnection("... connection string ...");<br />
}<br />
}<br />
usage:
<br />
DataLayer myDataLayer = new DataLayer();<br />
<br />
DataSet myData = myDataLayer.GetSomeData(5, "my string");<br />
DataSet myData2 = myDataLayer.GetSomeDifferentData("some value", true);<br />
<br />
By doing this you can store your query text however you like (I say just hard-code the query text into the data layer class). The advantage of doing it this way is that your queries are de-coupled from your application. You can change your data provider or switch to stored procedures or whatever. As long as you return the same dataset (or object) it doesn't matter how you do it.
|
|
|
|
|
Dear All,
I want to have a survey on locking mechanisms in SQL Server. I have studied different locking modes including shared,exclusive, intent shared ,...
Besides I have studied different locking modes, table hints and different Isolation levels in SQL server. But I still don't know what should I do if I want to take for example an exclusive lock at a row-level. There are few examples on SQL Server help. Would you please introduce me a reference with examples of these kinds.
Thanks in advance
|
|
|
|
|
MozhdehQeraati wrote: if I want to take for example an exclusive lock at a row-level.
You do not normally need to do this sort of thing. If you do your insert/update/delete in a consistent manner and use transactions where necessary you can leave the rest to SQL Server.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I want to control it myself. I have many clients that do read and write in on a DB server by long time transactions. But I don't know what to do.
|
|
|
|
|
I would firstly look at why you have long transactions - there is probably a design flaw somewhere. SQL Server is designed to work with many clients performing simultaneous updates without the need for the developer to attempt to control locking under anything but very exceptional circumstances.
I have been asked about this in the past and it has usually been because the developer either didn't really understand how SQL Server locking works, or they felt they could do a better job than the guys who wrote the code at Microsoft.
If you really want to do it yourself then may I wish you luck, and your end users a lot of patience 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i want to update an image field of a record in SQL but i can not .
i must first delete the record and then insert it but i want to update the record.
please help
|
|
|
|
|
fix your code.
If you want more help, try posting some details of the code you're using, so we can look at it and try to help.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You can learn the with content from the new SQL Server 2008 Jumpstart event. The site contains presentations, recordings, hands-on labs and demonstrations for the SQL Server 2008 JumpStart technical training event.
There are five tracks:
The online material includes presentations, videos, hands on labs and manuals, recorded webcasts, and the SQL Server 2008 Virtual PC used for the labs.
You'll be asked to register if you want to access the training material from the event. All the resources you need for testing your application on SQL Server can be downloaded from the Application Compatibility & Upgrade Track page.
If you are conducting Application Compatibility testing, remember to sign-in with your Windows LiveID and report your results through the SQL Server App Compat Testing Results Survey. If you encountered bugs with any of the product features, submit feedback to the SQL Server Engineering team so we can fix it. For online support go to the App Compat and Upgrade forum.
|
|
|
|
|
Hi all,
I am getting an 'incorrect syntax near StoredProcName' error on debugging the beginings of my web app.
I know the sql syntax is correct as I have tested it in management studio express(2005) annd it returns exactly what I expect. The error is only occuring when calling from my page.
Here's my page code followed by a very simplified stored procedure(I know my complex one will return the correct result and this has the same error).
Page code:
SqlConnection conn = new SqlConnection(connString);
string sPName = "getProjectProModules2";
SqlCommand cmd = new SqlCommand(sPName, conn);
SqlParameter par1 = new SqlParameter("@modCount", SqlDbType.Int);
par1.Direction = ParameterDirection.Output;
par1.Size = 1;
cmd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter("@singleModule", SqlDbType.VarChar);
par2.Direction = ParameterDirection.Output;
par2.Size = 20;
cmd.Parameters.Add(par2);
conn.Open();
SqlDataReader recaffected;
try
{
recaffected = cmd.ExecuteReader();
if (recaffected.HasRows)
{
int i = 0;
while (recaffected.Read())
{
if (i == 0)
{
string countStr = cmd.Parameters["@modCount"].Value.ToString();
count = Convert.ToInt16(countStr);
}
moduleList[i] = cmd.Parameters["@singleModule"].Value.ToString();
i++;
}
recaffected.Close();
recaffected.Dispose();
return moduleList;
}
else
{
moduleList[0] = "No Modules";
return moduleList;
}
}
catch (Exception ex)
{
moduleList[0] = ex.Message.ToString();
moduleList[1] = ex.Data.ToString();
moduleList[2] = ex.Source.ToString();
return moduleList;
}
finally
{
conn.Close();
conn.Dispose();
}
SP Code:
ALTER PROCEDURE dbo.getProjectProModules2
@modCount int OUTPUT, @singleModule varchar(50) OUTPUT
AS
select '1'
RETURN
If I don't put the parameter size in my page code, it errors saying invalid parameter size.
Really not sure whats going wrong, I've looked up the code all over the place and from what i can see I'm doing it right. Anyone have any idea what the problem is?
Thanks in advance.
Mark
OK, fixed that error for those interested, I was not setting the command type to be StoredProcedure. However, I only seem to be able to return null as my parameter values. Still if there's any suggestions I would appreciate it.
modified on Tuesday, May 27, 2008 5:41 PM
|
|
|
|
|
To return parameters, you create local variables and pass them to the parameters, from memory.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
OK, so I have created 2 local variables, 1 int and 1 string, these I am passing to the paramater as the value,
I.E.
string singleModuleStr = "";
int countInt = 0;
--it doesn't like me not assigning them....
.....
par1.value = countInt;
.....
par2.value = singleModuleStr;
do i then assign the par2.value etc to these variables at recAffected.Read()?
Don't want to sound dumb but I'm used to just returning a select list and I want to be able to do it each possible way to broaden my knowledge.
Thanks for the help so far.
mark
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
