Month/Year comparison against a date Hi,
Is there a way to get the last day of a month, given a date in a datetime variable
I have a stored procedure that accepts a datetime parameter. I need to find the last day of the month for that parameter value. For example, if the stored procedure is passed the datetime value, '6/13/2007', I need to be able to get from that '6/30/2007.'
Here's the bigger picture: The sp actually takes two datetime parameters (unfortunately, I don't have access to change the user interface). The sp needs to select records between the month/years of those dates, not including the first but including the second. For example, if the user specifies the following dates:
the sp needs to select all records that come after 6/30/2006 and on or before 7/31/2007.
I've tried this: ("ReportDate" is the name of the datetime field in a table in the sp and "@StartDate" and "@EndDate" are the datetime parameters in the sp)
Month(ReportDate) > Month(@StartDate) And Year(ReportDate) >= Year(@StartDate) And Month(ReportDate) <= Month(@EndDate) And Year(ReportDate) <= Year(@EndDate)
But this returns fewer records than when I enter 6/30/2006 and 7/31/2007 as the parameters and just compare dates like this:
ReportDate > @StartDate And ReportDate <= @EndDate
Thank you. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
VB6 RDO error I've been given a legacy app that I have to change to point to a Sequel 2005 database (from a sequel 6.5 database).
The app falls over when trying to execute a stored proc.
The message I get is:
Run-time error '40041':
Object collection: Couldn't find item indicated by text
The above message is displayed when the following line is executed:
qryStoredProc(0).Direction = rdParamReturnValue
I have reinstalled service pack 6 for vb6 but this hasn't made any difference.
Unfortunately due to build difficulties I can't point to the existing sequel 6.5 database from the machine that is pointing to sequel 2005 !!
So I'm not sure if this is due to the version of sequel or a problem with my developer machine build.
Anybody got any ideas
Thanks in advance. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Transaction Question When I run the following in Query Analyzer:
SET QUOTED_IDENTIFIER OFF -- allows double quotes (") used as wrapper for dynamic sql. DECLARE @E int BEGIN TRAN T1 PRINT 'Start...' EXEC ( "
begin tran T2| raiserror( ' inside proc', 16, 11) PRINT STR(@@ERROR) rollback tran T2 PRINT STR(@@ERROR) return
SET @E = @@ERROR IF @E <> 0
PRINT 'Error ' + LTRIM ( STR ( @E )) + ' out of here...'
ROLLBACK TRAN T1
COMMIT TRAN T1
I get the following output and error: Start... Msg 50000, Level 16, State 11, Line 3 inside proc 50000 Msg 6401, Level 16, State 1, Line 5 Cannot roll back T2. No transaction or savepoint of that name was found. 6401 Msg 266, Level 16, State 2, Line 7 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2. Error 266 out of here...
Anyone have an idea why I would get the 6401 and 266 error
JD Waleska, GA MSBassSingerNO-SPAM@alltel.NO-SPAMnet (just remove the string NO-SPAM) Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Unable to install SQL Server CE Server Tool on SQL Server 2000 SP 4 Hi,
I have installed " SQL Server 2000 SP 4" to SQL server and it is running fine.
But when i install the " SQL Server CE version 2.0 Update for use with IIS and SQL Server 2000 SP4 ", i get below error:
"SQL Server CE Tool is compatible only with SQL Server 2000 Service pack 1 and higher. When you install SQL Server CE Server Tools on a computer running both IIS and SQL Server 2000 , ensure the installer for SQL Server CE Server Tools corresponds To the version of SQL Server 2000."
I have go thougth few articles in the forum, try to open the sqlce20sql2ksp4.exe file and then run only the MSI, but still fail.
By the way, i found that the sqlce20sql2ksp4.exe does not include any MSI, it is a direct executable file.
Appreciate if anyone tell me the solution.
Thanks in advance.
Plane. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Integrating SSRS with report viewer Your words made me confused. Just ask you some questions about your issue 1. Are you using web page which include Report Viewer Component to show RS Report 2. If you use your own web page, you need do programe code to call web serrvice in youe asp.net. 3. The report viewer is a web component which is used for showing RS Report in web form. Suggest you do research on MSDN and you will find a lot of samples of RS Web Service. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
successful implementation of dataware house and data mining hi all
i want an article on a company or organization that has successfully implemented dataware house and data mining,
and by its help they have been able to increase their business or sale etc etc help will really be appreciated Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Aggregation wizard I changed my data source tables to views which effectively reduced the number of rows being loaded into dimension to only those items that appear in the fact table. However, in the Aggregation Wizard, when I request a row count, it appears that the wizard is not looking at the view, but the original table.
Is this possible
Sully Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Monitor network resources used by single database Hi,
Is there any methord to monitor network utilization per database on single instance of SQLServer2005 or SQLServer2000.
Mohd Sufian Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Drop database on a different SQL Server. Hi,
I am new to SQL Server 2005.Till now, I have been using a SP to execute DROP DATABASE command to drop databases on my existing database server.
but now i want to delete a database which is on a different SQL Server 2005 instance on a different machine. but i am not sure how to do this.
Can anyone please help me on this
Any help would be appreciated.
Thanx in advance.
Kawal Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
replicating UDT's to subscribers I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.
would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
XQUERY / XPATH versus nodes containing ":"-character Hi, all.
I'm having problems using XPATH / XQUERY versus input containing ":" in the node names.
<MyData:Output xmlns:MyData=" www.myprovider .com">
<MyData:Provider> <MyData:Property MyData:Name="FirstName">Joe</MyData:Property> </MyData:Provider> </MyData:Output>
Code Snippet SELECT XMLContents.query('/MyData:Output/MyData:Provider') FROM dbo.Some_Table __________________________________________________________ XQuery [dbo.Some_Table.XMLContents.query()]: The name "MyData" does not denote a namespace.
However, if I remove all the "MyData:" references from code and data, it works fine.
I don't have an option of getting the incoming data changed; is there a way to escape the character to get this code to work Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
SQL Server vs. XML Configurations Sam,
Configurations based on a SQL Server table work just fine. But there is a slight difference when compared against XML files. As you are telling the package to go to a SQL server table and 'grab' some configuration values; you also would need to provide the connection information to get to that table right . That is why, when creating a SQL Server based configuration the wizard ask you to provide the configuration settings directly (using an existing connection manager in the package) or indirectly (using an Environment variable). It seems to me like you are using the direct method; if so, you need to make sure that connection manager is properly configured at run time (basically it can get connected).
BTW, the password needs to be specified in either of SQL Server or XML file configurations; so yes, you need to add it after creating the configuration. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
converting to numeric (6,3) Hey guys,
I am trying to achive the following:
Truncate the values 1680.390000000000000000
to numeric (6,3)
All works ok until i hit a value over 1000, (as above)
The code i was using is:
cos1 = 1680.390000000000000000000
CONVERT (NUMERIC(6 , 3), ROUND(COS1, 3))
I have tried a few others, but nothing seems to work..
I get the following error. "Arithmatic Overflow"
Has anyone got any suggestions
Scotty Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Datasource Views and Teradata I'm trying to to use SSAS 05 w/ our Teradata warehouse (VERSION 05.01.02.70 RELEASE V2R.05.01.02.40) and I'm having a problem creating datasource views.
My issue is that the wizard returns every view and table in the warehouse (tens of thousands) which takes over 10 minutes . I can't seem to limit it to only the database I'm intersted in using. I've tried using the restrict schema option but then no objects are returned. Any ideas on how to do this Can you create the datasource views without the wizard
Thanks. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Understanding code created by SSIS import/export Wizard The first package is created when you create a new Project. So it is probably empty and does not contain anything. You may probably delete it. The second package should contain a data flow task that contains most of the package logic. Don't try to edit XML - it is somewhat useful for experienced user or for advanced troubleshooting, but most of the editing should be done in the GUI designer. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Parameterized Queries Just getting started using SSce and having a few problems
What I want to do is something like this...
Dim Code As Integer
Dim Description As String = txtDescription.Text.Trim
Dim conn As SqlCeConnection = ConnectToLocalDatabase()
Dim ssql As New System.Text.StringBuilder
ssql.AppendLine( "INSERT INTO T_Titles (Description)" )
ssql.AppendLine( "VALUES(@Description)" )
ssql.AppendLine( "SELECT @Code = @@IDENTITY" )
Dim cmd As New SqlCeCommand(ssql.ToString, conn)
Dim sqlCode As New SqlCeParameter( "@Code" , 0)
sqlCode.Direction = ParameterDirection.InputOutput
cmd.Parameters.Add( New SqlCeParameter( "@Description" , Description))
Code = CInt (sqlCode.Value)
The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.
I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.
Thanks Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Users and Roles script In 2005, is there a way to automate this I'd like to be able to do this automatically periodically:
1) Script out the development users and their roles in my development database
2) Backup the production database and restore the it to the development database
3) Re-synch the existing users (sp_change_users_login), drop any obsolete users (sp_dropuser), and add any development users with roles back on
Does anyone have a script that can help me achieve this Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
how to extract this text? I have options_field contains a lot of data.. here are examples:
DT，NB，NM，ANI=80088080 39139662 ANI=80088080 36637576 ，DNIS=80088080，DT，NB，NM，UNIT ANI=80088080 39188390 ，ANI=8008808017403125，DNIS=80088080，DT，NB，NM，UNIT ANI=80088080 39653135 ，ALIAS，DNIS=80088080，DT，NB，NM，UNIT ALIAS，ANI=80088080 39169467 ，ANI=8008808017326875，UNIT，NM，NB，DT，DNIS=80088080 DT，NB，NM，DNIS=80088080，ANI=80088080 36331200 ，ANI=80088080 39318646
I want to extract every 39 and 36 mobile numbers in the ANI. All numbers are 8digits as highlighted ad some records will have more than mobile number while some won't have any mobile number listed. so the result should be
39139662 36637576 39188390 39653135 39169467 36331200 - 39318646 Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Daylight Saving Time DST 2007 Problems If you go to a cmd prompt and type "time" and open a query window and run "SELECT GETDATE()" Do you get the same hour When was the last time you rebooted the server It use to be, I don't know if it still works this way, Windows would fudge the Windows clock after DST and would reset the RTC clock when the computer rebooted. So your RTC could say 1:13pm and Windows would report 2:13pm, until you rebooted. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Unicode causes Dimension build failure? I ran the select distinct statement as you suggested, in SQL Management Studio as a TSQL statement. The query completed successfully, no error messages were returned. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Change member properties order in excel 2007 pivot table Hi everyone,
i am trying to change the member properties order in a excel 2007 pivot table but it wont work... I've found how i should do it according to Microsoft Office web site ( http://office.microsoft.com/en-us/excel/HA101777521033.aspx , see : Display member properties as data in a PivotTable report ) but even if i follow the instruction by the letter it is not working... I've searched a lot but i can't find anything on this...
Anybody's having the same issues or know a way around it
I'm using SSAS 2005 SP2 and Excel 2007
Thank you very much! Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
SQL Server 2005 Limitation: Having many attributes for Neural Network Hello, Say that I have 100,000 attributes/feature selections for my SQL Server Neural Network Algorithm. Customer Attr1 Attr2 Attr3 ..... Atr100000 ============================== Jack 1 0 1 ..... 1 Sam 0 1 1 ...... 0 Mary 1 1 0 ...... 1 Knowing the fact I can't fit those info on a table and SQL Server's Neural Network does not support table prediction . What's an alternative to use Neural Network in SQL Server 2005 to solve my problem Please assist! Mary Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
readonly custom property Well, for now I would do what Jay suggested.
Feel free to open a request for adding this feature on the connect site.
Thanks. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Label in parameter Hello!
Is it possible to create a row of "labels" in a multi-value parameter list
i mean something like this:
//C Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
SSIS performance Hi,
I have 4 execute packages say A, B, C, D. These packges contains packages for data transfer in turn.
Package A - 15 packages
Package B - 15 packages
Package C - 20 packages
Package D - 20 packages
When i run these packages one at a time in the order A, B, C, D then the execution time is around 17 mins.
If I make a parent package and put A, B, C, D in sequence in it, executing this parent package increases the execution time to arnd 50 mins.
Pacakge A, B, C, D doesnot run in parallel. They run one at a time. So i was wondering why the there is so much time difference.
Please let me know if theres some configuration settings to make the parent package efficient.
All the test conditions are same in both the cases. The source and the target are SQL server which are on the same machine as the SSIS.
Vipul Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
You don't need the Notes client installed on the Server at all as you will be using a POP3 email account to connect to the Domino Server (at least that is the way that I run it.)
Sign back on as the SQL Server service account then launch Outlook 2000. Click Tools, Options, click the Mail Services Tab, then click Reconfigure Mail Support and select the Corporate Workgroup option. You may have to setup the mail profile/accounts again if needed (choose POP3 when creating the mail accounts). Be sure to set Outlook to use each profile that you created and launch Outlook and click send/receive to test the email accounts.) Then logoff and sign back in as the normal Server user then restart SQL Server again... Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Problem in Configuring ASPState DB for .NET 2.0 Hi,
In the current production environment we have web applications running in asp.net 1.1 (win 2003 server) which uses ASPState DB for the session management. We are in the process of migrating few of the web application in asp.net 2.0. We would like to use the same ASPState DB for both 1.1 and 2.0 applications for maintaining the session.
Problem that we are facing: After going through couple of article for configuring the ASPState DB, We ran the InstallPersistSqlState.sql of .net 2.0 framework (log information is given below). Though the sql has updated the existing ASPState DB with the stored procedure corresponding to asp.net 2.0, still we get the following error
Exception: System.Web.HttpException Message: Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.
For certain reasons, we are not able to use aspnetreg_sql.exe for setting up the ASPState database. Is there something that I am missing when I run InstallPersistSqlState.sql alone.
Appreciate your help.
PS: We ran the InstallPersistSqlState.sql in the local sqlserver2000 (residing in winxp) and it works fine.
Run 'aspnet_regsql.exe - ' for details. -------------------------------------------------- --------------------------------------------------- Completed execution of UninstallPersistSqlState.SQL --------------------------------------------------- ------------------------------------------------ Starting execution of InstallPersistSqlState.SQL ------------------------------------------------ -------------------------------------------------- Note: This file is included for backward compatibility only. You should use aspnet_regsql.exe to install and uninstall SQL session state. Run 'aspnet_regsql.exe - ' for details. -------------------------------------------------- The CREATE DATABASE process is allocating 0.63 MB on disk 'ASPState'. The CREATE DATABASE process is allocating 0.49 MB on disk 'ASPState_log'. If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61 The specified @job_name ('ASPState_Job_DeleteExpiredSessions') does not exist. (1 row(s) affected) Type added. (1 row(s) affected) Type added. (1 row(s) affected) Type added. (1 row(s) affected) Type added. (1 row(s) affected) Type added. If the category already exists, an error from msdb.dbo.sp_add_category is expected. Server: Msg 14261, Level 16, State 1, Procedure sp_add_category, Line 32 The specified @name ('[Uncategorized (Local)]') already exists. ------------------------------------------------- Completed execution of InstallPersistSqlState.SQL ------------------------------------------------- Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Restoring Differentials from 2000 to 2005 From what I understand is that you want to have a reporting database that is an exact copy of your production database so the reports will not impact the performance of the production server.
If this is the case, since you can't do log shipping (My reccommended choice in this scenario) between versions (That I am aware of anyway), my second choice would be to set up replication. This can be done between 2000 & 2005 and will provide for almot real-time data synchronization.
If, for some reason, you can't use replication, such as very complex transformations to the data, the next choice would be a tool like SSIS. You copy all of the data to the report server 1 time, then every hour, you run an SSIS package that copies only the changed data to the reporting database. How you identify the data that has changed since the last run depends on the data source. Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Drive allocation Any comments, please. I have a new server 5x160GB drives. It needs to run IIS for a web app and SQL Server 2005. One user database. I was thinking of dividing space as follows and wanted to get some thoughts from others.
2 drives = RAID 1 set, 160GB space, OS, IIS, SQL exe, tempDB, user database log file
3 drives = RAID 5 set, 320GB space, SQL data files, user database files
Thanks for looking... Tag: SQL Server 2005 Service Pack 2 - RTM - WHEN???
Does anybody know how many CTPs will be provided before the RTM will be delivered