Answer Questions

• Wiindows Alter table

  I have a table which has a field with datatype text , now i want to change this field to varchar. How i can do that. Thanks in advance... alter table mytable alter column mycol varchar(50) ...Show All

• Nfrf It seems a bug of SQL Server 2005.

  I do the following steps: 1:Use Manage Studio login the server with Integrated security. 2:Create a dabase named testdb; 3:Create a SQL Server login named amber ,and set it to be dbowner of testdb; 4:Create a SQL Server login named guxiaobo ,set it's default databse to be testbd,and in the testdb databse map login guxiaobo to user guxiaobo; 5:Close Manage Studio and reopen it ,this time use login amber log to the server. 6:In database testbd create a databse role role1 owned by dbo; 7:In database testdb create a schema schema1 owned by dbo; 8:Set user guxiaobo to be member of role1,and set guxiaobo's default schema to be schema1; 9:In the schema properties-schema1 dialog choose permissions,in the users or roles l ...Show All

• elvis8900 ms sql 2005 delete record problem

  I'm facing problem when delete a record in the ms sql 2005 . The error message i got is stated below: No rows were deleted A problem occured attempting to delete row 1304. Error: .Net Sql Clint Data Provider Error Message: Possible index corruption detected. Run DBCC CHECKDB. Correct the errors and atempt to delete the row again or press ESC to cancel the changes. just certain record i can not delete and face this problem. i detach the database and attached in the ms sql 2000, and select the same record to delete. there is no problem at all. i wander is it ms sql 2005 bugs can anyone tell me how to fix it .. how to to use the DBCC CHECKDB thanks alot.... P/s : if i posted at the wrong section jus ...Show All

• ismar Cannot save my Maintenance plan

  I use sa to login to sql 2005. I can save Maintenance plan using wizard, however, when I try to save maintenance plan using the maintenance plan design screen. I received error message "Apply to target server failed for job ...". When I check the job, there is no step in the job. In the Maintenance wizard, there is no maintenance clean up task. I want to create a task to delete my backup file if it is older than certain days. Any suggestions are welcome. Thanks in advance. My SSIS is installed, however, it is disabled. Will this cause the problem Hi Dont use the wizard. You can use the cleanup maintenacne task to cleanup backups files older than x days. Cheers Only ...Show All

• ANS-Denver performance: memory usage: any way to flush memory?

  Hello, we are using SQL Server 2000 on Win2k3 machine we are having different sproc's that have a structure like: create table #tmp1 @query = 'insert into #tmp select from ......' if condition1 append where clause1 if condition2 append where clause2 exec(@query) while loop @query = 'update #tmp set col1 = (complex select stmt.....)' exec(@query) select * from #tmp drop table #tmp this proc is using multiple tables, some of them having records around 50k. when this sproc is getting executed, memory usage of sqlservr increases but when the execution completes, it doesn't reduce the memory usage though we are dropping the temp table explicitly. we even tried deleting all the records of temp table, before dropping it. s ...Show All

• b182f117 mySQL to MS SQL 2005 - ODBC?

  Hi, Posted this at the tail end of another thread but that one appears to have died. Thought I'd try again since my question was a little distinct from that one anyway, but apologies if I've committed forum sacrilege. I am having a heck of a time trying to migrate a fairly simple MySQL DB to SQL Server I am using SQL Server 2005 Standard Edition SP1. I tried following MS' instructions in this article: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx hoping that what worked for 2000 would work for 2005, since the principles seem sound. No luck. I can create the ODBC link, but SQL Management Studio, the '.NET provider for ODBC' option doesn't give me the choice to copy tables - only to write a query. ...Show All

• Alexnaldo Santos Install of 2005 tools fails

  I'm trying to install SQL Server 2005 tools, but keep getting this error, TITLE: Microsoft SQL Server Setup ------------------------------ None of the selected features can be installed or upgraded. Setup cannot proceed since no effective change is being made to the machine. To continue, click Back and then select features to install. To exit SQL Server Setup, click Cancel. For help, click: http://go.microsoft.com/fwlink LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=SQLSetup90&EvtType=28108 Then on the Details, it says the following, Name: Microsoft SQL Server 2005 Tools Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and ...Show All

