Hello, can anyone see a problem with this T-SQL? 1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 GO
4 ALTER PROCEDURE [dbo].[Logon_P]
5 @User_ID VARCHAR(50),
6 @User_Password VARCHAR(50)
7 AS
8
9 IF EXISTS(SELECT 1
10 FROM [User]
11 WHERE [User_Name] = @User_ID)
12 BEGIN
13 RETURN 1
14 IF ((SELECT User_Password FROM dbo.[User] WHERE [User_Name]) = @User_ID) = @User_Password
15 BEGIN
16 RETURN 2
17 END
18 END
19 ELSE
20 RETURN 0
21 Its returning the following error:Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11An expression of non-boolean type specified in a context where a condition is expected, near ')'.
I have been trying to write a sub query to use the where not exists command and im having a problem. Basically i want to be able to return all the Items from the ProductMaster table which have not already been selected and are there fore in the CustItem table.
This is what i currently have for my query, however i do not recieve any rows.
Select ItemCode from ProductMaster where Not exists (Select ItemCode from CustItem where customer = @Customer)
I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields. SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR" SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue) I have more code to show, if this looks correct. Just let me know. Thanks in advance.
I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.
How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.
I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.
Hi, Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is: <asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource> This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get) HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query: blogID, title, description, tags, dateAdded, noOfComments 1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0 Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
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);
i have restored site using stsadm, site get successfully restored. all user of the site able to access the site but the unable to access from same machine on which it is restored. i got the error The file exists. (Exception from HRESULT: 0x80070050)
I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
i have to check whether SSIS package is present in MSDB or not.
I am using DTUTIL for this perpose . Command goes like this.
dtutil /SQL Package_for_testing /Exists
Error(0xC0014014) while checking for the existence of package "Package_for_testing" on SQL server
this is giving the error "The specified package does not exist. Description: The ExistsOnSQLServer method has encountered OLE DB error code 0x80 004005 (Login timeout expired). The SQL statement issued has failed. Source:"
But the package is there in MSDB when i checked manually.
I want to know what this error signify? and how i can solve it. With advance thanks Srinivasa Mahendrakar
this works fine, but the problem is when sql server restarts I loose trace but trace-file remains. When I want to create new trace with the same parameters I receive Error = 0x80070050(File Exists) . Is there any way to reuse trace-file? Or to change trace's file path to indicate file already created?RegardsKonRi
I am receiving errors from a job that no longer exists. The databases created for this no longer exist also.
DATE/TIME: 4/6/2007 3:32:00 PM
DESCRIPTION: The log shipping secondary database LOG1.log_ship_test has restore threshold of 45 minutes and is out of sync. No restore was performed for 6004 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
COMMENT: (None)
JOB RUN: (None)
I have gone into agent jobs and deleted those associated with it. I have also gone into the msdb database and deleted any lines associated with it in the log_shipping tables. But the errors keep coming. What do I need to do to clean this up?
I am getting this error, when I try to access a directory in the remote path. Though the directory exists, I get this error. 550 CWD CMD FAILED : EDC5129I NO SUCH FILE OR DIRECTORY.
When I try to access through the remote login, I am able to see the directory as well as the files there. How to overcome this error. Is there any workaround for this. Thanks.
SQL Server 7 i did a restore of a database, then tried to add the User login to it, but when i select database access, i get the followinf error :- "Error 15023, user or role already exists !
the user did exist on the Database, but when i select Database,Users or Database,Roles the User doesn't exist !! so i can't drop it !
When i run the Execute SQL Task i get the following error:
[Execute SQL Task] Error: Executing the query "exec dbo.myProc " failed with the following error: "End tag 'ROOT' does not match the start tag 'LagArtNr'. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Sicne the XML is valid I have no idea why the task fails... I have read this link... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619288&SiteID=1 ... where using FOR XML RAW should do it, but it doesn´t help me
Hi, When i try to save my stored procedure.. i am getting the above error and this is my sproc 1 INSERT INTO Statement..ClientSources 2 ( 3 ClientId, 4 ClientSourceId, 5 SourceName 6 ) 7 Select Distinct 8 @ClientId, 9 SOURCE_NUM, 10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 11 WHEN 2 Then SRC2NAME 12 WHEN 3 THEN SRC3NAME 13 WHEN 4 THEN SRC4NAME 14 WHEN 5 THEN SRC5NAME 15 WHEN 6 THEN SRC6NAME 16 WHEN 7 THEN SRC7NAME 17 WHEN 8 THEN SRC8NAME 18 WHEN 9 THEN SRC9NAME 19 WHEN 10 THEN SRC10NAME 20 WHEN 11 THEN SRC11NAME 21 WHEN 12 THEN SRC12NAME 22 WHEN 13 THEN SRC13NAME 23 WHEN 14 THEN SRC14NAME 24 WHEN 15 THEN SRC15NAME 25 END 26 FROM 27 PlanDBF p 28 Where 29 p.PLAN_NUM = s.PLAN_NUM 30 ) as SourceName 31 FROM 32 SourceDBF s 33 Where 34 SOURCE_NUM NOT IN ( 35 SELECT DISTINCT 36 ClientSourceId 37 --SourceName 38 FROM 39 Statement..ClientSources 40 Where 41 ClientId = @ClientId 42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help will be appreciated. Regards Karen
I used a backup copy of our production DB (residing in our prod machine) to do a database RESTORE to our test DB (residing in our test machine). This step was successful. However when I tried to access the test DB via Peoplesoft application, I am unable to logon. Only then did I notice that all the users, with the exception of "sa", were gone. When I attempted to add a user via Enterprise Manager's Action - Add Database User, I get the message, "Error 15023: User or role '%' already exists in the current database. What's the best way to fix this without resorting to copying the source server's master database (If i do this, I risk clobbering some other DB's that are present in the target server but not in source server)? Any help you can provide will be greatly appreciated!!!
While trying to build SSIS Package Project, the following error is thrown. I have added configuration file recently to fetch the dynamic values such as connection string, output path etc.
I tried manually cleaned up the debug folder and tried build but still the error occurs.
Can any one help in suppressing this error?
Error 1 System.ApplicationException: Could not copy file "D:ExportExport.dtsConfig" to the deployment utility output directory "D:ExportinDebug". ---> System.IO.IOException: The file 'D:ExportinDebugExport.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0
Help! I am importing a large comma delimited text file into an existing table useing the BULK INSERT command. The table is 4 colums (char16, char16, varchar50, char1). The first 100 or so lines go in without an error. then, I recieve an error stateing that an entry is too long for the field in the database, and kicks me out. The entry is 50 characters, which is allowed. Any ideas why this would happen?
I am creating a function which is going to return a table. The Code ofr the function is as follows... =============================== Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70)) AS begin declare @t1_sys char(10),@t1_all varchar(11) declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS) if (substring(@cg1,1,2)='Q$') set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$') DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY insert into @temp_qcard values(@cg1) OPEN C1 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all WHILE @@FETCH_STATUS = 0 BEGIN
insert into @temp_qcard values(@t1_all)
declare @t2_sys char(10),@t2_all varchar(10) DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
begin OPEN C2 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t2_all)
declare @t3_sys char(10),@t3_all varchar(10) DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
begin
OPEN C3 FETCH NEXT FROM c3 INTO @t3_sys,@t3_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t3_all) FETCH NEXT FROM c3 INTO @t3_sys,@t3_all end end close c3 deallocate c3 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all end end close c2 DEALLOCATE c2
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all END
CLOSE c1 DEALLOCATE c1 Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata
RETURN END ==========================
While compiling this I am getting the Below error .... ================== Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35 Mixing old and new syntax to specify cursor options is not allowed. =================
Can Anyone please help me how to resolve this issue...
Can't get over this error, and net searches reveal other postings similiar, but no answers.
SSIS database transfer task (with overwrite) from SQL 2k source to SQL 2k5 destination fails with:
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [RFRSH_USER] " failed with the following error: "User, group, or role 'RFRSH_USER' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter.
The error seems the same regardless if the destination DB exists or not!
Lately I noticed that I cannot return any rows using enterprise manager. The version I am using is MS-SQL 7.0 with SP 1 on NT. This will return an error:-
An unexpected error happened during this operatation. [Query]-Query Designer encountered a query error. Unspecified error.
I can return rows using another computer with enterprise manager installed.
This query was run against a database on 2008 SP3 and 2012 SP2
Here is an example of what I ran
Declare @A_number varchar(5) Select Top 1 Column_Is_an_int = @A_number From Our_Main_Table Where A_Database_Name = 'A database with many records in this table and multiple records in the Column_is_an_int some are more than 5 characters'
Select @A_number Exec My_Stored_Proc @A_Nmuber
The top result for that database had an integer that was 8 characters including the - sign.
When the query ran I would normally expect it to throw the binary data cannot be truncated error. However in this case it returned a * in the variable which then tried to pass it in to the SP which of course threw a fit.
Once I changed the varchar(5) to varchar(50) it worked perfectly.
HI,I am getting an error on my sqlserver database server. My clients aregetting kicked out of transaction, giving an error"New transaction cannot enlist in the specified transaction coordinator"When i saw the event log on the DB server i found the following message************************************Event Type: InformationEvent Source: MSDTCEvent Category: CMEvent ID: 4156Date: 6/13/2005Time: 9:30:48 AMUser: N/AComputer: XXXXXXXDescription:String message: ProcID= 0x780 QMGR::Receive Returning Out Of ResourcesError.************************************************** ***************I am using SQLServer 2000 Advanced server, on Windows 2000 Advanced server.Thanks for help.Indushekar.
I have a .rdl file that was exported out of ProClarity's Desktop Professional 6.1 using their RS plug-in. I uploaded the file into Report Manager and when I execute it, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'Three_Month_Funding_Trend'. Unable to recognize the requested property ID 'ReturnCellProperties'.
Does anyone have any idea what this is referring to? Does it have something to do with my configuration, connection or the report definition? Other reports such as DBMS based reports work fine.
I have just installed SSRS 2005 SP2 on a x86 box (one of several boxes we use to run SSRS). However, on this particular machine, I get an http 404.3 error (file not found) when attempting to access any aspx file in the reports virtual directory. aspx files in the /reportserver site all return just fine.
All the check marks for the configuration site show green I've uninstalled / re-installed / re-booted / etc... No luck
I have small problem with the ADO 2.6 - 2.8 Command object.
I created a Stored procedure preforming some tasks within a tran.
whitin the tran i'm collecting the @@ERROR values and in the end (after tran closing )generating the correct error string - which will be send back to the caller
in the VB6 app, i'm using a command object with the next line:
Set MyRecordset= MyCommand.Execute
In all the cases and option, when i run the SP - the command preform the tasks and return closed recordset (with the currect answer) and i can't read it.
the only solution that worked until now: i need to call MyRecordset.Open which will requery the SP again and generate an error (User defined error).
It happens only when i run command.execute with SP that preforms multitasks and return result recordset.
Due to a SQL 2014 cluster installation failure related to security while setting up the primary node, I had to remove the node and any related installation programs from the node and redo the installation again. However I am unable to use the machine name as the SQL network name during the instance configuration, I get the following:
Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException: The SQL Server failover cluster instance name "MachineName" already exists as a server on the network. Specify a different failover cluster instance name.I am still getting the same name since the node name "MachineName" is listed under the cluster name. I have used machine name as SQL network name without an issue. I do not have any existing SQL machine name in network using same machinename which I want to use for this installation.
I am trying to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid or invalid, I get the error.
Anyone else seen this? There is an amazing lack of any discussion about the error when I search for it.
It doesn't even make any difference what I put in the @provstr parameter - the sp_addlinkedserver statement always executes without an error, but running a query that uses the linked server generates the error.
