I've not touched SQL server programming since 1999. I have very little
memory of it and need some clarifications on some basic questions that
I could even use a book for. Until I get myself a good book, someone
please help me with the answers:
1) What are SQL functions and how are they different from stored
procedures? Do both of the programming objects not achieve the same
thing? What was the need of having one in addition to the other?
2) How do we use an "if construct"/if clause within a SQL statement?
Can we use conditional checking with the if construct within a stored
procedure? Can you please post a trivial example of a stored procedure
with an if clause?
3) Stored procedures can have input parameters as well as output
parameters. Can they also have in/out parameters that are like "by
reference" parameters? What's the syntax on Microsoft's T-SQL version?
4) How does one check the return value of a stored procedure?
Hi All,I am used to other SQL engines, and have a few basic questions--1)If I wanted to conditionally drop a table, does SQL Server have away to natively do this? Many SQL implementations will allowsomething like:CREATE OR REPLACE tablename ASSELECTx,y,zFROM sourcetable;Does SQL Server have something like this? This syntax, both the"create table as select" syntax and the "create or replace" syntaxseem to cause problems.2) Some of our existing queries have a keyword, "GO" where I wouldotherwise expect a semi-colon. Is there a functional differencebetween the two? I seem to be able to replace the "GO" keywords withsemi-colons without any changes in how the script behaves, but Ithought I would check and see if anyone has advice about thedifferences here.Thanks in advance,Ben
Im studying sql server clustering at the moment and have come across many points which confuses me. Can any SQL Clustering Guru clarify some of them for me or point me in the right direction. thanks.
1) Single Instance clustering now replaces Active/Passive clustering? If so, doesn't this mean the you cant run an 'Active/Active Cluster' by installing 2 instances of sql server on each node?
2) Multiple Instance clustering now replaces Active/Active clustering?
3) You can only have 1 instance of SQLSERVER on each node in a single instance cluster (2 nodes). However u have more than 1 sqlserver in a multiple instance cluster?
And last but not least, do u know where i can get the latest resources on sql server clustering? apart from microsoft.com. thanks.
I have two questions concerning formatting output in SQL.
I have a processer speed column that I would like to make usable. A sample entry is 2793, which I would like to be returned as '2.8 GHz'. I am using the following successfully, but when I try to append the ' GHz' it gives me a conversion error (I have tried putting both a cast and convert around the round statement). ROUND(CONVERT(DECIMAL,PCCPUSpeed_IN) / 1000,1) + ' GHz'
Secondly, I have a RAM colum that stores values such as 534753280. I would like the results to fallin into 64 Mb buckets. So values between 512000 & 532480 should return '512 Mb'. I realize I can do this using a CASE statement, but is there a better way?
I've done some basic database stuff with Access and ASP, but I have a client who has told me that he is in over his head and wants me to try getting his "mini CMS" site to work. He told me that he set everything up in Dreamweaver using ASP and "MSSQL"
I have never used what he calls: "MSSQL" before. Do I need software to create an "MSSQL" database? or is it all coded?
Also, one other question. The whole purpose of this site is to have Admin users log in to a page where they can edit the content of a page and upload it. If there is a large block of text, does it all reside in a field in the database, or would it be a link to a text file? kind of like linking to an image since images don't actually reside in the database.
I have created two tables and inserted the data using insert statements. The two tables that I have created are Automobile and Dealer_Info and have a 1:M relation from Dealer_Info > Automobile.
I am trying to list the Vehicle_Model and the number of times they are repeated, but when I run the code (below) the Count is inserted into an arbitrarily made up attribute (No column name). When that happens I cannot sort in DESC order.
I am using SQL(2000)
Select Vehicle_Model, Count(Vehicle_Model) from Automobile Group by (Vehicle_Model) Order by count(*) ;
Results:
Vehicle_Model (No column name) Escort 1 F150 4 Explorer 6 Taurus 12
I have been working with SSIS and find myself stopped on what seem like very basic tasks.
Am I right in thinking that, if you want to import a flat file into a SQL datatable, in most PC based systems, that you have to manually cast EVERY SINGLE COLUMN to unicode string, if you don't want to get the 'cannot convert between unicode and non-unicode string data types'? I mean, this is surely a VERY COMMON task, taking a CR/LF delimited ASCII file and dumping it into a database tables? What if you have 50 fields to import?
Am I the only one who finds it very difficult to cast a string from a textfile to a date format? At this point, I always just dump the date to a 8 character string and save it that way, and use a SQL view to cast it to a date. At least SQL Server can do 'basic date conversions'. Is there any way to freeze execution and find out what data was provided to a derived column, if you're trying to do some substring operations and hodge together a date? I couldn't find any. I usually put a extra column in a datatable and plunk some data there to find out 'what it looked like', how else can you debug an SSIS program?
Is there a planned service pack or update to SSIS that will make this more usable? Does Microsoft ever intend to implement an UPSERT type module? Or a File Move operation that handles directories that fill up with log files, each of which have to be added to a datatable and then moved to an archive directory (surely, another very common task - I figured out how to accomplish this but it is far from intuitive, the way it currently works). Sorry for grumbling, but honestly, I find turnaround time to develop a very simple import program is RIDICULOUS. It's quicker to write a COBOL program to issue a bunch of formatted SQL UPDATE statements than using this application.
I am new to SQL 2005, can someone give me some details instructions about how to do below two tasks:
All my developers are in a window domain user group, I need to grant dbo privileges to that domain group so then can do the their development work. The rule is all objects they create need to be owned by dbo not by there ID. ( I can€™t do it because I got €ś The €śDeafult_Schema clause cannot be used with a windows group€?) Same as above but this time they only need select permission on tables nothing else.
I'm new to using SQL server and have a few setup questions. I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.
Hello all, this is my first post, I have a couple of basic questions regarding the use of SSIS. I have used SSIS for a couple of years for on off table loads but never implemented production jobs with it so please bear with these questions.
1. When making scripts that connect to one or more databases, is there a way to store the userid and password somewhere besides embedded within the package, such as a web.config file, this would make routine password changes much more manageble.
I guess it was only one question but any help would be greatly appreciated.
Hi. I'm reading about encryption and not totally up-to-speed on what the Service Master Key is all about...
Let me paraphrase this book: Microsoft SQL Server 2005 Implementation and Maintenance Training Kit by Microsoft Press, while I ask questions... ___________________________________________________________
The database engine uses the Service Master Key to encrypt... - Linked server passwords (example?) - Connection Strings (example?) - Account credentials (example?) - All database master keys
You should back up the Service Master Key and store it offsite. (Even if I never plan to use encryption?)
ALTER SERVICE MASTER KEY REGENERATE regenerates the service master key. (Is it building a completely different key, or regenerating the original?) (Why would I want to regenerate it?) (Does this void all database master keys encrypted by the original Service Master Key?) (If so, do I regenerate all database master keys voided by the regeneration?)
A database master key is optional. (So if I have Server A with encrypted database A, that I want to restore to Server B, I need the following items to perform restore? - a backup of database A - the Service Master Key of A at time of backup - associated password of Service Master Key at time of backup)
We have a small Ethernet LAN running SBS 2003 R2. I have installed SQL Server 2005 that came with that release on the server. I did not specify a named instance.
1) What is the name of the default instance?
2) When I go to a client computer I cannot see the SQL server instance.
For example on my PC I have VS2005. If I go to Server Explorer, all I see is the local copy of SQL Server 2005 Express I have installed on my PC. I see nothing for the server. Does the server need configuring within SQL Server to allow this. Do I need to change permisions on the folders where the SQL Server 2005 files are stored.
In short, what basic steps do I need to follow to get acces to SQL Server on my server from a client.
3) If I want to create a new named instance, do I have to do Run the whole setup again? There appears to be no managemnt tool for instances of SQL server.
I really confused , I wanna get an rowid on sql 2000 table so I have created a sproc and it's syntax is OK How can I check it on sql query analyzer? this sql server 2000 Also How can I use that in select statement?
thanks..
here is my select statement which I have to use sproc inside select custid,ordernum,sku,amount, dbo.get_rownums (custid,ordernum,sku ) ??? from tp_cod cod
here is my sproc: CREATE PROCEDURE [dbo].[get_rownums] @custid as varchar(10),@ordernum as varchar(5),@sku as varchar(10) , @i as int output AS BEGIN DECLARE @SkuID as varchar(10) --DECLARE @i as int DECLARE got_sku CURSOR FOR Select sku from tp_cod where custid=@custid and ordernum=@ordernum set nocount on set @i=0 OPEN got_sku FETCH NEXT FROM got_sku INTO @SkuID WHILE @@FETCH_STATUS = 0 BEGIN Set @i =@i + 1 if @SkuID=@sku begin return @i end else begin FETCH NEXT FROM got_sku INTO @SkuID end END CLOSE got_sku DEALLOCATE got_sku END GO
Greetings! I am writing a proof of concept ASP.net application for my employer and I have run into a couple perplexing issues. First off, In my Page_Load event I have the following code. txOTHrs.Attributes["onBlur"] = "return calculateTotal(this)"; The onblur event handler is coded as follows function calculateTotal(tb) { var regHrs = document.getElementById ("txRegHrs").value; var otHrs = document.getElementById("txOtHrs").value; var rate = document.getElementById("txRate").value; var regAmt = rate * regHrs; var otAmt= rate * ( otHrs * 1.5 ); var total = otAmt + regAmt; document.getElementById("txTotal").value = total; } Everything seems to work fine. The onBlur event is handled and, as I expect the value in the txTotal text box is updated correctly. The problem occurs when I try to access that value in the codebehind page. When ever I look at txTotal.Text in the debugger it is always blank. This has left me scratching my head as I can clearly see a value on the web page. Have I missed some step where I have to notify the server that a value in the control has changed ?
My second question is, I hope a very easy one to answer. In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?
Any help anyone can provide is greatly appreciated!!
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" ) conPubs.Open() End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then 2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
I downloaded SQLExpress and Visual Studio Express to my home computer. I built a simple database, adding data through theSQLexpress admin tool. I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder. I can still see and work in the database through SQL server Express. The web page and the MSStudio attempt to connect to the mdf file both fail with this message: Cannot open user default database. Login failed.Login failed for user 'KAAAK/Administrator'. So it seems to be trying to connect as the Windows user. When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user. from web.config: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345" providerName="System.Data.SqlClient"/> </connectionStrings> That was changed from the original string created automatically by MS Studio <connectionStrings> <add name="stocksConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|stocks.mdf;Integrated Security=True;User Instance=True;" providerName="System.Data.SqlClient"/> </connectionStrings> I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.? Thanks, Michael
Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row. When it gets to the third line the page crashes with an error. SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True"); SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection); SqlDataReader reader = cmd.ExecuteReader();
is there somewhere i need to put in my username or password? or is this code just wrong
not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present. ....WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%' however it appears to finding only the rows where all three words are present in the L_Keywords field
This is a very simple question. How would a select satement be formated in the following example. SELECT Grade, Student_ID, First_Name, Last_Name FROM Scores WHERE (This is where I'm stuck and I know this is not the right formatting although I wish it were because it would make my life a little bit easier.) Student_ID = 115485, 115856, 568547, 965864, etc... I may have up to 100 specific student ids to put in this one statement. I know I can use the "WHERE Student_ID = 115485 OR Student_ID = 115856, OR Student_ID = 568547" but that would be alot of waste. Seems like there should be an easier way than using the "OR Student_ID =" for every entry. Can someone explain another way I can do this. Thanks in advance.
Hey, I have a pretty simple question.My query is throwing an error saying "Invalid column name 'subject'."The problem is that subject is a custom column I've made, well just look at the sql:SELECT a.ArticleID, subject=ISNULL((select subject from subjects),'') where subject='some subject'
I have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
Hi there. I am trying to use MDX code to create a measure in ProClarity. Please help!!
A store can be one of a number of ‘Brands’. The MDX segment below gives me the Sales Value of a selected item, but for Store Brand 'Brand1' only. This works fine - but how do I add a brand? That is, how do I see the combined Sales Value for the selected item for 'Brand1' and 'Brand2'?
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2.... But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.
I need help. 160821A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. I need help. Our SQL Server is crashing. The Database is still recovering. I can not kill any Process when the Server crashed. I shoot down and restarted the server. Nothing to do. I can not access to the database. It is recovering. How long? I do not know. What can be the reason of the recovering? Nor the event log of Windows NT or the log files of the SQL SERVER can help me.
Here is a part of the log file
23216Arithmetic overflow error for type %s, value = %f.
10915There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
99/01/19 01:14:25.69 spid25 bufwait: timeout, BUF_IO, bp 0x1bba600, pg 0x11b50, stat 0x801000/0x6, obj 0x23494814, bpss 0x124a2a0 99/01/19 01:14:27.15 ods Error : 17824, Severity: 10, State: 0 99/01/19 01:14:27.15 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'myserver'. 99/01/19 01:14:27.15 ods OS Error : 232, The pipe is being closed.
I have many views and views on these views. Is there anyone out there who might have a suggestion as to how I can keep them straight so that I won't lose track of what I made them for? I am starting to not remember why I made some, so I have to go back and try and remember. Any suggestion would be appreciated.
