Hi all, I have a stored proc which returns twice the result and I dont
know why. Can someone have a look at the following code?
BTW, I commented the last SELECT/JOIN, cause that one doubled the
result too.
CREATE procedure ent_tasks_per_user_company (
@companyName as varchar(50),
@resourceName as varchar(50)
)
AS
SELECT
tasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID as
ProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,
tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,
codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,
codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,
taskStd.TaskName as TaskName, taskStd.TaskResourceNames as
TaskResourceNames, taskStd.TaskPercentComplete as TaskPercentComplete
INTO #myTemp
FROM MSP_VIEW_PROJ_TASKS_ENT as tasks
INNER JOIN MSP_OUTLINE_CODES as codes
ON
(
codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
AND
codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
)
INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
ON
(
taskStd.WPROJ_ID = tasks.WPROJ_ID
AND
taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
--AND
--taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
)
WHERE (tasks.TaskEnterpriseOutlineCode1ID <-1)
/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp
INNER JOIN MSP_OUTLINE_CODES taskCode
ON
(
taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
)*/
SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
@resourceName + '%'
I am very well versed on manipulating data using passthrough queries through access or the command object in ADO/VBA but this problem requires a sql server side solution and I have been assigned the task
Scenario
Access via an ODBC link cannot view Sql table data that uses a BigInt as a primary key. Access can update the data, insert data ands delete data to these tables - but viewing (by design) returns the actual count of the rows but each field is populated with #deleted.
I can view the table data if I use a passtyhrough query but performing row level and field level edits from there is impossible. Not all people have Studio manager so that is not an option.
We are moving away from our legacy application and the development company has already developed the schema using the BigInt so changing that to a numeric or integer is out of the question. My boss has determined that using a set of bridge tables with identical tables in a different database where we replace the bigint with int will work - we can view the data in the "bridged" database and even update it from select queries and simply by opening the table in view mode from Access.
Here is the hard part (for me) because I have never done this. I can write SQL and understand the security/roles etc, but I have never administered SQL server regarding server side triggers and stored procedures - I have always used the command object or passthrough queries to deal with sql stored procedures. I need to either
A)Create some trigger behind the tables in my bridge table that will fire a stored procedure to replicate the data to the bigint database. They are both on the same sever.
B)Somehow create a "mirror" that always replicates from my bridge database tables (5 of them) to the new database.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.
For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.My question is, how to I introduce a new select or join from another table without duplicating the original data?
got a blank mssql 2005 express database. my table name is aspnet_IPNUmber. got two (2) columns IPNumberStart and IPNumberEnd both varchar(Max).
could somebody make a sample stored procedure for me that will insert the following records? im still learning how to make stored procs and my webhost only allow creating database in my domain but not uploading my database.
Hi I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
Dim conn As New SqlConnection() conn.ConnectionString = Session("constr") conn.Open()
Dim cmd As New SqlCommand("dbo.CheckLogin", conn) cmd.CommandType = CommandType.StoredProcedure ' Create a SqlParameter for each parameter in the stored procedure. Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10) usernameParam.Value = Trim(txtuser.Text)
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10) pswdParam.Value = Trim(txtpassword.Text)
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5) Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10) Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
'IMPORTANT - must set Direction as Output useridParam.Direction = ParameterDirection.Output usercodeParam.Direction = ParameterDirection.Output levelParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection cmd.Parameters.Add(usernameParam) cmd.Parameters.Add(pswdParam) cmd.Parameters.Add(useridParam) cmd.Parameters.Add(usercodeParam) cmd.Parameters.Add(levelParam)
Dim reader1 As SqlDataReader Try If conn.State = ConnectionState.Closed Then conn.Open() End If Try reader1 = cmd.ExecuteReader Using reader1
If reader1.Read Then Response.Write(CStr(reader1.Read)) Session("userid") = reader1.GetValue(0) Session("usercode") = CStr(usercodeParam.Value) Session("level") = CStr(levelParam.Value) Server.Transfer("home.aspx") Else ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level") End If End Using Catch ex As InvalidOperationException ErrorLbl.Text = ex.ToString() End Try Finally If conn.State <> ConnectionState.Closed Then conn.Close() End If
End Try
Else ErrorLbl.Text = "Please enter you username and password"
I have a stored proc that is pulling varchar data from a column and trying to use it in the rest of the proc. The problem is that in some of the data there is a single quote (ie Dave's). How can I pass this data in a useable form.
Hi!I have this table:Units -id uniqueidentified (PK), -groupName NVARCHAR(50) NOT NULL, -name NVARCHAR(50) NOT NULL, -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id groupName name ratio -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks!
Hello, I have created a MS Exchange 2000 link server in my MS SQL Server 2k. I have created a stored procedure (and a view...) using info from that linked server. When I am logged on the server as the Administrator, I can call my stored proc without any problems. When I use another computer (and I am not logged as the admin of the server) and I call the stored procedure, the following error is always raised : Server: Msg 7302, Level 16, State 1, Procedure test_proc, Line 3 " Impossible de créer une instance du fournisseur OLE DB 'exoledb.DataSource.1'. " <== I know it is a french error but it can be translated as : "Unable to instancied the OLE DB 'exoledb.DataSource.1' provider"
I would like to know if I can make run my stored proc in the admin account or what should I do to make it work
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug: --- Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type. ---
Error from page execution: --- Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip") Line 78: Line 79: cmd.ExecuteNonQuery() ---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Ok. Here is what I need to do. I don't even know if it is possible. I have a production server and a db backup server. In a perfect world, I want to be able to place a copy of my databases (users and all) on the backup server and have it update (all changes) regularly.
I messed with DTS but it errors out because I don't have user accounts set up on the backup server. (I'm not entirely sure it does anything I want to do anyway)
I like the script table to.... function is sql server 2005, but I was wondering if there was a way to do this for the entire database.Like if I want to create the database and all the tables inside it without having to copy each individual table's code. Know how to do this? Thankee.
I am developing an application that uses SQL Server 2000 for the back end. I am at the stage where some modules in the app can be tested while I finish development on some others. I run my own tests against SQL Server running on my own PC but for other people to test I have set up another server with SQL Server 2000 and have restored my database there.
My question is as follows: I would like any changes to my database (structure and data) to be replicated on the test server's database (not necessarily immediately, but without much delay). I've heard the buzz words (log shipping, replication, etc) but would like some advice on the best way to proceed. At the moment I don't need any data back from the test server and I don't particularly care if test data on that server is lost although these may become issues later on.
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
i would like to create a new database role that has exactly the same permissions as an existing database role. the combinations of permissions are complicated enough to make this a time consuming task worthy of a script of some sort. any suggestions?
i'm running SQL Server 2000 and can't find anything about copying roles unless it's through DTS from one DB to another. not sure if this is applicable to basically duplicating and renaming a role in one database.
in case you're wondering why anyone would want to do this you may not be surprised to know that it's a government thing. some policy about differentiating between 2 roles -- even though they currently have exactly the same permissions, the similarities might change in the future.
I have a table that holds a number of offers made to a orginization for placements at a lecture. what im wanting to do is have each of the rows for a orginization repeated so that the names of people attending can be put into the database.
the result im looking to get is something like this where the name of the attendess would be inputed in an application. id | orginization | lecture | nameofattende 1 | orga | lec1 | j. blog 2 | orga | lec1 | s. smith 3 | orga | lec1 | h. samual 4 | orga | lec1 | j. sams 5 | orga | lec1 | b.j. james 6 | orgb | lec1 | m. curry 7 | orgb | lec1 | k. murry 8 | orgb | lec1 | g. hansen
Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?
I tried the following, but it doesn't seem to work:
INSERT tableOne (value1, value2, value3) OUTPUT source.id, inserted.id SELECT value1, value2, value3 FROM tableOne AS source WHERE ID = @number