I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number.
DECLARE @num INT
SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity
FROM BarCodes INNER JOIN
Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN
getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity
WHERE (ArticlesTrafic.DocumentID = @Param2)
The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?
here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL)
but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.
__________________________________________________ _ CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS BEGIN DECLARE @v_object_key VARCHAR(80) DECLARE @v_object_name VARCHAR(40) DECLARE @v_object_verb VARCHAR(40) DECLARE @v_datetime DATETIME
SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!! SET @v_object_name = 'FochiniTable' SET @v_object_verb = 'Create' SET @v_datetime = GETDATE()
IF ( USER <> 'webuser' ) INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment) VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')
END ________________________________________________
i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id
and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
We have one main package from which 7 other child packages are called. We are using ParentPackage variables to assign values for variables and database connections.
While the values from ParentPackage variable get assigned to some of the packages properly, to others it doesn€™t assign the value.
For example: Except for one of the packages the database connection string gets assigned properly to all other packages.
Similarly, in another package one of the variables doesn€™t get assigned. In other packages it is assigned properly.
We have checked all the other property values and they are exactly the same.
We cannot make any head or tail of this erratic behavior.
I want to know the answer of this question please as i have to submit my assignment as soon as possible. The assignmet question is" We use Composite Index on a table. Which of the following statements will speed up and slow down the operations respectively: 1-Select 2-Insert 3-Modify 4-Delete "
So is there anyone who can help me in this? i would appreciate that! :)
Hi everyone. This is my first time here, and I am really in need of some help! I am a senior Information Technology Major and I am in my first SQL course where I have to write a report on a company that uses SQL Server. I haven't been able to find any companies in my area, and need to find a company to write my paper on as soon as possible. I just have a set of brief questions; they are as follows (and I understand, for security reasons, if you are unable to answer certain questions, do not feel obligated to do so!):
Basics: A brief overview of your company: What is your position? What the SQL Server database is used for?
Implementation: Which version of MS SQL Server is used? Type of instance(s) is(are) used? Which authentication mode is used? Do you utilize SQL Server Dynamic Disk Space Management, and if so, how? How is data security maintained? What type of encryption has been implemented?
Maintenance: What type of backups are completed and how often? How would the database would be restored in the event of data loss? How do you preserve data integrity within the database? Do you utilize any monitoring & troubleshooting tools to assess SQL Server performance (SQL Server Profiler, System Monitor, Database Engine Tuning Advisor, etc).
I greatly appreciate anyone willing to help me with my paper. Thank you! Lynette
I have an application that is developed to support a customer per database. All the data is unique to that customer and is physically partitioned from other customers. Also, I have a database that has common tables to all customers. I use stored procedures to access all data. I would like to keep from duplicating all the stored procedures (since the meat of them stays the same) because of the database references.
Is there any way to use the "USE <database>" functionality in the stored procedures to switch context dynamically without having to reference the unique databases?
I have a problem that I can't find any information on and I bet someone here has hit the wall on this.
I have 2 sql server 2000 servers runnng sql std edition. One has 8.00.2039 SP4 and one has 8.00.818 SP3. The server with SP4 will not let me use a parameter in the DTS SQL task. I get the infamous Access Violation Error. If I replicate the SQL task on the SP3 server I have no problem works great and life is good.
Can anyone tell me if thre is a hotfix for this or something? I've googled this to death and tried various possible fixes with no positive result. Any help would be greatly appreciated!!
Hello,I'm having some problems using the Report Manager, but I *really* needto make a role assignment now. Can't wait to have the problem withReport Manager solved.So, I would like to make this role assignment directly in theReportServer database, using the SP SetPolicy or some other SP.I'm looking for the equivalent of making the "New Role Assignment" andthenGroup or user name: EveryoneRole "Browser" checkedOKCan anyone tell me exactly how to do this?Thanks in advance,Filipe HenriquesPS: By the way, the problem I've got using the Report Manager is thatalways appear "The request failed with HTTP status 404: Not Found.",although I can see perfectly the Report Server page.Does anyone knows what's the problem?
Hi all, There is nothing happen when I finished my checkout process, I expect the data will be saved to order and orderitem table in my SQL database, but no data found on order and orderitem table and no error messages display during operation!!! Below is my checkout.aspx.vb code, the whole code line number around 138, I captured the part from 1~ 64 line number, I suspect line 35 - 48 have a problem, can somebody help me, many thanks. 1 Imports System 2 Imports System.Data.SqlClient 3 Imports SW.Commerce 4 Partial Class CheckOut 5 Inherits System.Web.UI.Page 6 Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 7 If Not Page.IsPostBack Then 8 If Profile.Cart Is Nothing Then 9 NoCartlabel.Visible = True 10 Wizard1.Visible = False 11 End If 12 If User.Identity.IsAuthenticated Then 13 Wizard1.ActiveStepIndex = 1 14 Else 15 Wizard1.ActiveStepIndex = 0 16 End If 17 End If 18 End Sub 19 Sub chkUseProfileAddress_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) 20 21 ' fill the delivery address from the profile, but only if it’s empty 22 ' we don’t want to overwrite the values 23 24 If chkUseProfileAddress.Checked AndAlso txtName.Text.Trim() = "" Then 25 txtName.Text = Profile.Name 26 txtAddress.Text = Profile.Address 27 txtcity.Text = Profile.City 28 txtCountry.Text = Profile.Country 29 End If 30 End Sub 31 Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) 32 33 ' Insert the order and order lines into the database 34 35 Dim conn As SqlConnection = Nothing 36 Dim trans As SqlTransaction = Nothing 37 Dim cmd As SqlCommand 38 Try 39 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("swshop").connectionstring) 40 conn.Open() 41 trans = conn.BeginTransaction 42 cmd = New SqlCommand() 43 cmd.Connection = conn 44 cmd.Transaction = trans 45 46 ' set the order details 47 48 cmd.CommandText = "INSERT INTO Order(MemberName, OrderDate, Name, Address1, Address2, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @city, @Country, @Total)" 49 cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50) 50 cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime) 51 cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50) 52 cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255) 53 cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 15) 54 cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50) 55 cmd.Parameters.Add("@Total", Data.SqlDbType.Money) 56 cmd.Parameters("@MemberName").Value = User.Identity.Name 57 cmd.Parameters("@OrderDate").Value = DateTime.Now() 58 cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text 59 cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text 60 cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text 61 cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text 62 cmd.Parameters("@Total").Value = Profile.Cart.Total 63 Dim OrderID As Integer 64 OrderID = Convert.ToInt32(cmd.ExecuteScalar())
How can I make it work when I need to pull out a field which is text type using a stored procedure? Please help!!!Thanks I am getting the following error Error 409: The assignment operator operation could not take a text data type as an argument ===========my sp================================= CREATE PROCEDURE [dbo].[sp_SelectABC] (@a varchar(50) output, @b text output ) AS set nocount on select @a=name, @b= description from ABC
I want to execute something likeselect iif(type='credit',amount*-1,amount) from tablehow can i do that? help file says iif is used in MultidimensionalExpressions but that seems overly complicated for this task.any ideas?
I would like to know if there is any sort of improvement when using Inline SQL vs Stored Procedures as far as the execution plans are concerned when they are used in SSIS packages. I happened to create a SSIS package which calls Stored Procedures but internally they are using Linked Servers to get the data required. At this point they want to know what will be benefit that can be achieved when Inline SQL is used.
I would appreciate if anybody can give their thoughts or provide some informative articles like pros and cons.
For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views?
select enc_id,labcnt,order_name,date_due
reate FUNCTION fn_GET_ARRAY_VALUE( @DELIMITER VARCHAR(100), @STRING VARCHAR(1000), @ARRAY_POSITION INT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @CURRENT_POSITION INT DECLARE @VALUE VARCHAR(8000) SET @CURRENT_POSITION = 0
WHILE @CURRENT_POSITION<@ARRAY_POSITION AND CHARINDEX(@DELIMITER,@STRING,0)>0 BEGIN SET @STRING = SUBSTRING(@STRING, CHARINDEX(@DELIMITER, @STRING, 0) +LEN(@DELIMITER), LEN(@STRING) -CHARINDEX(@DELIMITER, @STRING, 0) + LEN(@DELIMITER) ) SET @CURRENT_POSITION = @CURRENT_POSITION + 1 END
IF CHARINDEX(@DELIMITER,@STRING,0)=0 SET @VALUE = @STRING ELSE SET @VALUE = SUBSTRING(@STRING, 0, CHARINDEX(@DELIMITER, @STRING, 0) )
Hi, I'm trying to get MSSQL to choose which row to display from a result from a query, but it seems to need an 'IF' or something similar.
My query asks the database to pick out all rows that meet critera X, but sometimes (quite correctly) some of the rows created by query X are duplicates. (A data overlap nothing wrong with the query)
I want to be able to get the query to decide which one of these duplicates to display. There is a unique element.
In my example I want to remove ID 1 from the result, I can't use the 'active' column as this will remove ID3 fro the result. I want the query to recognise the duplicate based on the sysname, then choose to display the result with 0 in it.
Does anyone have a clue what I'm talking about? I'm loosing the plot.
i am not sure if this forum is right place to ask this question.. i am trying right a dos batch file to do setup of sql commands run by sqlcmd , run some dos commands etc
net start mssql$server sqlcmd -E .....
net stop mssql$server...
sqlcmd -E ....
in unix you can run isql with the sql commands place inside..
isql -Uuser -S server <<EOF select 1 select 2 go EOF
you can put above in a shell and it will run.
i am trying to do similar stuff in windows for sqlcmd.. how can i do it
only option i have is to create lot of .sql files and run with -i option on sqlcmd..
can some body let me know how to do a inline commands in dos? thx AK
I have a serialized XML that I got from a dataset. In my 'Data Flow Task', I bind the 'XML Source' source to this XML file. Since the XML file is having the schema along with it, I check the 'Use Inline schema' option. However, when I put a dataviewer to see the rows getting sent to the destination, I see that no rows are getting transfered. As you will see from the XML file I am trying to use, I do have one row to transfer.
I tried kepping the schema file and the content file separate and that worked. I am not sure if there are any inherent issues I need to take care of, when using inline schemas to transfer data. I have the SP2 for SQL 2005 installed.
I have two questions regarding in-line bar charts...
First, I put the text as data labels and check 'Auto' for positioning, most of the time the text is written to the right of the bar (which is what I want). However, there is one case that if the space to the right of the bar is not enough for the text, it starts writting on the bar itself.... is there a way I could set it so that the text only appears when there is enough space to the right, so that text never appears on any bars?
Secondly, is there a way I could put hyperlinks on the text?
I am developing ASP.NET 2.0 website. I need to know some about using stored procedure. I searched through google. But could now find a favourable repLy. Here is ..
Which way is efficient, using SQL inside the code or as SRORED PROCEDRE, which one to use with ASP.NET? Is the Stored procedure must be created withing the server or from my application?Can anyone please give some practicle explaination about this? My advance thanks for all...
Hi i have the following stored procedure, i am trying to convert it to inline sql, however i got stuck, because how would i output a value e.g "@Access_Right_ID", this is the stored procedure below CREATE PROCEDURE dhoc_AccessRight_Insert @AccessLevel char(50), @Access_Right_ID int OUT, @Tstamp timestamp out ASSELECT * FROM tblAccess_Right WHERE AccessLevel = @AccessLevel IF @@ROWCOUNT <>0 BEGIN RAISERROR('This record is already in the database',16,1) RETURN 14 END ELSE BEGIN SET NOCOUNT OFF; INSERT INTO tblAccess_Right ( AccessLevel ) VALUES (@AccessLevel); SET @Access_Right_ID = @@Identity SET @Tstamp = (SELECT Tstamp FROM tblAccess_Right WHERE Access_Right_ID=@Access_Right_ID) --Make sure this has saved, if not return 10 as this is unexpected error IF @@rowcount = 0 BEGIN RAISERROR('This record has not been inserted at this time, it might have been inserted by another user, please try again',16,1) RETURN 10 END ELSE BEGIN IF @@error <>0 BEGIN RETURN @@error END ENDEND GO
Hi i have the following method in my page, it works fine and everything, but i am trying to modify it so that it can take a stored procedure; protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);String sql = "SELECT userID, userName FROM users WHERE userName LIKE " + "'" + userName.Text + "%' OR organisation= " + "'" + OrganisationList.SelectedValue + "'"; conn.Open(); SqlCommand comm = new SqlCommand(sql, conn);SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection); DataList1.DataSource = reader; DataList1.DataBind(); conn.Close(); }
My question is what should my stored procedure be, and also how will i now structure the above method.
I regularly create stored procedures and use them like functions within other stored procedures.
I've never had any difficulty but then I never ran any metrics on it.
Does anyone know if there is an efficiency difference between that approach and just doing an inline query? How much of a difference is it? minimal? impractically large?
For example, if I define an sp like:
create proc isValidUser @userID int , @result int OUTPUT as
if exists(select * from user where userid = @userID) set @result = 1
I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.
(SELECT COUNT(SDD_Status) AS On_Time , SDD_Date as On_Time_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time' AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) a,
(SELECT COUNT(SDD_Status) AS Early ,SDD_DATE As Early_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early' And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) b,
(SELECT COUNT(SDD_Status) AS Late , SDD_Date As Late_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late' AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) c,
(SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration , SDD_Date As Stay_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) d,
(SELECT DISTINCT(SDD_Date) AS DelDate FROM SDD_Store_Delivery_Data_Table WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date)e,
Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.
