Hi there!
I've a table with a nvarchar(max) column on Filegroup2. While inserting a lot of big datas (Word documents, each 2.5MB), the primary filegroup is growing - but the Filegroup2 remains still on its creation size.
Is that a bug? By the way, i ve dropped the table and recreated on the other filegroup, after that i've restartet the SQL 2005 Service.
Someone experience?
Thanks, Torsten
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
This is my starting point:
boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209
After I switch partition 1 to a staging table I run:
ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;
and
ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');
I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:
PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210
How do I make sure that the data stays in AdtLog_Archive_201209 file group?
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!
View 1 Replies View RelatedI'm having a database insertion problem. I make an entry to my registration form, write it to the database; then the next registration entry displays an error that the same UID can't be written to the database. I don't know where to start tracing this, but here is the stored procedure I use and some of the code that might generate it.
@UserName nvarchar(128), @Email nvarchar(50), @FirstName nvarchar(25), @LastName nvarchar(50), @Teacher nvarchar(25), @GradYr int DECLARE @UserID uniqueidentifier SELECT @UserID = NULL SELECT @UserID = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName
INSERT INTO [table name](UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr) Protected Sub cuwCreateUserWizard1_CreatingUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles cuwCreateUserWizard1.CreatedUser strEmail = CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Email"), TextBox).Text strUserName = CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text.ToLower strFirstName = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtFirstName"), TextBox).Text strLastName = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtLastName"), TextBox).Text lngGradYr = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtGradYr"), TextBox).Text strTeacher = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtTeacher"), TextBox).Text End Sub Protected Sub cuwCreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles cuwCreateUserWizard1.CreatedUser Dim cmd As New SqlCommand("sp_CreateUser", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@UserName", strUserName) cmd.Parameters.AddWithValue("@Email", strEmail) cmd.Parameters.AddWithValue("@FirstName", strFirstName) cmd.Parameters.AddWithValue("@LastName", strLastName) cmd.Parameters.AddWithValue("@Teacher", strTeacher) cmd.Parameters.AddWithValue("@GradYr", lngGradYr) Using con con.Open() cmd.ExecuteScalar() con.Close() End Using cmd.Equals(Nothing) End Sub
Please let me know if any other info is needed to help determine what's wrong.
Hi,
i have following problem:
I have a DB with 4 tables in MSSQL6.5. In those tables write an NT service (4 threads for each table) 24h/day. I want to know somehow when i have no write in one of those tables for more than 1-2 hours (i dont want to querry those tables everytimes). MSSQL6.5 offer this kind of facilities? Or I can do this using stored procedures or something else?
Thank you very much,
Sebastian Bologescu
I am very new to SQL server and I'm using stored procedures for my program. So far I wrote one tonight, that works just fine.
I haven't really written one before, but its kind of similar syntax since I know C++/C# and VB.
My question is, even though this works for what I need it do, is it written correctly? Can you see any problems with it, or would you have done it differently?
I want to make sure its done correctly, and it runs as fast as possible.
Thanks!
[pre]
CREATE PROCEDURE CreateNewUser
@UserID int out, @LoginID nvarchar(30), @Password nvarchar(30), @RegisterDate smalldatetime, @LoginIDExists nvarchar(30)AS /* Check to see if the loginID is already in use before attempting to save it. We MUST have a unique loginID for each user */ SELECT @LoginIDExists = loginID FROM users WHERE loginID = @LoginID
/* If we pulled a value from the database, then the loginID already exists, return with error code 1 */ IF (@LoginIDExists = @LoginID) BEGIN SELECT 1 RETURN END
ELSE BEGIN /* The loginID does not already exist, attemp to add the new user to the database. */ INSERT INTO users ( loginID, loginpassword, registerDate ) VALUES ( @LoginID, @Password, @RegisterDate )
/* Grab the UserID for the new user. */ SELECT @UserID = @@identity
/* return with error code 0 */ SELECT 0 RETURN ENDGO
[/pre]
I'm wondering if anyone has a written project plan for upgrading a server to 7.0 from 6.5.
I'm looking to make this upgrade myself shortly. I would appreciate any dialog, or a written
working plan. Thanks,
Mark Blackburn o `"._ _ M onterey
mark@mbari.org o / _ |||;._/ ) B ay
Science at its Best! ~ _/@ @ /// ( ~ A quarium
(831) 775-1880 ( (`__, ,`| R esearch
http://www.mbari.org/ '.\_/ |\_.' I nstitute
Database Administrator
MBARI Personal Web Page: http://www.mbari.org/~mark/
If someone can tell me if this script run with sql2000 it would be great.
Thanks in advance.
SELECT "Date du traitement :" = getdate()
go
print 'ORDRE DE TRI '
print '----------------------'
go
EXEC SP_HELPSORT
GO
print ' '
print 'VERSION'
print '-------------'
print ' '
go
SELECT @@VERSION
GO
print ' '
print 'CONFIGURATION DU SERVEUR '
print '----------------------------------------------'
print ' '
go
EXEC SP_CONFIGURE
GO
print ' '
print 'LISTE DES BASES SQL'
print '---------------------------------'
print ' '
go
EXEC SP_HELPDB
GO
PRINT " "
PRINT " "
PRINT 'LISTE DES DEVICES'
PRINT '-----------------'
PRINT " "
EXEC SP_HELPdevice
PRINT " "
GO
DECLARE @cmd NVARCHAR (300)
DECLARE @cmd1 NVARCHAR (300)
DECLARE @cmd2 NVARCHAR (300)
DECLARE @cmd3 NVARCHAR (300)
DECLARE @dbid INT
DECLARE @MaxId INT
DECLARE @dbName SYSNAME
SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
SET @dbid = 1
print " "
print " "
print'DETAIL DE CHAQUE BASE 'print" "print" "
WHILE @dbid <= @MaxId
BEGIN
SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)
IF (@dbname IS NOT NULL)
BEGIN
select @cmd3 ='DETAIL DE LA BASE ' + @dbname
print @cmd3
PRINT '************************************************* ********************************************'
print ' '
select @cmd = 'use ' + quotename(@dbname) + ' exec sp_helpfile'
exec (@cmd)
print ' '
print ' '
print 'UTILISATEUR DE LA BASE '+ RTRIM(@dbname)
print '************************************************* ******** '
print ' '
select @cmd1 = 'use ' + quotename(@dbname) + ' exec sp_helpuser'
exec (@cmd1)
PRINT ' '
print ' '
print 'DROITS POUR LES OBJETS DE LA BASE '+ RTRIM(@dbname)
print '************************************************* ******** '
print ' '
select @cmd2 = 'use ' + quotename(@dbname) + ' exec sp_helprotect'
exec (@cmd2)
SET @dbid = @dbid + 1
END
ELSE
SET @dbid = @dbid + 1
print " "
print " "
END
I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10
NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10
and so on for a 6 month period.
Hey guys, I had purchased a program that generates full operational asp.net pages from an access db. I was unfamiliar with the language, and so that was the route I took. After disecting it and studying I've learned a bit and made it work in my environment. However, I now need to migrate everything over to mssql. I got the db portion down and now I'm trying to modify my scripts to point to a sql db rather than an access db. I thought it was going to be relatively simple but it's proving to be quite a challenge for me.Was wondering if anyone could check my work.
The original access driven page had the following connection strings.vb page had [ public strConn as string="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("../") & "/db/db.mdb"end class]and in my aspx. pages I hadAccessDataSourceControl1.selectcommand="SELECT * FROM table "AccessDataSourceControl1.ConnectionString = strConn
My new page now has the following.I got rid of the .vb page and put this in.
Dim myConnection as New SqlConnection("Server=mysqlserver;Database=db;UID=myusername;PWD=my pw;")
Const strSQL as String ="SELECT * FROM table"
Dim myCommand as New SqlCommand(strSQL, myConnection)
further down I have a wmx data grid. I'm assuming I don't need to touch that but there is a line that has,
<wmx:AccessDataSourceControl id="AccessDataSourceControl1" runat="server" ></wmx:AccessDataSourceControl>
and I don't know what the equivilant of that is. I know its pointing to the accessdatasoucrcontrol1 that has the select command, but what would be the equivilant ofwmx:accessdatasourcecontrol?Also, is this the way to go? by that i mean, should I just rescript everything? Also,
1) Am I connecting to the sql db properly? 2) some sites say I have to put a connection string in the web.config file, is that true?3) Does any one have any references I can goto, I'm having some trouble finding a good article that lays down exactly what needs to go where in an sql asp.net environment.
Thanks guys.
I added one crummy column to my table. I updated the stored procedure and added the thing to the aspx page which is my form for adding an article. I have a strong feeling that something is foul over here...Why is it that Visual Studio will not allow me to capitalize the word get and when I delete the () after ShortDesc they immediatlye reappear and the get statement is set to get. Every other one of them, and there are 9 others use GET and there is no () after the public property variable name. Does anyone know the reason for this?
Public Property Author As System.String
GETReturn _Author
End Get Set(ByVal Value As System.String)
_Author= ValueEnd Set
End PropertyPublic Property ShortDesc() As System.String
GetReturn _ShortDesc
End GetSet(ByVal value As System.String)
End Set
End Property
I am beginning to write stored procedures for an application which is to be written in VB5. The stored procedures will be written in 6.5 but when the system goes live, we hope to upgrade to SQL server 7
What I want to know is what changes will I have to make to the stored procedures when I upgrade to 7.0?
I've created an alias of my computer name
with the Network client utility
Computer name : SCG59730
Instance name : Mercure
Alias : MyProject
When I try to register this new server (MyProjectMercure)
SQL Server doesn't find the server
Hello,
View 6 Replies View RelatedI have a 3 data flows with an oledb source, a script component and a sql server destination that reports success and that it wrote the rows to the table in question. However, the rows are not written to the SQL Server 2005 table that is the destination. I have many other data flows in the same package that work exactly the same way that insert the rows they should insert in the destination table.
An example of the message I get indicating success is as follows:
[DTS.Pipeline] Information: "component "SQL Server Destination" (75)" wrote 289 rows.
Has anyone else seen this behavior or have an idea what might be wrong? Why would the data flow report success when the rows were not, in fact, inserted in the destination table?
Hello,
We're trying to launch a SSIS package throw a webservice. This WebService loads a configuration file with "myPackage.ImportConfigurationFile(pathConfig)" and assigns values to some variables with "myPackage.Variables.Item(Parameters.Keys.Item(i)).Value = Parameters.Item(i)". Seems to work.
I we execute the webmethod of the WebService, this works but only when we execute first time, the SSIS finished succesfully. But when we execute a second time the Webmethod, we've a lot of failure with messages:
The result of the expression "@[User::Email]" on property "ToLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.The result of the expression ""The Import of " + @[User::ExcelFile]+ " is successful"" on property "MessageSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Thx for your help
Hi guys,
acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...
Can any one tell's me about this...
ok. the problem: some tables are empty, so i can't be sure why some are updating at the DB and some arent.
I have manually picked thru every line of the xml that i'm reading into the dataset here, and it is fine, data is all valid and everything.
the tables i'm most worried about are bulletins and surveys, but they all have to be imported from my upload, the code steps thru just fine and I've been thru it a million times. Did I miss something in my dataadapter configuration?. 'daBulletin ' Me.daBulletin.ContinueUpdateOnError = True Me.daBulletin.DeleteCommand = Me.SqlDeleteCommand17 Me.daBulletin.InsertCommand = Me.SqlInsertCommand17 Me.daBulletin.SelectCommand = Me.SqlSelectCommand25 Me.daBulletin.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblBulletin", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("BulletinID", "BulletinID"), New System.Data.Common.DataColumnMapping("ContractID", "ContractID"), New System.Data.Common.DataColumnMapping("Msg_Type", "Msg_Type"), New System.Data.Common.DataColumnMapping("DatePosted", "DatePosted"), New System.Data.Common.DataColumnMapping("Subject", "Subject"), New System.Data.Common.DataColumnMapping("B_Body", "B_Body"), New System.Data.Common.DataColumnMapping("I_Read_It", "I_Read_It"), New System.Data.Common.DataColumnMapping("DateRead", "DateRead")})}) Me.daBulletin.UpdateCommand = Me.SqlUpdateCommand16
here is my merge function: Private Function Merge(ByVal sFilename As String, ByVal User As String) As String
Dim connMerge As New SqlConnection(ConnectionString)
Dim dsNew As New dsBeetleTracks
Dim dsExisting As New dsBeetleTracks
Dim strResult As String
SetConnections(connMerge)
Dim idc As New System.Security.Principal.GenericIdentity(User)
Dim currentUser As BeetleUser = bu
dsNew.ReadXml(sFilename)
If currentUser.IsInRole("Admin") Or currentUser.IsInRole("QA") Then
If dsNew.tblBulletin.Count > 0 Then
daBulletin.Fill(dsExisting.tblBulletin)
dsExisting.Merge(dsNew.tblBulletin)
strResult += daHelipads.Update(dsExisting.tblBulletin).ToString + " Bulletins updated<br>"
End If
End If
If dsNew.tblHours.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("Admin")) Then
daHours.Fill(dsExisting.tblHours)
dsExisting.Merge(dsNew.tblHours)
strResult += daHours.Update(dsExisting.tblHours).ToString + " hours updated<br>"
End If
If dsNew.tblHeliPads.Count > 0 Then
daHelipads.Fill(dsExisting.tblHeliPads)
dsExisting.Merge(dsNew.tblHeliPads)
strResult += daHelipads.Update(dsExisting.tblHeliPads).ToString & " helipads updated "
End If
If dsNew.tblExpenses.Count > 0 Then
daExpenses.Fill(dsExisting.tblExpenses)
dsExisting.Merge(dsNew.tblExpenses)
strResult += daExpenses.Update(dsExisting.tblExpenses).ToString + " expenses updated<br>"
End If
If dsNew.tblPersons.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn")) Then
daPersons.Fill(dsExisting.tblPersons)
dsExisting.Merge(dsNew.tblPersons)
strResult += daPersons.Update(dsExisting.tblPersons).ToString + " persons updated<br>"
End If
If currentUser.IsInRole("Field") Then
daSurveys.SelectCommand.CommandText = "exec Surveys_Field_Select"
daSurveys.InsertCommand.CommandText = "exec Surveys_Field_Insert"
daSurveys.UpdateCommand.CommandText = "exec Surveys_Field_Update"
End If
If dsNew.tblSurveys.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("Field")) Then ' Or CurrentUser.IsInRole("Admin")) Then
daSurveys.Fill(dsExisting.tblSurveys)
dsExisting.Merge(dsNew.tblSurveys)
strResult += daSurveys.Update(dsExisting.tblSurveys).ToString + " surveys updated<br>"
End If
If dsNew.tblSurveyChecks.Count > 0 And (currentUser.IsInRole("QA") Or currentUser.IsInRole("Admin")) Then
daSurveyChecks.Fill(dsExisting.tblSurveyChecks)
dsExisting.Merge(dsNew.tblSurveyChecks)
strResult += daSurveyChecks.Update(dsExisting.tblSurveyChecks).ToString + " survey checks updated<br>"
End If
If dsNew.tblTreatments.Count > 0 And (currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn")) Then ' Or CurrentUser.IsInRole("Admin")) Then
daTreatments.Fill(dsExisting.tblTreatments)
dsExisting.Merge(dsNew.tblTreatments)
strResult += daTreatments.Update(dsExisting.tblTreatments).ToString + " treatments updated<br>"
End If
If dsNew.tblInternalQC.Count > 0 And (currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn") Or currentUser.IsInRole("Survey")) Then ' Or CurrentUser.IsInRole("Admin")) Then
daInternalQC.Fill(dsExisting.tblInternalQC)
dsExisting.Merge(dsNew.tblInternalQC)
strResult += daInternalQC.Update(dsExisting.tblInternalQC).ToString + " internalqc updated<br>"
End If
If dsNew.tblTreatmentChecks.Count > 0 And (currentUser.IsInRole("QA") Or currentUser.IsInRole("Admin")) Then
Try
daTreatmentChecks.Fill(dsExisting.tblTreatmentChecks)
dsExisting.Merge(dsNew.tblTreatmentChecks)
strResult += daTreatmentChecks.Update(dsExisting.tblTreatmentChecks).ToString + " treatment checks updated<br>"
Catch dbex As DBConcurrencyException
strResult += vbCrLf & dbex.Message
For x As Integer = 0 To dbex.Row.Table.Columns.Count - 1
strResult += vbCrLf & dbex.Row.GetColumnError(x)
Next
End Try
End If
If dsNew.tblHeliPiles.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then ' Or CurrentUser.IsInRole("Planner")CurrentUser.IsInRole("QA") Or CurrentUser.IsInRole("Admin") Or
daHeliPiles.Fill(dsExisting.tblHeliPiles)
dsExisting.Merge(dsNew.tblHeliPiles)
strResult += daHeliPiles.Update(dsExisting.tblHeliPiles).ToString + " piles updated<br>"
End If
If dsNew.tblHeliCycles.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then ' CurrentUser.IsInRole("Planner") Or Or CurrentUser.IsInRole("Admin")) Then
daHeliCycles.Fill(dsExisting.tblHeliCycles)
dsExisting.Merge(dsNew.tblHeliCycles)
strResult += daHeliCycles.Update(dsExisting.tblHeliCycles).ToString + " cycles updated<br>"
End If
If dsNew.tblHeliTurns.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then 'CurrentUser.IsInRole("Admin") Or CurrentUser.IsInRole("Planner") Or
daHeliTurns.Fill(dsExisting.tblHeliTurns)
dsExisting.Merge(dsNew.tblHeliTurns)
strResult += daHeliTurns.Update(dsExisting.tblHeliTurns).ToString + " turns updated<br>"
End If
If dsExisting.HasChanges Then
dsExisting.Merge(dsNew)
End If
dsExisting.AcceptChanges()
'duh.
'If dsNew.HasChanges Then
' dsNew.AcceptChanges()
'End If
If dsExisting.HasErrors Then
Dim bolError As Boolean
Dim tempDataTable As DataTable
bolError = True
strResult += "<br>"
For Each tempDataTable In dsExisting.Tables
If (tempDataTable.HasErrors) Then
strResult += PrintRowErrs(tempDataTable)
End If
Next
End If
dsNew.Dispose()
dsExisting.Dispose()
connMerge.Close()
'edebugging will only track strresult
Dim fsError As New FileStream(Server.MapPath("./incoming/error.txt"), FileMode.Create, FileAccess.Write)
Dim swError As New StreamWriter(fsError)
swError.WriteLine("--==ERROR LOG==--")
swError.WriteLine(Now.Date.ToShortDateString)
swError.WriteLine("-----------------")
swError.WriteLine(strResult)
swError.Close()
fsError.Close()
Return strResult
End Function
SS 2005 64Bit SP2 Hello Chaps Intermittent problem with the SQL Agent job history not getting written to the history table. Background:Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs. There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it. Stuff run to try to track the job:EXEC sp_help_jobactivity @job_name = 'MyJob' EXEC sp_help_jobhistory @job_name = 'MyJob' SELECT *FROM dbo.sysjobhistory WHERE job_id = 'MyJob GUID'The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null). sp_help_jobhistory had some historical records but nothing since mid last month. sysjobhistory correlated with sp_help_jobhistory as you would expect. Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June). We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it. We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will. Ta!
View 7 Replies View RelatedI added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.
ddave
----------------------------
SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''')
AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '
does anybody know, is there any possible way to connect to database written in Natural ?? After evaluating everyday backups on that database all i can see is BCK data format. im trying to create datawarehouse running on sql server 2000 and gather all operational data from the systems that are working in my firm. I just managed to get data originating from Interbase and these with dbf and txt format . unfortunatelly my problem with bck data format is still open.
As you can see im a beginner with that issue so if anybody could help i would be very gratefull.
Hi,I'm trying to write a java.sql.Date to a database and the minutes/seconds etc. are not being written. I've seen and tested manyexamplesfound via searches and still have come up with nothing. Here's what Ihave:java.sql.Date formation - recommended constructor forms:java.sql.Date entryDttm = newjava.sql.Date(System.currentTimeMillis());ORjava.sql.Date entryDttm = new java.sql.Date(newjava.util.Date().getTime());// prepared statement insertpstmt.setDate(1, entryDttm);// what is written to database2007-02-07 12:00:00.000// what needs to be written2007-02-08 09:37:25.793The above date value is written when I insert using the SQL methodGETDATE()The field is stored in a MS SQL Server 8.0 database and is defined asa 'datatime' or 'smalldatetime' field.How can I replicate the results of GETDATE() into 'datetime' and'smalldatetime' fields ina MS-SQL Server database (or is it possible?). Would a timestampdatatype make more sense?I'm using MS_SQL Server JDBC drivers.Any advice would be GREATLY appreciated!
View 2 Replies View RelatedHow can I declare output parameter in stored procedure, written in C++/CLR?
In C# it is proc_name( out SqlInt32 value ).
In VB.NET it is proc_name( <Out()> ByRef value As SqlInt32 ).
And in C++ ?
Thanks a lot for support.
Hi,
I'm trying to add exernal functions defined by a third party in a dll. They developed the dll in Visual C++ and is provided as is, with very little additional info about the code. The dll is tested and can be integrated into an application developed with VB.NET but no other experiences are available. I tried to add the functions to SQL Server 2005 Express edition but on attempting to CREATE the ASSEMBLY i get the following error:
CREATE ASSEMBLY for assembly 'ThirdPartyDll' failed because assembly 'ThirdPartyDll' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = -6
Finished running [dbo].[ThirdPartyDll].
Is there an alternative way to add these functions for non pure .NET assemblies, or in general, is there a workaround to solve the problem?
By the way, do i need to register the dll with RegSvr32 first?
Thanks,
Marco.
where in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 Replies View RelatedHi All
I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).
I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.
Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.
Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).
Thank in advance.
Eamon.
Having two tables.
1. ID Year
----------
1 1990
2 1991
3 1992
2. Name Year
--------------
ABC 1
XYZ 2
123 3
By passing year as an argument, Want to retrieve all the Names that does not have records for that particular year.
For example, if pass 1990, then the result set should be 'XYZ' and '123'
Thanks in advance
in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
A question for everyone:
With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
Thanks, in advance,
Brian
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
hello,
ihave atablelike this -
z uniqueidentifier
a varchar(40)
b varchar(40)
c varchar(40)
d varchar(40)
e varchar(40)
f text
g image
I am asked to split this table into 2 filegroups and move text and images on to one and other stuff on one.
I am new to MS-SQL 7.0.
Can anyone suggest me how to go for it.
Thanking you.