• beto81 Package variables

  Couldn't quite find the answer I was looking for via a forum search. I have 9 packages that are currently called by a master package - all child packages take a datetim e variable from the master package which is subsequently used in the child processes. The question I have is that if I run the master package manually from Visual Studio I can set the master package variable manually to a date of my choosing and run it, which works fine. However we will be wishing to automate the package execution at some point and want to know the best way to run the package(s) on an automated basis and have the data variable supplied to the master package. What would be the best way to do this Presently we have a variable called MasterPackage ...Show All

• DamonPeng Erasing digit

  Hello. I have a column in my database which is holding phone number. The problem is that although I gave the number starting with zero but it will erase it automatically. So can you help me to solve this problem Thank you ok thank you for you help KHodam wrote: Hello. I have a column in my database which is holding phone number. The problem is that although I gave the number starting with zero but it will erase it automatically. So can you help me to solve this problem Thank you As Paul alluded to, you aren't storing the phone number as an integer, are you Store it as a varchar and make sure that inside your data flows that you aren't converting the ...Show All

• minority80 Assign expression value using code

  I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated. Thank you in advance. All task properties support expressions, it is only Data Flow components that require the developer to actually set some code that says an expression is supported. The problem is that the result of an expression cannot be greater than 4000 char ...Show All

• Fille RSS Text Data Mining

  Hi, Somewhere in the literature surrounding SQL Server 2005 DM, I saw a reference to a project that collected and mined RSS data. Does such a project exist Thanks, Bob Various people in the group (including myself) have played with such projects, but haven't published anything. (For one thing, all the RSS content is copywrited by the RSS provider, which makes it difficult). In any case, the methodology is fairly simple - use Integration Services to read the RSS feed and pump through the text mining transforms, and then use the results in data mining, OLAP, or reporting. The specifics of the latter part depend on the problem you are trying to solve and the information you have. For examp ...Show All

• satishpkumarin Performance problem: 2000 vs. 2005

  I copied a 2000 database onto 2005 on another box. Running the exact same SELECT query, with 7 tables joined, is running in less than 2 minutes on 2000 and almost 3 hours on 2005. The 2005 box is "bigger and better". I've compared the sp_configure output. There are differences in max worker threads, cost threshold for parallelism, and priority boost. I don't think any one of these is significant. I checked the server properties and the database properties and, again, I did not see what I would consider any significant differences. The 2005 instance has 4GB memory, as opposed to 2GB, but not using that much. In fact, the Total Server Memory (KB) for the instance indicates around 1,230,000 for some reason. We need to reso ...Show All

• markand SQL Agent Roles

  I have a domain account that is assigned the the SQLAgentOperator, SQLAgentReader, and SQLAgentUser roles. That domain account is able to create a job and delete the job. But, it is unable to edit the job. If I create a SQL Login with these same permissions, it is able to edit the job. What am I missing We were looking forward to implementing these new built-in security roles because we had to use our own security roles to give us similar functionality in SQL 2000 but it doesn't seem to be working properly in 2005. Michelle This server is SQL Server 9.0.2153 Enterprise Edition. We have another one that is SQL Server 9.0.2047 Standard Edition. Both are 64-bit. I think that the 9.0.2153 h ...Show All

• Tryin2Bgood Cannot create an instance of the XML Document Object Model. MSXML may not be registered

  I have a small VB.Net 2005 app using the Microsoft.SQLServer.ManagedDTS.dll so I can use the SSIS/DTSx functionality. The program ran fine in the IDE. Then when I tried to create a setup project for it I recieved a warning that all dependancies for MSXML6.DLL were not found. I clicked ok and the setup was created. I tried to run the setup and it failed with "....\msxml6.dll failed to register. HRESULT -2147023782. ... So I said cancel the setup and registered msxml6.dll. I deleted my setup project and recreated it. This time I did not get the warning about msxml6.dll dependancies. I tried the setup again and it executed without any errors however when I run the program I now get: "Cannot create an ins ...Show All

• Jim Perry Printing

  If I've missed something, please tell me. I'm trying to print control and data flows out of SSIS for documentation purposes. What I'm missing seems to be print preview and an option to print-to-fit. As a result, I've created a feedback item: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=238047 ...Show All