I have an SSCE database that I did not create. I'd like the app I'm working on to programmatically create said database the first time it runs (if for no other reason than the fact the next version may want to need the schema and not wipe out what's already there.
My idea was to have the Management Studio give me a database creation script like SSDE would and execute the T-SQL commands inside of the app but it doesn't appear that's something the Studio will do for SSCE databases. Is there another/better way to pull this off?
I am a total newbie when it comes to SQL databases.
I currently work for a company developing software. We use a VB Development environment and connect to Dbase III database files using DAO... ewwwww...
We are looking to upgrade our development environment to VB.net and our database to either MSSQL or mySQL...
My question is - most of our users are on standalone machines. Our software is NOT internet based, nor do we want it that way at the moment.
Is there a way to connect to a MSSQL database (ie the .mdf file) without having a server running? Or are we better advised to stick to an MS Access database if this is the case....
Basically the reasoning behind upgrading is better speed and record locking. Any advice would be greatly appreciated.
After moving production to 2005 mid-2006, which isn't too bad on the "learning new tricks" scale, I'm wondering if I'm missing something by not adopting SSMS projects as a mechanism of organizing stored procedures. You see, I still create my own folder structure and store all my SP's there. Report SPs, Admin SPs, Maintenance SPs, etc...
I've cranked up a project folder, and I can't see any intuitive way of saving an existing SP there that brings any benefit beyond what I currently do. Navigate to a project folder, open it up, save the sp.
Net benefit to an old dog: zero
I see articles on the net about add-ins to sort files in project. Seems like a headache is created by a project and add-ins are needed to solve the problem.
I like my prior method of organizing SPs.
Am I missing a key benefit of a project folder somewhere? Some magic that makes project folders worthwhile?
Hi, For your day-to-day SQL Server issues like query tuning, optimization, TSQL problems, I am writing the blog called http://blog.namwarrizvi.com
Some of the latest articles are: Generating 1 million rows in less than a second Conditionally add column in the table Multiple Inserts in one statement Capture every data operation in SQL Server 2008 100 Nano seconds precision in SQL Server 2008 Represent Trees and Graphs in TSQL MERGE Statement of SQL Seerver 2008 Return Last n Orders by using APPLY operator Number padding in TSQL Microsoft Performance Point Server and Sharepoint Caching and Recompilation in SQL Server 2005
and many more....
I will really appreciate comments and suggestions.
I am using QA.Usinf the mouse, When ever I want to select a part of a line, I click the first character of my selection with the mouse, and When I want to select a part of my line, the cursor goes to the end of the line instead of stopping at the character in which I want to stop the selection. Do u know how I can avoid this, since it s annoying me. Thank you for your help.
Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.
I'm generally following theTips and Tricks article.
"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.
e.g.
SELECT * FROM OPENQUERY(DMServer, 'select €¦ FROM Modell PREDICTION JOIN OPENQUERY€¦')
The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query SELECT * FROM OPENQUERY(DMServer, 'SELECT t.[CardTransactionID], t.[PostingDate], [Misuse Abuse Profile].[Even Dollar Purchase], PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score, PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt, t.[BillingAmount] FROM [Misuse Abuse Profile] PREDICTION JOIN OPENQUERY([Athena Dev], ''SELECT [CardTransactionID], [PostingDate], [BillingAmount], [AccountNumber], [SupplierStateProvinceCode], [MerchantCategoryCode], [PurchaseIDFormat], [TransactionTime], [TaxAmountIncludedCode], [Tax2AmountIncludedCode], [OrderTypeCode], [MemoPostFlag], [EvenDollarPurchase] FROM [dbo].[vMisuseAbuseProfile] '') AS t ON [Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND [Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND [Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND [Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND [Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND [Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND [Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND [Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND [Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND [Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase] ')
In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no? OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".
Hi there I sorry if I have placed this query in the wrong place. I'm getting to grips with ASP.net 2, slowly but surely! When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>
The database is definitly in the folder that the error message relates to. What I'm finding confusing is that the connection string seems to be finding "aranga"s database. Is it something daft?
Some categories have sub-categories. Therefore, some entries will have null for parentID while others will point to another category.
I need to select all of the categories, but have them returned in the correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)
I have two tables: 1) Table that holds all available ports. 2) Table that holds users for each port.
There may be times where one user is getting more than one port at a time.
I've built up an ASP .NET page that will display each user its port/s in one table. On another table I want to display all the other available ports which the user doesn't posses and can buy to own.
My problem is where I try to build up the query. I just can't get all the other ports in normal display.
For example, this is what I need:
Ports table: 1/1 1/2 1/3 1/4
Users table: User A , 1/1 User A , 1/2
ASP page display:
------------ User A Holds: 1/1 1/2 ------------
Available: 1/3 1/4 ------------
Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it ! I've tried all kinds of Joins and nesting SELECT queries with no luck.
here is my problem and i am probably just stupid. i need the total amount Transfered minus the Received per barcode. so here i would need to see a total of 3.
I've getting the error message at the time ntbackup starts the backup of the server:
Event Type: Error Event Source: SQLWRITER Event Category: None Event ID: 24579 Date: 22-Aug-07 Time: 20:00:26 User: N/A Computer: DBS Description: Sqllib error: Database DYNAMICS of SQL server instance XXX is stored on multiple volumes, only some of which are being shadowed. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 53 51 4c 53 4e 41 50 43 SQLSNAPC 0008: 33 36 31 00 00 00 00 00 361..... 0010: 53 51 4c 53 4e 41 50 43 SQLSNAPC 0018: 32 34 36 00 00 00 00 00 246.....
Where is the problem? How to fix this? Does this mean that I would not be able to restore server in the case of failure?
SQL - version SQL Server 9.0.3054 (2005 SP2) OS - 5.2.3790 (W2k3 R2 SP2)
I am trying to insert form data...three form sections into three different tables....i can insert into one table just fine....is this not possible ...or what? If so can someone please send me a small sample... thank you ...by the way i am using visual studio...vb asp.net 2.0 with ajax. Thanks in advance!
I haven't tested it, because at the moment I can't access my SQL database I know this is probably a stupid question, but...How can I test for DBNull when I'm using SqlDataReader.Item(string)? I use to check it using: if(!reader.IsDBNull(2)) ...But I have found that can not count on a particular ordinal reference because some of the stored procedures select the columns in different orders. can I do something like: if(reader["Column1"] != DBNull.Value)or with the reader["Column1"] throw an error because it is not found?? I too hate when people ask questions that they could test themselves...but I can't really test it at the moment. Any input would be greatly appreciated.
I'm trying to backup a database onto a different server within the same SQL Server Group, but the Enterprise Manager only offers the drives on the local server as destination options. Do I have something configured incorrectly or is there a way to do this manually? Thanks in advance.
Ok, I'm trying to understand how the db user corelates to the SqL login. Can one db user have multiple logins attached to it (like dbo with sa and nt admins).
This is in reference to the issue of broken chains of ownership problems that pop up from multiple users creating tables in a single database. I would like to be able to make all the developers be the dbo user of a given database without making them a sysadmin of the entire server. Is this possible?
If not, is there a sp that fixes a db so that all ownership reverts to dbo?
I have a little query in access ..now i must write it in T-Sql
the original... (MSG.IDLINGUA = IIF( EXISTS (SELECT * FROM ESMESSAGGI AS MSG WHERE MSG.IDLINGUA = [@idLingua] AND MSG.IDMESSAGGIO = BITS.IDDESCR), [@idLingua], LDEF.IDLINGUA))
I can't be able to assign a select value at my field... IDLINGUA to perform another select.. Of course I have used the "select case when exists"
I want to run SQL Server 2000 at home, on my PC. I dont have a server. My OS is Windows XP. Is this possible? Or do I have to use something like Access. If so then that sucks.
I use SQL Server at work and I want to practice at home.
I have installed MS SQL server eval edition and read through a lot of BOL, just about got my head around users and authentication etc and have a pretty good grounding on db design and architecture.
I now want to mess around with the sample databases a bit to get used to the SQL server interface before I start creating new ones etc but beyond random simple queries that I make up i'm struggling with what to do. I'm sure there are some microsoft tutorials based on the northwind and pubs databases I just cant seem to find any anywhere.
Dear Sql server experts I am suffering with a stupid problem in login into SQL server using SQL authentication method Well here is the case. When I start SQL server management studio of sql server 2005 A connect to server dialog comes up with 3 enabled combo box's for server type , server name and authentication If I connect to server via windows authentication then every thing goes fine but when I use the damn sql authentication so this alert occurred "Login failed for user 'toolbox', The user is not associater with a trusted SQL Server connection." Even with sa. Does any body has idea what to do? How to fix it? Please.
this is the alert when i use "sa" TITLE: Connect to Server - Cannot connect to myserver - ADDITIONAL INFORMATION: Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
Hopefully, this is an easy. Does SQL Server use the Windows system dateand time? Or does it keep track of what day/time it is on its own?We were just discussing this in the office today, because we'replanning for the changes in Daylight Savings Time in the United Statesthis year. It's going to start a month earlier and end a month laterthan it used to. Microsoft is apparently putting out patches forWindows so the OS will know to adjust the time by an hour on the rightdays, and we're trying to determine if our SQL and Oracle databases aregoing to be affected at all.--Richard
In sql server 2000, how do you manually go and truncate the logs? Doesthis only occur when you create a backup? all I want to do is manuallyshrink the log files. I have backups, so I don't need them.I actually backup the db once a week and back up the logs every day,yet the logs are getting real big. I thought this was configured inthe backup and/or the maintanance plan. I've done a restore (from thedb backup) manually hundreds of times, but I've never had to restoreto a checkpoint from the transaction log backups, maybe this is whyI'm confused on how to do this.thanks,dave
This is bad, what I thought I knew about clustering I now am questioning as I research this more.
When I have a multi server (say 4 servers) cluster using Windows 2003 & SQL 2005 Enterprise editions, can they or can they not all access the same resource database? If not, then I need to know how to provide better service to a web app that is heavy on the database access. We have a load balanced front end (web services) but the current SQL Server is now becoming a bottleneck and need to start to spread the workload across multiple sql servers access the same database in real time. Data is not currently partitioned. Regardless of the arrangement I will need to maintain the cluster for failover, but also need to spread the workload.
Thanks
Raymond Laubert MCITP:Administration, MCT http://www.rd-webhosting.com Home Based Business Library and Hosting
