Hi all, I am new to transact-sql and hoped that someone here might be able to help. I have a db with a field called "part" ... part contains text in the format:
xxxxx-xx-xxxxx
(the number of x's before or after each hyphen vary) such that I could have xxx-xxxxx-xxx as the part.
Someone wrote this transact-sql to take my current "part" field and move it to three different part fields ... a,b,c
If xxxxx-xx-xxxxx was the part in the original db, then a would contain xxxxx .. b contains xx ... c contains xxxxx
Make sense? Here is the code. It doesn't work and I can't seem to figure out why! It looks like it is just trying to find the hyphens and cut out the text from that... but it fails with an error that says "invalid length parameter passed to the substring function". Note, if I take off the "-1" from each line below... then the function works but produces the wrong data.
char(45) = "-" (hyphen)
UPDATE [dbo].[parts] SET
a = SUBSTRING(part,1,CHARINDEX(CHAR(45),part)-1),
b = SUBSTRING(part,CHARINDEX(CHAR(45),part)+1,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))-1),
c = SUBSTRING(part,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))+CHARINDEX(CHAR(45),part)+1,LEN(part))
GO
Does anyone know what I can do to fix this or is it much more difficult to do than what it seems?
I am trying to get the id of last entered record and I used select ID command but it doesn't give me the ID of last record enteredProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click Dim name, address, nice As String Dim sConnString, sSQL, sID As String Dim u As MembershipUsername = Me.name.Textaddress = Me.address.Text
u = Membership.GetUser(User.Identity.Name) nice = u.ToString()sSQL = "INSERT into test ([Address],[Name], [username]) values ('" & _ address & "', '" & _name & "', '" & _ nice & "'); SELECT ID FROM test WHERE (ID = SCOPE_IDENTITY())" MsgBox(sID) MsgBox(sSQL) sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString()Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection) MyConnection.Open() MyCmd.ExecuteNonQuery() MyConnection.Close() End UsingResponse.Redirect("transferred.aspx?value=2") End Sub End Class
Can someone please help me fix this. The if statement always runs. I only want it to run if the statement is true and there is a result coming back. If the productID does not have any magazines linking to it, I don't want the If statement to run. Thanks in advance.
ASP.NET code:
If Not Magazine.GetMagazinesForProduct(ProductID) Is Nothing Then blanklabel.Text = categoryDetails.Spacer blanklabel.Visible = True blanklabel2.Text = categoryDetails.Spacer blanklabel2.Visible = True magazinerecommendedlabel.Text = "Recommended/Featured in the following magazine(s):" magazinerecommendedlabel.Visible = True End If -------------------------
magazine class:
Public Function GetMagazinesForProduct(ByVal productID As String) As SqlDataReader Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand("GetMagazinesForProduct", connection) command.CommandType = CommandType.StoredProcedure
Return command.ExecuteReader(CommandBehavior.CloseConnection) End Function
-----------------------
Stored Procedure:
CREATE PROCEDURE GetMagazinesForProduct (@ProductID varchar) AS SELECT Magazine.[Name], Magazine.[Issue], Magazine.SmallImagePath FROM Magazine INNER JOIN MagazineProduct ON Magazine.MagazineID = MagazineProduct.MagazineID WHERE MagazineProduct.ProductID = @ProductID RETURN GO
hi there, i have been wrestling with this for quite a while, as in my other post http://forums.asp.net/t/1194975.aspx, what someone advised me was to put in try catch blocks ot see whats going on, problem is i have never really done it whit this kinda thing before, and i was wondering if someone could point me in the right direction.
For example where would i put the try catch block in here, to show me if its not working public int getLocationID(int ProductID, int StockLoc) { // Gets the LocationID (Shelf ID?) for the stock column and product id // passed // The SQL will look Something like: string strSQL; strSQL = "SELECT " + " location" + StockLoc + " " + "FROM " + " tbl_stock_part_multi_location " + "WHERE " + " stock_id = " + ProductID;string sConnectionString = "Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxxx"; SqlConnection objConnGetLocationID = new SqlConnection(sConnectionString);SqlCommand sqlCmdGetLocationID = new SqlCommand(strSQL, objConnGetLocationID); objConnGetLocationID.Open();int intLocation = Convert.ToInt32(sqlCmdGetLocationID.ExecuteScalar()); return intLocation; }
All: I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update. When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#. When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening? Here is the basic shell of my SP: CREATE PROCEDURE [dbo].[spHeader_InsertUpdate] @FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime AS Declare @rtncode int IF NOT EXISTS(select * from HeaderTable where FormID=@FID) Begin begin transaction --Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE Begin begin transaction --Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID; --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
how recursive CTE works...Synthesise table with non-recursive CTE
;WITH Employee (ID, Name, MgrID) AS ( SELECT 1, 'Keith', NULL UNION ALL SELECT 2, 'Josh', 1 UNION ALL SELECT 3, 'Robin', 1 UNION ALL SELECT 4, 'Raja', 2 UNION ALL
[code]....
the first part query will return one row IDNameMgrIDnLevelFamily 1KeithNULL11
then this result set using after union all query .how the level + 1 condition is working ? and how its return values .
Is there any transact code for sql server that I can type out to view all of the relationship Definments of the current database or even an individual table.
In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:
Merge TST.dbo.LockCombination AS LKC1 USING (select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId from [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?
I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays. This is the syntax I have to start with, but it is presenting with an error:
Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.
What should I alter?
CREATE TABLE dbo.CreateCustomCalendar ( wk_Date INT IDENTITY NOT NULL, FullDate DATETIME NOT NULL, WeekDayName VARCHAR(9) NOT NULL, IsWorkday varchar(20) NOT NULL )
Hi, Recently I have been trying to write a script in order to redo this entire database, it's actually only about rectifying column and table names as well as a few erroneous relations, etc. The idea is that the actual data is okay it's just the organization that is completely messed up (spaces in column and table names, etc.)
Anyway my problem is this, a part of this script is about mass-renaming columns and tables the code is as follows in the Query Analyzer:
When I barely analyze the code (Ctrl+F5) it gives me the following error: Serveur : Msg 170, Niveau 15, État 1, Ligne 2 Ligne 2 : syntaxe incorrecte vers 'sp_rename'.
Which would roughly translate into: Server : Msg 170, Level 15, State 1, Line 2 Line 2 : Incorrect syntax near 'sp_rename'
When I execute it, it gives the same error. Why does it do that when i give it the two lines while if i enter the lines one by one it works just fine. How is a SCRIPT supposed to work if I have to "baby-feed" every statement separately.
PS: i am working on SQL Server 2000 (in Query Analyzer menu ?/About .. it says SQL Version 8.00.194
Have there been any updates to sql 2005 transact codes, because I tried to use some transact code that I used in sql 8.0 and in sql 2005 on the same database and it did not work.
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'. USE [merck_acronyms] GO
Basically, I am asking if it is possible to use SSMS (Query Analyzer window) with Informix. I am thinking linked server. If I have the ODBC driver installed on my workstation (win 7 pro VM workstation -- SSMS on same VM machine) is this doable? Do I just do the standard linked server routine or is there a special way (if it is even doable).
altimebest1 writes "Please show sql codes to creating a table showing Value and quantity under a heading, the month the data was collected or the value and quantity in the 1st, 2nd, 3rd and 4th quarter values under a heading Year. My source table is: comCode char 7 monthCode char 2 year char 4 countryCode char 3 quantity int value money insurance money freight money i also have a lookup table for:
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------ Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19 Select statements included within a function cannot return data to a client. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20 Invalid column name 'WorkDay'. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22 Invalid column name 'Date'.
------ my function code ---------------------------- CREATE FUNCTION [dbo].[FGetWorkDate]( @WorkDays VARCHAR(5), @DateFromValue AS DateTime ) RETURNS DATETIME
below is some code I use to identify where a patient has attended the ED department whilst also admitted as an Inpatient. This report works fine. However while most of the results are recording issues to be corrected some of them are real and as so can be excluded from the report.
Is there a way I can build in an exclusion table which would include:
SELECT IP_ADMISSION.HeyNo AS HEYNo ,IP_ADMISSION.NHSNo2 AS NHSNo ,IP_ADMISSION.Forename AS Forename ,IP_ADMISSION.Surname AS Surname ,IP_ADMISSION.SourceAdmNatCode AS SourceAdm
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
how to hard code mention date range in my SP to get expected results in my query 01/01/2012 to 12/31/2012
DECLARE @ACCOUNT AS INT DECLARE @POSTING_DATE AS DATETIME DECLARE @FIRST_POSTING_DATE AS DATETIME SET @POSTING_DATE = {?POSTING_DATE} SET @ACCOUNT = {?ACCOUNT}
I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.
I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.
whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.
In the 70-461 objectives it says: Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications...I think I understand what this means in general. They want us to be able to create a view that will still be able to call the original data even if the table is modified. In other words, the view table shouldn't easily be broken. ie, type a code that does NOT ensure non regression, then change the code so that it does ensure non regression.
I'm looking for a way to refer to a package variable within any Transact-SQL code included in either an Execute SQL or Execute T-SQL task. If this can be done, I need to know the technique to use - whether it's something similar to a parameter placeholder question mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements within the Execute SQL task, so I don't think the Execute T-SQL task is even necessary for this purpose.
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here: Function Main() on error resume next dim cn, i, rs, sSQL Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>" set rs = CreateObject("ADODB.Recordset") set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount sSQL = rs.Fields(0).value cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution rs.MoveNext Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script: - The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Table 1: AddressBook Fields --> User Name, Address, CountryCode
Table 2: Country Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Hello, I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls. I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems ( @eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar, @eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar, @outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar, @q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar, @levent nvarchar, @eventdate nvarchar, @eventtime nvarchar ) AS UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile, q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn, outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3, qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName, CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime WHERE (ePRN = @ePRN) and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text); cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text); ((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text); cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text); cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text); cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text); So is there any way to do it better than this way ?? Thank you
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
My error is that 'Name tr is not declared' tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'. What is the correct way? Try conn.Open() Dim tr As SqlTransaction tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.ExecuteNonQuery() cmdInsert2.Transaction = tr cmdInsert2.ExecuteNonQuery() cmdInsert3.Transaction = tr cmdInsert3.ExecuteNonQuery() tr.Commit() Catch objException As SqlException tr.Rollback() Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next Finally conn.Close() End Try
