I have a field that contains data such as 'ACB00/02'. How would I write into a function that gathers a load of data together for me a peice of code that will grab only the first 2 chars of the afore mentioned value, So the result would be in the new created colum in a temp table 'AC'.
This is to occur over 7,000 records but they are not all AC, I just want the first two from every line.
The tblSAVDetails has raw data from all of our servers nationwide The tblSAVComputers have a list of servers in our site The SAVResults will store the results of the query
I want compare the list of servers in tblSAVComputers with the list contained in tblSAVDetails and IF it finds the server to then retrieve the details. However if it doesn't find the server I want to flag it (with a NULL or NA or somthing). After all that I want it to insert the results into tblSAVResults.
The SQL command I am using is: --------- SELECT * FROM tblSAVComputers LEFT OUTER JOIN tblSAVDetails ON tblSAVComputers.SAVComputers = tblSAVDetails.SERVERS ---------
I have tried so many ways, but I just cant get it to work. I know how to insert data into a DB, but I just cant get this command to work in vbScript (it works fine if I run it directly on the SQL server)
Any help would be brilliant! If you need more info, please let me know.
In SQL Server is there a quick way to automatically insert empty value instead of NULL? I have a long Insert-SQL command with LEFT JOIN and don't want to use ISNULL for every field.
Researching on this in the meantime myself and I believe there is no way.
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..
I have a table
CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) ) AS BEGIN SELECT KeyName FROM TEST001 WHERE KeyName = @KeyName END KeyName = @KeyName
Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..
IS there any way to find out all such usage on the ALL Procedures in the Database ?
For those of you who would like to reference my exact issue, I'm dealing with the RSExecution SSIS package at the "Update Parameters" data flow task, at the Script Component.
The script tries to split parameter data into name and value. Unfortunately, I have several reports that are passing parameters that are very large. One example has over 65,000 characters all in the normal "¶mname=value&parm2=value..." format.
The code in the script works fine until it gets to one of these very large parameter sets. I have figured out what is causing the issue. Here's some code:
Dim paramBlob as Byte() paramBlob = Row.BlobColumn.GetBlobData(0, Row.BlobColumn.Length)
The second parameter of the .GetBlobData function takes an INTEGER as its count! Therefore, no matter what kind of datatype I pass to the string that the script will later split, it will be limited to 32767 characters.
THIS IS A PROBLEM!!!
Does anyone know a workaround for this issue? I need all of the parameter data to be reported, and I would hate to have to skip over rows like this. Also, if I'm missing something, please fill me in!
I am trying to narrow down this problem. Basically, I added 3 columns to my article table. It holds the article id, article text, author and so on. I tested my program before adding the additional field to the program. The program works fine and I can add an article, and edit the same article even though it skips over the 3 new fields in the database. It just puts nulls into those columns.So, now I have added one of the column names I added in the database to the code. I changed my businesslogic article.vb code and the addarticle.aspx, as well as the New article area in the addartivle.aspx.vb page. The form now has an additional textbox field for the ShortDesc which is a short description of the article. This is the problem now: The command parameters.length is 9 and there are 10 parameter values. Right in the middle of the 10 values is the #4 value which I inserted into the code. It says Nothing when I hover my mouse over the code after my program throws the exception in 17 below. Why is command parameters.length set to 9 instead of 10? Why isn't it reading the information for value 4 like all the other values and placing it's value there and calculating 10 instead of 9? Where are these set in the program? Sounds to me like they are hard coded in someplace and I need to change them to match everything else. 1 ' This method assigns an array of values to an array of SqlParameters.2 ' Parameters:3 ' -commandParameters - array of SqlParameters to be assigned values4 ' -array of objects holding the values to be assigned5 Private Overloads Shared Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)6 7 Dim i As Integer8 Dim j As Integer9 10 If (commandParameters Is Nothing) AndAlso (parameterValues Is Nothing) Then11 ' Do nothing if we get no data12 Return13 End If14 15 ' We must have the same number of values as we pave parameters to put them in16 If commandParameters.Length <> parameterValues.Length Then17 Throw New ArgumentException("Parameter count does not match Parameter Value count.") 18 End If19 20 ' Value array21 j = commandParameters.Length - 122 For i = 0 To j23 ' If the current array value derives from IDbDataParameter, then assign its Value property24 If TypeOf parameterValues(i) Is IDbDataParameter Then25 Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)26 If (paramInstance.Value Is Nothing) Then27 commandParameters(i).Value = DBNull.Value28 Else29 commandParameters(i).Value = paramInstance.Value30 End If31 ElseIf (parameterValues(i) Is Nothing) Then32 commandParameters(i).Value = DBNull.Value33 Else34 commandParameters(i).Value = parameterValues(i)35 End If36 Next37 End Sub ' AssignParameterValues38 39 40 41
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
SELECT RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) FROM [Place] WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
i am having names like AB_12 I want to get all rows with left part similar , AB im that case
SELECT id, name FROM Users WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN ( SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users GROUP BY LEFT(name, CHARINDEX('_', name) - 1) HAVING (COUNT(*) > 1) )
does not work
is there any way to use a variable ?
declare @nm nvarchar set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
We ran into a problem loading access where tables where if we did notselect the property allow zero length we got an error message whenloading data with some empty values.We are now ramping up SQL server and the question came up will SQLserver have the same problem with empty data values.Is there an SQL server equivalent to allow zero length?
how can I can update a table :: I must get all the left part of a columns part1_part2I dont know how many characterssomething like :UPDATE Users SET Users.Info = left(Users.name, _ ??? or split('_'))in that case Users.name = part1i must update only the one with a '_' and do nothing for the othersthank you
I am trying to pad a field from the left with zeros that is defined in a table as "[char] (9)". The field can have from 1 to 9 chars populated. In this instance the value in the field is "133000", which I am trying to pad so that it will be "000133000". I have made several failed attempts to do this, the latest of which was this:
select right(replicate('0',9)+ convert(varchar(9),a.GS06GRP_CTL_NBR),9) fromTable1 a
Hi all - am in a bit of a quandry over this one!My application is all up and running - it's an despact & accounts app - I've missed one thing though - the ability to deal with half pennies (or half cents for those of you over the pond ;)) All of my tables are set up as decimal (19,2) along with all the params in my stored procs (a lot!). Is there an easy way to change all of these in 1 swoop? Thanks in advance, Stephen.
I'm trying to load a flat file (text) into a table with 83 columns in it. The record length on the flat file is 251 characters long. For some reason when running the DTS import utility, DTS can't read the past the 142 character on the flat file, it spills on to the next line. The red bar is set on the 143 character when i use the fix field options in DTS import. Can the red bar be moved out further? Or is there a max field length using DTS?
Hi Guys I am having a problem with the execution of a query.This is how it is working,if i include all the clounms(26 columns) for the output of 34,000 records it is taking around 4 Min. but if i exclude one column or reduce the size of the column i.e from text to varchar(100) it is taking 23 Sec.
If i am trying to change the size of the column from varchar(100) to varchar(150) it is taking the same time i.e, 4 Min.
Looking at this i am wondering if there are any system setting where we can increase the size of the rows for the out put or are there any other things i have to look at to increase the speed of the Query.
Did anyone come across this problem. Any help is appriciated. Thanks Chak.
Hi, I am sending the output of a query to a file and unfortunately every row is containing only a certain number of characters. My query has rows having length of 1500 characters for each row. When I get the query and send it to a file I am seeing only the first 250 characters for each row instead of 1500 characters.
I will be grateful if anyone can help me suggesting a solution for this problem.