Few Simple (silly) Questions On SQL 2005 && CLR Integration.
Mar 2, 2007
Hi all,
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ?
Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ?
What are the major disadvantages of using CLR stored procedures instead of T-SQL?
I am setting up a development server with SQL 2005 Dev Ed beta 2. I have been going around in circles trying to find out how to create a database diagram. Microsoft really knows how to make things difficult! Would someone mind telling me how to build a diagram of my new database?
HELP PLEASE! 1) Is it true that INSERT, UPDATE, and DELETE queries can be carried out on only ONE table at a time?2) I have a newly created database which is empty, and has four tables related with FK's. How can I begin to enter data, if i can only do an INSERT or UPDATE on one table. When i do a SELECT query, i don't even get an empty results set to modify. But i think that's an old fashion technique anyway. And if i can only INSERT VALUES into one table at a time, that violates the relational integrity. PLEASE HELP!DanThe only thing I can think of is the de-enforce the Relationships Constraint... Then, one at a time, fill each table's 1st record with fictious data, and then enforce the Relationships again, hoping that the identity columns will match up.
When I'm designing an Integration Services package, the drag and drop doesn't work. It did at one time but doesn't now. What did I change??? Driving me nuts!!!
Also, this is a simple package. I'm exporting 6 colums from a view (db --> csv). I only want the first initial from the middle name. What data flow transformation object do I use? I know it's not the data conversion because that concerns the column type.
I am a PHP/Delphi MySQL programmer for some years, now I am going to create some code in MS SQL and VB. So I downloaded and registered MS SQL 2005 Server Express Edition and some tools like SQL Server Studio Management Express, and VB 2005 Express.
As I have some knowledge of MySQL design and programming, I have installed MS software, rather without problems. Then, using Studio Management I created new database, new table in it with some four fields (int, varchars and smallint). Then from the same tool I opened table and inserted some data, and closed this application.
Next I opened Visual Basic and in Database Explorer I set up connection to my SQL database indicating the file it is stored in (path*.mdf). I ordered not to copy data to new file but to use original one.
Then in Data Sources I created new data source based on previously set connection. In the end I dragged and dropped my table from Data Sources into my new Form of my new Windows Application, nice grid with panel appeared. So I ran the app, I tried to put some data and then after trying to save (using floppy disk icon on panel) i got error: Update requires a valid UpdateCommand when passed DataRow collection with modified rows. which appears in event handler for saving in line i've indicated.
Me.Validate() Me.LudzieBindingSource.EndEdit() Me.LudzieTableAdapter.Update(Me.TestDataSet.ludzie ) ' <-- here highlights
I tried then to add to ID field identity specification, nothing changed. Then in query editor of management console I put some code: exec sp_configure 'user instances enabled', 1. Reconfigure.
As some sources mentioned. I restarted Service. Nothing Changed. So this is my problem. What to change.
I use Win XP Pro SP2.
And next one question, even more important for me is the way I connect to database. I found that in Visual Basic Creating new connection in Database Explorer I indicate the file on my local drive!!! And for example if I try to open my table alongside in Management Studio Express and in my App in VB and the error raises Cannot open user default database. Login failed. Login failed for user 'FS7120Mm227'. which is not a surprise for me as in VB i am using file which cannot be shared.
In MySQL I simply put address, port and so on and I could use my database in Delphi, PHP, and another management tools with no hassle. Is it possible in VB? Or it only can be used to exclusive using database?
Please inform me if such questions shall be directed to VB forum, I am not sure, and thank you for _any_ answer.
MS goes right way giving people great possibility to use and even sell small apps using their Express Editions of SQL and VB, but it is still too confusing for people who encouraged start using their apps and have such basic problems.
One Table 3 Columns: StudentName, ExamSubject, FinalScore
A class with 40 students Each student took the 3 exams and have scores. All student took the same 3 exams.
120 rows total.
Task One: Top Score in each subject Task Two: Top 3 student name in each subject Task Three: Top 3 total score student in the class Task Four: Avg. Score for each student
I've previously used version 7 and 2000.I've recently started to learn ASP.net. I've installedVisual Web Developer 2005 Express and this includesSQLServer 2005 in the installation. The task managerand and the list of services definitely show I've gotSQLServer running on my computer.Here's the problem: how do I actually use it? I can'tfind any tools to create and access databases.I've tried installing the Enterprise Manager from 2000but this gives me an error message saying I must use the 2005tools to connect.So, is there an Enterprise Manager (or similar) for 2005?Where do I find it?
1. Let's say I have a SqlDataSource named Fred. Say I have a string variable, call it Barney. If I call Fred.Insert(Barney) HOW Does the insert command GET the Barney value and pass it to the SQL Insers string? 2. Lets say also in this same insert, I want the user Lets say Batty, who is logged in, to have her username automatically inserted into the table along with her Barney value. What is the method to get a hold of user name that is magically floating around in the session somewhere? All said and done, I'd love for it to work like this: "INSERT INTO [BedRock] ([UserName], [Character] ) VALUES (SomethingThatResolvesToSignedInUserName, SomehtingThatResolvesToStringValueofVariable) Table BedrockUserName CharacterBettyRubble 'Bam-Bam' THANKS inadvance fro ANY HelpDan-Simple and sleepless-Harper
I've been running around in circles all afternoon trying to create one simple report using Reporting Services (with latest SP2 installed) and SharePoint 2007. To the best of my knowledge, I have everything configured correctly:
When I access http://<server>/ReportServer, I see the server name of my SharePoint site. When I click on the name of my SharePoint site, it shows me the directory structure I have created within my SharePoint Site When I drill down in the directory, I can ultimately see the forms I created in my forms library (created via InfoPath 2007).
The next step is to create one simple report from the data in one of these forms libraries and a report on all the items within a form library. I'm stuck at the first step of creating a report, namely what to enter as the Data Source and the connection string. With a SQL database this isn't an issue.
How does one create a data source that will allow reporting over SharePoint content with the setup described above? And, if you have information that is found in the SQL Books Online, please be kind and post links so others know where to find this information.
Hi guys, I have read many articles on the matter and I have probably used up all my printer's ink in doing so, however, some questions still remain. 1) What happens if I have to reboot the mirror.. security update, etc.? Obviously the session is broken during reboot, but would I have to do another backup of the principal and resync everything? 2) I know it is not best practice but at this point I have no choice, however, I wanted to get your guys feedback on having two instances of SQL2005 on my development box. One for the mirroring of the production and the second for development. The two instances would live on their own drive... not partitioned and have adequate memory and space. What would I have to look forward with this? 3) Lastly, I am still uncertain if mirroring is approved for production. Is it?
We are currently running a corporate client with Windows 2000 and .Net 1.1. We are running a number of SQL Server 2000 applications and are now thinking of upgrading to SQL Server 2005 as part of a data consolidation exercise. I am concerned on a number of points:
Can I connect to SQL Server 2005 using old ADO connectors? We have about 40 Excel VBA solutions, and we dont want to upgrade to SQL Server 2005 if we will be unable to connect to the data source. We cannot upgrade any new versions of MDAC or upgrade the .NET framework so this is a concern.
Do we need .NET 2.0 or Visual Studio 2005 to connect and work with SQL Server 2005? If so, this will be a problem as we cannot upgrade any client beyond .NET 1.1, and only have VS 2003 as a scripted application we can install for any development.
Has anyone have any experience of the KPI capabilities of SQL 2005? We are bordering on committing to a Business Objects BI platform, and having worked with BO Dashboard Manager and Performance Manager for 4 months (it was horrible), I am not relishing the prospect and would like to propose SQL 2005 as an alternative.
What is the proper syntax to return records that appear in 1 table but NOT the other table? I have 2 tables that should contain the same records(based on shipping report number), so my join will use this field. How can I only return the data where the shipping report number appears in only 1 of the tables though>?
Hello,I have been researching the use of symmetic and asymmetic encryption inSQL 2005 and I am pretty excited to give it a try. Through examples, Ican encrypt the data, but I cant figure out what to do next...What I want:1. our social security field to be encrypted so that only the person(s)that need it can decrypt it.2. prevent DBA's from decrypting the data themselfs3. Simple way to encrypt the data on the table (maybe a trigger?)I thought I would use asymmetric keys, this way I can embed the publickey into my data warehouse process to encrypt the data.I thought I would prompt the user for the private key when the reportruns, that way I wont store the key on the server.This would be a place to start.Someone in the office said that we can store the keys in Activedirectory, so maybe I could make this seemless to the user running thereport?I've found a lot of great articles that got me started, but I amneeding the next stepAny Ideas would be apprecitated!TIARoblinks to articles I have found handy:http://www.databasejournal.com/feat...int.php/3483931http://www.devx.com/dbzone/Article/29232/0/page/3http://www.sqlservercentral.com/col...rintversion.asp
I am an Oracle DBA who inherited SQL Server administration. I have been to some 2005 training and I've been supporting several DB's for a while now but I still have some nagging security questions and would appreciate some help.
1) I needed to grant execute on a specific procedure but when I drilled down, I found that it already had execute in the EFFECTIVE PERMISSIONS. I would like to know how to tell where it got this permission from. I did some digging and found that execute appears to have been granted to the schema itself. I didn't know you could do that. Would this result in the effective permission that I observed?
2) I am trying to audit the permissions on existing principles. In Management Studio I drilled down and found permissions under Security and under Server Properties. There are also more permissions under Database Properties and Security and still more assigned at the specific object level. Where can I go or what can I query to see ALL the permissions a principle has been granted across the entire server?
3) If I grant a principle CONTROL to a schema does that also automatically confer DDL rights to said schema or would additional privs be required to perform DDL?
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
I printed out several pages of documentation regarding the install of 2005. When I tried performing the installation from CD I double-click splash.hta, as indicated in "How to: Install SQL Server 2005 (Setup)". Under Install I see two options:
Server components, tools, Books Online, and samples Run the SQL Native Client Installation Wizard
The documentation indicates I should be clicking on "Run the SQL Server Installation Wizard", which is not an option. If I select "Run the SQL Native Client..." it appears to be only installing the client and does not follow the remainder of the instructions. I can't find any information on Microsoft's site on how to perform an install from CD.
I'm guessing I should be using the DVD, but has anyone else noticed the "How to" documentation is a little off and have you found any other documentation that is more accurate?
Hi pardon my ignorance but I wonder if someone could answer a few questions for me.
I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.
A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.
So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.
Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.
The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.
How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.
I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.
I suspect the problem is the way my program is going about the checking.
Should I use an SQL insert statement like??
If exists(SELECT username from users where username=@username) BEGIN RAISEERROR("Username already exists") END ELSE BEGIN INSERT etc
If so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?
Also some more general questions.
1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.
2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()
As a rule I close all my connections as soon as I'm done with them.
A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.I wish to use a variation of this, SQL_Latin1_General_CP1_CS_AIcollation, but there is no such collation returned fromfn_helpcollations(). Also, if I try to use this collation ina CREATE DATABASE stmt, SQLS yells about it.I see that there is a Latin1_General_CS_AI. What effects are therein using this collation? The SQL_* collations are SQL collations,while non-SQL_* collations are Windows collations, yes? SQLS runsonly on Windows, so am I safe in using Latin1_General_CS_AI? Whatdoes the CP1 in the SQL collation signify? Am I asking for trouble?------------------------------------Assuming that I set Latin1_General_CS_AI (or any other case-sensitivecollation) at the database level, I believe my DDL/DML for that databasealso becomes case-sensitive. How can I specify that I want ONLY my dataaccess to be case-sensitive, and not my DDL/DML? I don't want to haveto remember to type "select * from MyCamelCase" when "mycamelcase"should work.Any help appreciated.A new SQLS DBA..aj
I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?
32-bit Physical Memory = 8GB Target Server Memory = 6GB (Willing to consume) Total Server Memory = 690MB (Currently consuming) Minimum Server Memory = 2GB Maximum Server Memory = 6GB
For the 32-bit server the Target Server Memory matches Maximum Server Memory
64-bit Physical Memory = 32GB Target Server Memory = 28GB (Willing to consume) Total Server Memory = 397MB (Currently consuming) Minimum Server Memory = 4GB Maximum Server Memory = 30GB
For the 64-bit server the Target Server Memory is less then the Maximum Server Memory
Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?
Note: Please refer to my previous message to see the current VS2005 C# code I am using to create my database.
This database will be used for short term operations. In otherwords, a user will create the database, generate a lot of data that will be created using:
SqlBulkCopy bcp;
Then, there will be 'activity'... updates, queries, deletes, etc for a few days and then the user will at a minimum remove all of the data and at some future point in time, will recreate the data and do the process all over.
In otherwords, I am not concerned about backups or transaction logging or recovery or any of these 'normal' issues of using a SQL database.
That said, how do I use SMO to keep the LDF limited in size and as small as possible (assuming I can't turn of the tranaction log to begin with).
2nd question is...I read soething about being able to log ONLY the exection of a Bulk Copy operation. How do I specify that? This is my SQLBulkCopy code.
I have a db in SQL server 2000 developer edition. I am using Visual Studio 2005 standard, and also VWD Express. I would like to just access the database that is in SQL server 2000 so I can get on with developing the site, but VS2005 refuses to recognize the instance. It recognizes 2005 instances right away.The services are all running, the network protocols are enabled, uid's/pw all of that is correct. I am getting the "named pipes" error (the named pipes protocol is enabled).Any suggestions? I'd really rather just work with the db as-is in SQL 2000. But I can't get it to connect. I can connect to it if I export it to SQL 2005, (on the same development machine) but can't get the stored procs over to the 2005 db. So my web app is not finding the stored procs and won't run.Do I need to uninstall SQL 2005 from this box if I want to use SQL 2000?Thanks,--Donnie
Previously in Sql Server 2000, we would be in enterprise manager, you'ddouble click on a view, and a nice little dialog box opened with the t-sqlstatetments, there was also a check sql syntax and apply and cancel buttons.Not exactly query anaylizer, just a quick lightweight dialog box. Is thisfeature still around? Seems like I have to go into the query anaylizer likemode to edit a view now. I am a total newbie to version 2005. Are there anyoptions I can set to make it behave the old way? All feedback isappreciated.TIA,~CK
So I am fairly new to Express and I have installed it on my development machine; much tio my chagrin it is quite difficult to import data into SQLEXPRESS. I have a 'sa' account setup and I have created a new database and table within that database, however, when I try to import data into that table by setting up a link server to excel I am having some major issues!
I ran this code first to create the linked server...
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:Anchor_Hocking blactionlist.xls'
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking".
The file is not open. I have granted full access rights to all users....I am really frustrated!
Also, how can I get SSIS on this machine with SQLEXPRESS?
During last year, I developed more than 200 Reports using SQL Reporting Service. It is really fantastic and in many ways it has reduced lot of work.
But still I am not able to find out solution of following issues,
1) Where to set portrait or landscape printing mode? It takes based on paper size, if 8.5 x 11 then portrait else landscape. Now I want to set myself in report irrespective of page size then HOW TO DO IT? 2) I want to set Height, Width and Merge cells options based on Expression like many other things we do, ex. Font, Text Align, Visibility, etc€¦ HOW TO DO IT? 3) I am not able to find out option for Vertical Cell merging? Is it available or is there any trick? 4) If entire group or table can be printed within a page then it is fine but if page break occurs then I want to print Entire Group or Table on next page. In many cases you can€™t allow page break like, Pay Slip of Salary, Purchase Order Month Wise Schedules, etc€¦ So some option should be there like €œInsert Page break if table or group is not able to fit on current page€? HOW TO DO IT? 5) Is there any way to know that there is page break in this Table or Group? 6) Can I Insert Page Break based on Expression?
Theses problems really create headache. There must be some solutions.
First of all I would like to politely greet everybody as I'm new on that forum and new to Data Mining in fact.
To introduce myself I can say I'm a student of Computer Science and I'm trying to use Time Series algorithm for weather analysis. I know that forecasting weather is a hopeless task even for the fastest computers in the world but what I'm trying to do is a kind of aposteriori analysis of historical data to notice some dependencies or characteristic weather behavior on a specified region and perhaps make some short time predictions.
I tried Time Series Algorithm although I have some doubts about methodological justification of this choice (if You have any critical comments please share them with me). But my main questions are about the usage of the algorithm itself:
I've read the documentation and a tutorial on this page for historical predictions but I still don't know what exactly are HistoricalModelCount and HistoricalModelGap. I know that my historical predictions are bounded by a €“ HistoricalModelCount*HistoricalModelGap*, but it's a rather operational knowledge... The explanation is always clouded with an €œinternal model€? phrase. Can You point me to a document where I can find some more detailed information? (What is the form of the model? How is it built? etc.)
Periodicity Hint. How should I treat these optional values? Are they other possible periods of data? I have data about weather measurements made every six hours for thirteen years** so is it a good choice to set this parameter to {365*4,4} (The first goes for a year and the second for a day)?
This is a technical question and I'm really ashamed of myself that I bother You with it. On the time chart in a model Viewer I can see date from the last year only. Zooming out/in, clicking insanely on every pixel on the screen, did not give any result (apart of broken mouse buttons). Is is possible to browse that data in mining model viewer chart? Thank You in advance for Your replies!
*This formula suggests how this parameters could work but I would like to know it for sure €“ don't want to make some awful mistakes in my project. :-) **Of course I plan to reduce the amount of data but the period will stay.
The first question is how to of TimeSeries Algorithm?
Using SQL Server 2005 TimeSeries Algorithm ,I build a data mining model.But after three days,it is still training.The data has 2,200,00 rows.
So what can i do to improve the processing speed.
Thanks!
The second question is parameters in Data Mining Query Task.
Data Mining Query Task is used to get data from data mining model.In the mining model form, i choose a mining model . And in the query form,i wrote a dmx ,"select flattened top 100 predicttimeseries([Xssl],1) from [Time Series XSSL]".Last i choose a table that is for the data from mining model.
I Imported a table over to sql 2000 server(through the DTS), then I exported the table to access 2000. After creating my forms my users were trying ot add data but couldnt, they kept getting an error message saying these records are not updateable. After some research I found that I never appointed a primary key, so after doing so the records became updateable. constant learning process adn I love it :)
All: I am writing an Internet/Extranet based (ASP.Net 2.0) web application that uses SQL server 2005 as the database. I am using forms authentication on my web application. I am also storing the connection string to SQL server in my web config file. The conn string is encrypted using DPAPI with entropy. I currently have created a SQL login account on my SQL server for use by the web application. This is the user ID I am using in my conn string. The reason for this is because all persons using the application will NOT have a windows login. Here is my question: The login I created currently has defaulted to the "dbo" role and therefore has "dbo" rights to the database. I want to setup up this login account so that all it can do is execute stored procedures. I dont want this SQL login to be able to do anything else. In my application I am using stored procedures for ALL data access functions, via a data access layer in my application. Can someone guide me step by step as to how to setup this type of access for this SQL login. Thanks, Blue.
I have a webpage that displays 4000 or more records in a GridView control powered by a SqlDataSource. It's very slow. I'm reading the following article on custom paging: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx. This article uses an ObjectDataSource, and some functionality new to Sql Server 2005 to implement custom paging.There is a stored procedure called GetEmployeesSubestByDepartmentIDSorted that looks like this:ALTER PROCEDURE dbo.GetEmployeesSubsetByDepartmentIDSorted( @DepartmentID int, @sortExpression nvarchar(50), @startRowIndex int, @maximumRows int)AS IF @DepartmentID IS NULL -- If @DepartmentID is null, then we want to get all employees EXEC dbo.GetEmployeesSubsetSorted @sortExpression, @startRowIndex, @maximumRows ELSE BEGIN -- Otherwise we want to get just those employees in the specified department IF LEN(@sortExpression) = 0 SET @sortExpression = 'EmployeeID' -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment SET @startRowIndex = @startRowIndex + 1 -- Issue query DECLARE @sql nvarchar(4000) SET @sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE e.DepartmentID = ' + CONVERT(nvarchar(10), @DepartmentID) + ' ) as EmpInfo WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' -- Execute the SQL query EXEC sp_executesql @sql ENDThe part that's bold is the part I don't understand. Can someone shed some light on this for me? What is this doing and why?Diane
