I began installing SQL Server 7.0 the other day on a standard NT Server. When I got to the installation options, Enterprise Edition was the only server option. It wouldn't install on standard NT. Nothing on the box, in the box, or printed on the cd indicated that it was the Enterprise Version. The error message says something about not being able to install on this server from this CD.
We called MS and they did admit to sending an unknown number of CD's out with the wrong setup. I just thought I would pass that on to those of you who might be questioning your own sanity.
Good luck getting the replacement copy. They are telling us that it will be 4+ weeks before they overnight our copy to us. I won't even try to figure out why it would take that long to get a copy. Let us all know if any of you have run into this. I would like to see how common this is.
I have several DTSX packages which send out report files by mail. For security purposes, the SMTP server can only be reached from specified IP addresses, from specified processes, with specified sender address. I know all this information, except the process name that sends out the emails in a DTSX package. So my question is, what process can be identified as the one that sends out the email?
We have SQL server 2005 Reporting Services SP1. In Report Manager I have a shared subscription that runs every hour between 8am and 6pm (I created the shared subscription in Report Manager, then modified it in Sql Server Management Studio to run the hours I wanted). I have a second shared schedule that runs Tuesday and Friday at 7am. For 2 separate report subscriptions - one uses the hourly schedule and the other uses the 2-day schedule, the first time they run during the day it sends 2 email - after that it sends only 1 email, as it should. I created a subscription to email a report when the report content is refreshed from a report snapshot. In Properties...Execution, I selected to render this report from a report execution snapshot, and selected my shared schedule. For the History property I have selected Allow report history to be created manually and Store all report execution snapshots in history; I am not using a schedule to add snapshots to report history in the History property.
When I look at the job in Sql Server Management Studio, it has only ran once each hour. When I look at the snapshot history, there is only one record for each hour. Why is the first scheduled instance sent twice instead of once?
Can anyone assist me? I am trying to create a trigger within our local database that will allow me submit an email to a local exchange server. Its an internal email that our company has created for responses by candidates when the original stored procedure meets a condition of TRUE.
Is SQL 7.0 capable of sending emails to a specified address through a Trigger? I was told it could but have yet to come across any documentation backing this up.
Would anyone know the generic syntax I can use to create such a trigger?
Any suggestions would be appreciated.
Thanks in advance, Claude Johnson cjohnson@staffmentor.net
I have a package which truncates files, fills them with data andshould send a mail with the files as attachment when it finishesrunning. In between, when the task is still running mail is sent withempty files. When I take a look at the directory (at the end of thetask), where the files are created I find out that the files are notempty.(e.g. in task: doA->doB->doC->SendMail. But instead of waiting tilldoB->doC finishes, it does the following: doA->SendMail ... may bebecause doB takes long.)Can I indicate anywhere that it should delay the mail until at the endof the task?Thanks in advance.Harp
I've setup the option to mail the error to a person. When the option is on I get the error message by mail but the package does not finish (eg. the failing task does not become red and the output windows never says anything about the error) - if I set the option to off the task fails as expected.
as declare @From varchar(8000) declare @Subject varchar(8000) declare @Body varchar(4000) declare @smtp varchar(8000) declare @counter int, @tbl varchar(8000) Declare @MailID int Declare @hr int Declare @To varchar(8000) Declare @tblquery varchar(8000) declare @id int, @deptemail varchar(8000), @tmpmth varchar(8000), @usedb varchar(8000) set @from = 'name@mail.com' set @subject = 'testheader' set @body = 'testing successful' set @smtp = 'smtp.com'
--========================================================================= --============================ get database name ======================= IF (LEN(MONTH(GETDATE())) = 1) BEGIN Set @TmpMth = '0' + CAST(MONTH(GETDATE()) AS varchar(2)) --01 END ELSE BEGIN Set @TmpMth = CAST(MONTH(GETDATE()) AS varchar(2)) --12 END SET @UseDB = 'DATA' + CAST(YEAR(GETDATE()) AS varchar(4)) + @TmpMth --aia_DATA200712 --=================================================================== --============================ get table number ======================= set @counter = 1 while @counter >= 59 begin IF (LEN(@counter) = 1) BEGIN Set @Tbl = '0' + @counter --01 END ELSE BEGIN Set @Tbl = @counter --12 END
--=========================check if table being created exists IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE catalog_name = 'temptable') GOTO table_1 --=================================== get all email accounts ===================== set @tblquery = ' select ID, Email INTO temptable FROM ( select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.inward' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.local' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null )data DECLARE deptemail_cursor CURSOR FOR select id, email from temptable where id is not null '
--============================================================================= --====just above you can see the cursor.. below is sending the emails --======================================================================
exec(@tblquery)
OPEN deptemail_cursor
FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail
WHILE @@FETCH_STATUS = 0 BEGIN --If LEN(@deptemail) > 0 --BEGIN set @to = @deptemail EXEC @hr = sp_OACreate 'CDo.message', @MailID OUT --CDo.message |CDONTS.NewMail <-- different mail server EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp EXEC @hr = sp_OASetProperty @MailID , 'From', @From EXEC @hr = sp_OASetProperty @MailID , 'HTMLBody', @Body EXEC @hr = sp_OASetProperty @MailID , 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID , 'To', @to EXEC @hr = sp_OAMethod @MailID , 'Send', NULL EXEC @hr = sp_OADestroy @MailID --END FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail END
CLOSE deptemail_cursor DEALLOCATE deptemail_cursor
Table_1: drop table temptable return
set @counter = @counter + 1
end
this is suppose to send email automatically to every email account that it will get from all the tables (around 250 tables). the problem is its not sending, but if i try to take my code outside of the "SET @COUNTER = @COUNTER + 1 END" and close the if statement above, i can produce the correct result.. i'm thinking maybe its the positioning? but, there could be some overhauling needed to do with this.. sorry for posting the sp. sorry for the trouble.. please help me
I receive the following error when trying to invoke xp_sendmail 'xp_sendmail: failed with mail error 0x80070005'. However, for alert notifications the email works fine. SQL is using an alias account to send mail. This account doesn't have any funny characters. Any ideas why the alert system works but xp_sendmail does not? The invokation of xp_sendmail is being done by SA.
I am having a few problems with Database Mail and wondered if anyone could assist. It sends test mails fine, but when I run the following script:
(I've changed my email address in this post to test@test.com they are accurate in the scripts.)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test Mail',
@recipients = 'test@test.com',
@body = 'Sent by sp_send_dbmail',
@Subject = 'SQL Server Email Test Email';
It gives the following errors.
(from the above script)
mailitem_id = 16 profile_id = 2 recipients = test@test.com copy_recipients = NULL blind_copy_recipients = NULL subject = SQL Server Email Test Email body = Sent by sp_send_dbmail body_format = TEXT importance = NORMAL sensitivity = NORMAL file_attachments = NULL attachment_encoding = MIME query = NULL execute_query_database = NULL attach_query_result_as_file = 0 query_result_header = 1 query_result_width = 256 query_result_separator = exclude_query_output = 0 append_query_error = 0 send_request_date = 2008-04-15 10:50:03.827 send_request_user = COMPANYTest.Test sent_account_id = NULL sent_status = failed sent_date = 2008-04-15 10:50:04.000 last_mod_date = 2008-04-15 10:50:04.513 last_mod_user = sa
(from the test mail)
mailitem_id = 11 profile_id = 2 recipients = test@test.com copy_recipients = NULL blind_copy_recipients = NULL subject = Database Mail Test body = This is a test e-mail sent from Database Mail on UKDEVSQL1 body_format = TEXT importance = NORMAL sensitivity = NORMAL file_attachments = NULL attachment_encoding = MIME query = NULL execute_query_database = NULL attach_query_result_as_file = 0 query_result_header = 1 query_result_width = 256 query_result_separator = exclude_query_output = 0 append_query_error = 0 send_request_date = 2008-04-15 09:51:46.530 send_request_user = COMPANYTest.Test sent_account_id = 4 sent_status = sent sent_date = 2008-04-15 09:51:46.000 last_mod_date = 2008-04-15 09:51:46.610 last_mod_user = sa
sysmail_event_log gives this error for mail item 16 (the scripted email):
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2008-04-15T10:11:41). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image, 2 (SELECT Location 3 FROM Location_Table 4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID, 5 (SELECT TypeOfProperty 6 FROM Type_Table 7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty 8 FROM Property_Table 9 WHERE (TypeID = @TypeID OR 10 TypeID IS NULL) AND (LocationID = @LocationID OR 11 LocationID IS NULL) AND (Price >= @MinPrice OR 12 Price IS NULL) AND (PriceMax <= @MaxPrice OR 13 PriceMax IS NULL)
SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ "MONTH(Some_Date) >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" This is NOT: SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ Month >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" it says - Invalid column name 'Month'
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting Alter Table Employees Add Constraint CK_SNN Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _ "S (@UserID, @UserName, @UserPassword, @Name, @Email)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userID.ParameterName = "@UserID" dbParam_userID.Value = userID dbParam_userID.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_userID) Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userName.ParameterName = "@UserName" dbParam_userName.Value = userName dbParam_userName.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userName) Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userPassword.ParameterName = "@UserPassword" dbParam_userPassword.Value = userPassword dbParam_userPassword.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userPassword) Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_name.ParameterName = "@Name" dbParam_name.Value = name dbParam_name.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_name) Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_email.ParameterName = "@Email" dbParam_email.Value = email dbParam_email.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
Return rowsAffected End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Message.Text = "Register Successed, click on the link WebCam for login"
Else Message.Text = "Failure" End If End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs) Line 53: Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Line 55: Message.Text = "Register Successed, click on the link WebCam for login" Line 56:
Hi please lok at this SP I have written and point where am I commiting mistake. The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails] ( @ProductCode Int = null )
AS
DECLARE @sqlString AS nvarchar(2000) SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode, Master.PROD_DESC_TEXT AS ProductName, Detail.PiecesPerBox, Detail.Price FROM cp_nafta_dws.PRODUCT AS Master INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM' BEGIN IF NOT (@ProductCode = NULL) BEGIN SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode END END
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color). my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across. Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");} private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"]; if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}
Just a quickie... Can anyone see anything wrong with this SQL. I'm using Microsoft SQL Server 2000.
Code:
SELECT * FROM PFP_UserProfiles, Users, PFP_UserSkills, PFP_UserIndustries WHERE PFP_UserSkills.SkillID IN ( '222', '221', '182') AND PFP_UserProfiles.IsPublic = 1;
I'm working on an assignment and I am about to give up. I can't figure out what I'm doing wrong. It seems like I have everything worked out, but when I run the SQL query i've come up with, I get errors regarding INVALID tables.
As far as I can tell, all my tables are valid. Can anyone give me any pointers on what i'm doing wrong **read: I'm not asking for my assignment to be done for me, just asking for help because I am so close to getting the right answer....i think**
I am trying to created a view and have a need for conditional logic:
Here is what I presently have (not working): ---------------------------------------------------------------------------- IF (ISDATE(COMPLETIONDATE) = 1) BEGIN CASE WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN' WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW' WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED' END AS THRESHOLDSTATUS END ELSE IF (ISDATE(COMPLETIONDATE) = 0) BEGIN CASE WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN' WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW' WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED' END AS THRESHOLDSTATUS END --------------------------------------------------------------------------
Can someone tell me what I am doing wrong?
Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".
Ive written the following code to check if a foreign key exists, and if it doesnt to add it to a table. The code i have is:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]')) BEGIN PRINT N'Adding foreign keys to [dbo].[tblProductStock]' ALTER TABLE [dbo].[tblProductStock] ADD CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID]) IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END END
However i keep getting the following error:
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '?'.
INSERT INTO IS_REGISTERED VALUES (54907,2715,'I-2001')
Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__IS_REGISTERED__629A9179'. Cannot insert duplicate key in object 'IS_REGISTERED'. The statement has been terminated.
How do I fix this? I am trying to insert this into the IS_REGISTERED Table.
I posted this issue a couple of days ago, but got no solution yet. Anyhow, here is the thing:
"A limited user account is able to see items on the report manager with no permission?"
I have created a local user account on the domain machine where the reports are deployed. This user is not a member of administrator group. It is just a user under the User Group. This user doesn't even have a permission on the Report Manager, not even a browser role. However, this user is able to see the contents of the report manager and also can make changes role assignment under the properties tab in the report manager.
Accourding to my observation so far, any user account created on this domain machine is acting like an admin on the Report Manager, with out being given a permission on the Report Manager. I know something is wrong
here i am trying to get the count of both match ((substring(aecprda_1.upc_1,1,11) = substring(z.upc,1,11) + (AECPRDA_1.product_id = z.vendorcode)... probably AND won't do the trick. i believe my below query (3) is wrong. what changes should i make to get both the match and continue further. (3) should be atleast greater than (1) or (2)
1. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (only the first match)
2. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 2183 (only the second match)
3. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11)) and (AECPRDA_1.product_id = z.vendorcode) )
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (1st & 2nd match.. i expect the count to be higher than 2)
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 9" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
I'm thinking when it returns 0 rows it should do the first email, else it should do the second email. But it's always doing the second. And I know it's 0 rows, because I'm not getting any results. And I made sure of it.
My code is below. It gives me an error message that says: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
My code is: SqlConnection TestConnection = new SqlConnection(); SqlDataAdapter TestAdapter = new SqlDataAdapter(); DataTable TestTable = new DataTable(); int RowPosition = 0;
So i started learning some sql compact edition, and i'm making a small applicaiton to store quotes in... So i started down that road, i setup a few table, had some auto increasing indexes, and i started working on it, after a while i wanted to delete my test data and so i deleted all the rows from each of my tables and then i tried to run a
Code Snippet
DBCC CHECKIDENT('AuthorsTable', RESEED, 0) Then i get an error.. The error i get is
"There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = DBCC ]"
This works fine in sql express, but what am i doing wrong, I've looked all over the books online and all i can see is that there is a DBCC command in SQLCE, I've been looking for an answer for about 3 hours now and I'm not finding anything. So if you know what's going on, or if there is something else i can do to accomplish the same task.
I am using MVWD 2005 Express and Sql Server 2005 Express. I am using SqlDataSource control and GridView control to disply a ata table, and let user to input data into the database through dropdownlist and TextBox. When I run the application I always get exception:"Conversion failed when converting character string to smalldatetime data type." I could not find anything wrong. Could anybody out there help me find out what's wrong with my code. Much appreciate it.Here is the code:<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"DataSourceID="SqlDataSource2" DataKeyNames="DocumentID"><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /><asp:BoundField DataField="DocumentName" HeaderText="DocumentName" ReadOnly="True"SortExpression="DocumentName" /><asp:BoundField DataField="DateInput" HeaderText="Date" SortExpression="DateInput" /><asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" /><asp:BoundField DataField="DocumentCategory" HeaderText="Category" SortExpression="DocumentCategory" /><asp:CheckBoxField DataField="TopDoc" HeaderText="TopDoc" SortExpression="TopDoc" /></Columns><EmptyDataTemplate>There is no data to be displayed!</EmptyDataTemplate></asp:GridView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" DeleteCommand="DELETE FROM [Documents] WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"InsertCommand="INSERT INTO [Documents] ([DocumentName], [DateInput], [Comments], [DocumentCategory], [TopDoc]) VALUES (@DocumentName, @DateInput, @Comments, @DocumentCategory, @TopDoc)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Documents]"UpdateCommand="UPDATE [Documents] SET [DocumentName] = @DocumentName, [DateInput] = @DateInput, [Comments] = @Comments, [DocumentCategory] = @DocumentCategory, [TopDoc] = @TopDoc WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"><DeleteParameters><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></DeleteParameters><UpdateParameters><asp:Parameter Name="DocumentName" Type="String" /><asp:Parameter Name="DateInput" Type="DateTime" /><asp:Parameter Name="Comments" Type="String" /><asp:Parameter Name="DocumentCategory" Type="Int32" /><asp:Parameter Name="TopDoc" Type="Boolean" /><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></UpdateParameters><InsertParameters><asp:ControlParameter Name="Comments" Type="String" ControlID="TextBox2" /><asp:ControlParameter Name="DocumentCategory" Type="Int32" ControlID="DropDownList1" PropertyName="SelectedValue" /><asp:ControlParameter Name="TopDoc" Type="Boolean" ControlID="RadioButton1" PropertyName="Checked" /></InsertParameters></asp:SqlDataSource>Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.ClickDim savePath As String = "C: empuploads"Dim fileName As String = FileUpload1.FileName Dim dt As DateTime = DateTime.Now SqlDataSource2.InsertParameters.Add("DocumentName", fileName) SqlDataSource2.InsertParameters.Add("DateInput", dt)SqlDataSource2.Insert()If (FileUpload1.HasFile) ThensavePath += FileUpload1.FileName FileUpload1.SaveAs(savePath) Label1.Text = "Your file was uploaded successfully."DisplayFileContents(FileUpload1.PostedFile)ElseLabel1.Text = "You did not specify a file to upload."End IfEnd Sub
