I'm working in a team, and need to share a database between the group - we don't have the luxury of sharing a server and connecting to it.
So What we'd like to do is make a copy of the database schema, and then share that through our repository.
I figured I just need to copy the .mdf file from the sql server database folder, and put it in the visual studio directory to work on it?
So I've tried this, but when I try to create a TableAdapter to the database through visual studio, It gives me an "access denied". I figure this is probably something to do with security settings?
Do you think I'll need to change my Database connection from windows authentication to sql authentication?
I don't really know exactly how to do what I'm trying to do, so any help will be appreciated.
Team members appear twice or more if they belong to more than one team. I need to be able to show their name and main team. Team is not important at the moment but I just like to include all team members and display a team name against them.This is what I have at the moment:
SELECT SystemUser.systemuserid, FullName, TeamMembership.TeamID, TeamName FROM Team RIGHT OUTER JOIN TeamMembership ON Team.teamid = TeamMembership.teamid LEFT OUTER JOIN SystemUser ON TeamMembership.systemuserid = SystemUser.systemuserid order by FullName
at work all the databases i use are only accessible via the local network, i am wanting to take a copy of the databases and save them as a database file so i can burn them onto cd and take home with me so i can get some extra work done on the weekend (i'd also like to start doing this so i have backed up copies of the database incase anything happens to it)
i was wondering how i can do this? are there any tutorials on the web that someone can point me to? i have SQL Server 2005 Management Studio Express.
I am creating a program that will take a master database and create separate databases for class room training.creating my own app to do this since it will have other stuff to do.i will have a master database that i will need to create multiple copies of. 2-20 copies, it is about 7GB large. it is used in a classroom training course for our company software. it will also copy a folder on the server onto multiple subfolders.each computer in the classroom will access its own copy of the database/windows folders.
What i am looking for is a fast/reliable way to create the multiple database copies. then when the training class is over and a new one is getting started, we will run my program to reset everything back to start.Should i detach/copy/attach or create a master backup and restore it 20 times. What kind of user access pitfalls will i need to look out for.
I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.
Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.
Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.
What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.
since the last upgrade SQL 2000 --> 2005, the biggest database I am working on (1.4 GB, with loads of binary data) keeps freezin at least one time per week. When I say 'freezin' I mean that you cannot query some of the tables as it gives a timeout error when trying.
I am not a DBA and I don't have any idea of what to do, the only thing which comes to my mind is that this database is too big, as if I go to the preperties window it says Space Available: 38 MB (what's that? there's really only 38 MB on this DB?). I tried re-writing all mainteinment plans (full backup every night, log backup every hour), but I realized that the database keeps freezing (and the backup jobs keep running untill you stop them).
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states. Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL; SCOPE (Measures.[weighted measure state and above], Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE), Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE), ... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
Hello!Is it possible to pass a stored procedure a parameter, say @table anduse it as a table in the sql command?Finally i want a proc to copy tables from a database to another database.THERE IS MY CODE:CREATE PROCEDURE [user].[copytable]@dbSRC varchar(100),@dbTRGT varchar(100),@table varchar(100)ASBEGIN TRANSACTION FreeAndCopyTableTRUNCATE TABLE [@dbTRGT].admin.[@table]INSERT INTO [@dbTRGT].admin.[@table]SELECT *FROM [@dbSRC].admin.[@table]COMMIT TRANSACTION FreeAndCopyTableIF @@error <0 GOTO E_Copy_FailE_Copy_Fail:ROLLBACK TRANSACTION FreeAndCopyTableGOthanks in advance,muh
I am getting some problem in using group in sql server login.
i have two NT groups in windows.i) developer and tester
I added a user Lalit in both gruop. I want to give db_owner permission on a particular database(Employee) to developer group and data_denyreader and deny_writer on same database(Employee) to tester group.
Now when i logging in to sql server using Lalit(windows authentication) iam not able to access the database(Employee).
Now my concern is that Lalit should have full access to the employee database when entered as developer user and limited access to employee database when entered as tester user.
Sorry for the simple question, but I'm not having any luck getting this to work: I need to give someone read-only access to everything in a database.
If someone could help me by giving me (or linking me to) a step-by-step, idiot-proof recipe for giving Bob in my office read-only access to the whole database, I'd be very grateful. And remember: I'm good at db programming, but completely ignorant on the subject of db admin.
Created a maintenance plan to backup my sharepoint databases. When I execute it the following error occurs: Execution failed. See the maintenance plan and SQL Server Agent job history logs for details: Additional Information: Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI) - Execute maintenance plan. SharePointBackUP (Error) Messages * Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
When checking the Maintenance PlansharePointBackUP log it is empty! Under Job History I thinks this: Date 4/18/2008 12:55:35 PM Log Job History (SharePointBackUP.Backup_SharePoint) Step ID 1 Server DESD7 Job Name SharePointBackUP.Backup_SharePoint Step Name Backup_SharePoint Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed. line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error. Under SQL Agent there are no entries! So what bloody log am I suppose to be checking?! This is very frustrating. I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
I have 2 copies of the SP i work with. An original one and a backup. I usually work with the backup. Is there any way to update the original after updating the backup?. I usually forget what I have changed in the end of the day.
I'm getting 3 copies of the result set expected, could someone take a look and tell me why. I know I covered this in school, but I can't remember the issue. Thanks
SELECT P.Quantity as Qty, P.ItemID, P.VendorCode, P.Descr as Description, P.UnitPrice as Price, P.Amount, I.Freight, Rcvd = 0, I.QtyRcvd as Ship FROM PurchaseOrderItems P, ReceivedItems I, Received R WHERE P.POID = R.POID AND R.IntRcdID = I.PRID AND P.POID = 193
I work for a Geographic Information Systems contractor and we are new to SQL 2005. One of our clients sent us a copy of an .mdf file. I have tried to attach the file but they did not send us the associated log file. What is the most efficient way for us to exchange copies of SQL 2005 databases with clients. Most of our clients are novices with SQL 2005 also.
In previous threads I saw that in a scenario where Log Shipping is active any other Log Backup activities should be avoided in order to let the Log Chain stay intanct. Until now we just used Mirroring and Full Backup and Log Backup. Introducing Log Shipping to a third Server in a separate location would mean that the existing Log Backup Jobs must be removed or Log Shipping will not work.
I am glad to have read these threads before falling into that trap myself.
A question was raised by my colleague who is responsible for the System and Network Administration. Are these Log Backups that will be performed by Log Shipping just differential? He would not like to see that Log Shipping is pushing or pulling Log's that grow during time.
I named to him two reasons that it must be differential. A Log Backup truncates the existing Log which implies that the next Log is just the difference since the last Backup. Also, it must be differential because the Transations can only be committed once anyway, so Log Shipping implies that those Log Backups are just as big as the incoming Transactions since the last Log Backup.
I'm using CLR integration on SQL Server 2005 to call functions from a COM dll made with VB6. I do this to keep some important parts in packed unmanaged code, to make reverse engineering harder.
I keep the COM dll in the system directory. When it's referenced from a .net project, VS copies and uses it as 'interop.myCOM.dll'. When I add the assembly to SQL Server, it needs the referenced 'interop.myCOM.dll' file added. When a class from the COM dll is called by a CLR stored procedure, it needs to locate the real COM dll.
So, the same dll has to be in two different places in two different forms, one as an assembly (interop), and one in its own place. I guess the 'interop' one is not really used, but it just helps to retrieve class information during design time and initialization or something. Everything works ok, but thinking about the packing step and others, this makes things messy. How can I prevent using two copies of my COM dll?
I load the COM class as follows:
Code Snippet
Private Shared ReadOnly m_myClass As myCOM.clsGeneric = New myCOM.clsGeneric
then map stored procedures (shared functions) to related functions of the m_myClass object.
is it ok to run VSS (shadow copies) on an SQL server?
I have IIS and SQL on the same server. I would like to make shadow copies of my IIS files, but don't want to do anything that could corrupt my SQL databases.
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
there are two developers in different offices and they have started with the same db and keep modifying the stored procedures. in a point they want to update their copies of the DB with each other. Is there a easy way to do this in MS Sql server 2005.
there are two developers in different offices and they have started with the same db and keep modifying the stored procedures. in a point they want to update their copies of the DB with each other. Is there a easy way to do this in MS Sql server 2005.
Wondering how this is handled by Multicast component. If multiple copies are created in the memory and the size of the dataset is large, this could cause some performance problems. Any thoughts? TIA
I want to write a batch file that will do just that. The problem is bcp or bulkcopy never works with my code, it doesn't recognize it. Maybe I'm going the wrong way to do this, but I could use some help. Ideally I'll make the batch to do the importing via bulkcopy or something like it of a text file into a table and use windows scheduler to automate it. Be as specific as you can please, I'm very new to sql server.
Is it possible to group by "Team" in the following SQL Datasource? Each row has a numbers in their respective columns and need to be added up and only one row per team is shown showing the total? SELECT HomeTeam AS Team, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL) UNION ALL SELECT AwayTeam AS Team, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost, AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg, CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts FROM tblFixtures AS tblFixtures_1 WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
I am trying to setup a shape, shape attributes and calculate the cross sectional area using the formula specified in the tbShapes.Formula field. See the code below.
Now I need to calculate the expression above, but the expression is a varchar string.
Any help?
USE NORTHWIND GO
SET NOCOUNT ON CREATE TABLE [dbo].[tbProductCodes] ( [ProductCode] [int] NOT NULL , [fkAccountID] [int] NOT NULL , [Product] [varchar] (50) NOT NULL , [fkShapeID] [int] NOT NULL ) ON [PRIMARY] GO
INSERT INTO tbProductCodes (ProductCode, fkAccountID, Product, fkShapeID) SELECT 2001, 1, 'New Product', 1 GO
CREATE TABLE [dbo].[tbProductTemplateAttributeValues] ( [fkTemplateID] [int] NOT NULL , [fkAttributeID] [int] NOT NULL , [AttributeValue] [float] NOT NULL ) ON [PRIMARY] GO
INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue) SELECT 1, 1, 108 UNION ALL SELECT 1, 2, 36 UNION ALL SELECT 1, 3, 4 UNION ALL SELECT 1, 4, 5 UNION ALL SELECT 1, 5, 2 GO
CREATE TABLE [dbo].[tbProductTemplates] ( [TemplateID] [int] NOT NULL , [fkProductCode] [int] NOT NULL , [Template] [varchar] (50) NOT NULL , [fkMixID] [int] NULL ) ON [PRIMARY] GO
INSERT INTO tbProductTemplates (TemplateID, fkProductCode, Template, fkMixID) SELECT 1, 2001, 'ProductTemplate', 1 GO
CREATE TABLE [dbo].[tbShapeAttributes] ( [AttributeID] [int] NOT NULL , [fkShapeID] [int] NOT NULL , [Attribute] [varchar] (50) NOT NULL ) ON [PRIMARY] GO
INSERT tbShapeAttributes (AttributeID, fkShapeID, Attribute) SELECT 1, 1, 'Width' UNION ALL SELECT 2, 1, 'Height' UNION ALL SELECT 3, 1, 'Flange' UNION ALL SELECT 4, 1, 'Leg' UNION ALL SELECT 5, 1, 'Count' GO
CREATE TABLE [dbo].[tbShapes] ( [ShapeID] [int] NOT NULL , [Shape] [varchar] (50) NOT NULL , [Formula] [varchar] (100) NULL ) ON [PRIMARY] GO
-- Get the formula for the templates shape SELECT @cResult = s.Formula FROM tbShapes AS s INNER JOIN tbProductCodes AS pc ON s.ShapeID = pc.fkShapeID INNER JOIN tbProductTemplates AS pt ON pc.ProductCode = pt.fkProductCode WHERE pt.TemplateID = @iTemplate
SELECT @cResult AS Formula
DECLARE AttributeCursor CURSOR FOR SELECT sa.Attribute, av.AttributeValue FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa ON av.fkAttributeID = sa.AttributeID WHERE av.fkTemplateID = @iTemplate
OPEN AttributeCursor FETCH NEXT FROM AttributeCursor INTO @cAttribute, @fAttribute while(@@FETCH_STATUS = 0) BEGIN SELECT @cResult = REPLACE(@cResult, @cAttribute, CAST(@fAttribute AS VarChar)) FETCH NEXT FROM AttributeCursor INTO @cAttribute, @fAttribute END
SELECT @cResult AS NewFormula
CLOSE AttributeCursor DEALLOCATE AttributeCursor GO
-- Test stored proc
declare @iTemplate int, @fResult float
SET @iTemplate = 1 EXECUTE usp_shapes_GetCrossSection @iTemplate, @fResult OUTPUT SELECT @fResult AS Result GO
drop table [dbo].[tbProductCodes] GO
drop table [dbo].[tbProductTemplateAttributeValues] GO
Hi, Just a quick note for all that the SSIS team has started to post fresh content for download.
We will be adding some links to various pages so you can easily see things when looking at the the SSIS portal on MSDN http://msdn.microsoft.com/SQL/bi/integration/default.aspx
For now if you search for "ssis" on the microsoft downloads site you will find the current content. Sample Logging reports, jump start training, Meta Data info, and sample components. More coming over the next few weeks... http://www.microsoft.com/downloads/search.aspx?displaylang=en
Thank you and I hope everyone enjoys winding down 2005, or blowing it out, depending on your likes :)
In the past I wrote DTS transforms entirely by myself. With SSIS, our team of several developers now wants each member to develop a piece of the same package.
Do SSIS packages support this type of simultaneous multi-developer creation or is it a "one developer at a time" type product?
This may not be a MSSQL-specific question, butI wanted to ask it here first, in case there'sa MSSQL and/or SourceSafe solution that will help.Our dev team is having some difficulty withkeeping the nightly builds in sync with thestored proc mods. I'm wondering if there aresome good case studies on how to avoid this"drift". Something like genning a new DB fromchecked-in SPs, etc. alongside each regular build,then always have a paired enterprise app/databaseduo that is tagged and added to a history.FWIW, we have a 3-tier .NET/C# app, andADO.NET is throwing exceptions every otherday.If the suggestion is to whip the DB guys, thatworks for me as well. ;-)Nah, there's much love there.Thanks in advance,~swooz
i am a beginner of database design, could anyone please help me tofigure out how to make these two tables work.1) a "players" table, with columns "name", "age"2) a "teams" table, which can have one OR two player(s)a team also has a column "level", which may have values "A", "B",or "C"how do you build the "teams" table (the critical question is "do ineed to create two fields" for the maximum two possible players?")how do you use one query to display the information with the followingcolumns:"name", "age", "levelA", "levelB", "levelC" (the later three columnsare integer type, showing how many teams with coresponding level thisplayer is in).now suppose i don't have any access to sql server, i save the datainto xml, and load it into a dataset. how could you do the selectionwithin the dataset? or ahead of that, how do you specify the relationsbetween "players" and "teams".the following is the schema file i am trying to make (I still don'tknow if i need to specified the primary key... and how to buildrelation between them):<code><?xml version="1.0" ?><xs:schema id="AllTables" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="AllTables" msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Players"><xs:complexType><xs:sequence><xs:element name="PlayerID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Name" type="xs:string" minOccurs="0" /><xs:element name="Age" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element><xs:element name="Teams"><xs:complexType><xs:sequence><xs:element name="TeamID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Level" type="xs:string" minOccurs="0" /><xs:element name="Player1" type="xs:int" minOccurs="0" /><xs:element name="Player2" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema></code>
I have installed SP2 on my report server and configured for Sharepoint access.
This is all ok.
I am able to use the webpart to create a report view but it wants the report. Is there a way to manage the report server (they way I did with reportmanager) in sharepoint. If so I have not found out how.
any ideas or links to show how I can have teh reports listed in Sharepoint?
I would like to setup SSIS project so that multiple developers can work on same project. I am having issues with protectionlevel properties while another developer opens the package created by other developer.
Can anyone guide me on setting up project so that multiple developers could open the package and run (not simaltaneously though). Also tips on setting up source safe or team foundation will be appreciated!
