|
In Sql server management studio, after creating the stored procedure, it saves it in the default folder 'Projects'. Is that where the stored procs would be saved? If I wrote it in VS, where would I save it?
|
|
|
|
|
SQL Server itself stores the stored procedures in the database. While it is always nice to have your own text file version for easy editing there isn't anyway that I've found that is a great solution. All methods I've used have all been adequate and I'm still trying to find the great method that would actually save me time and make it easy to manage.
Both methods that I've found that work adequately are both deployment script driven - in other words the stored procedures are written in such a way that deployment is easy. However, the draw back is that code maintenance is slightly more difficult.
The first solution is to bundle all the stored procedures (or bundled by subsystem [however you want to define that]) into one script. When it comes time to deploy the application to the live/production environment all you have to do is run that script to upload the stored procedures. (I tend to have separate scripts for data model changes, additions, lookup/meta data, user defined functions, stored procedures and permissions). This solution suffers from the fact that the stored procedure script becomes bloated on a large system. However, it makes it easy to deploy manually by a DBA.
The second solution puts each of the scripts in various folders. The folders are numbered so that the can be followed in sequence. One of these folders will be for stored procedures. You can put all the stored procedures into the folder and name each file as per the stored procedure. This can produce potentially hundreds (or thousands) of files so a DBA is not going to go through each folder running each script manually. I've also found that concatenating the files for deployment can sometime produce strange effects, so the solution that I found was to create an install class to add to the setup of the application that follows the folders' numbering in sequence to deploy everything to the server. However this is also difficult to maintain.
I know this does not really answer your questions - but then again, VS still doesn't have a good solution to database deployment still (unless someone wishes to prove me wrong). I also realise you didn't ask about deployment, but it is an important thing to think about when you start as it will make everything easier in the long run.
Bottom line, your SQL Scripts should be stored with your Visual Studio Solution, unless the solution covers many different technologies in which case you may have to figure out a compromise solution.
NOTE: solution (with small "s") and Solution (with a capital "S") mean different things. The latter specifically refers to a Visual Studio Solution
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi,
I am trying to insert a datetime datatype into the Sql Server 2005 from the code in my file. The query is:
"Insert into xx (date, day) Values (" + currentdate + "," + currentday+")"
I get an error of not being able to convert the datetime to System.timespan. I dont know what it means.
can anyone please tell me what I am diong wrong 
|
|
|
|
|
You haven't posted enough code to make a real diagnosis. However, from what you have said there is a DateTime object that somewhere is being converted to a TimeSpan object and it isn't able to do that - sorry, that is just paraphrasing the error message, but without the code I can't help much.
Also, the little code that you do show has security holes in it. You may like to read about SQL Injection Attacks and tips on how to solve them[^]
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hello All,
I have in my database two columne number colume and varchar and i need to add this columne in one columne i try cast but it gives me invaled express.
please help
hoho
|
|
|
|
|
It is very difficult to read your request because it is just one very long sentence.
You have two columns in your database.
A number column, and a varchar column.
You need to add [some column] in one single column.
You try to cast [some column] but it gives you an error message saying invalid expression.
I hope that is correct. I've put in square brackets where there was not sufficient context in order to parse your request.
Please supply the missing context from your request. Please also supply the code that you currently have.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi everybody,
I am kind of new in this area... So, I need to restore some .dbf files on SQL. Could you tell me which is the best way to do that?
P.S. I don't even know the .dbf file's structure. 
Thanks a lot,
Cristina
|
|
|
|
|
Hello,
I posted this over at sqlserverentral.com but have not had any reply's.
If you are a member here is the link:
Code[^]
What this will do:
Allows you to show the usage of any table, procedure, column, or view in any database on the server where the code is running.
Example:
exec spFindObjectUsage 'MyTableOfParts'
This will return all the views and procedures that call out MyTableOfParts.
Here is the code:
<br />
<br />
CREATE PROCEDURE spFindObjectUsage<br />
@ObjectToFind NVARCHAR(100) = ''<br />
,@ResultMessage VARCHAR(200) = '' OUTPUT<br />
AS<br />
DECLARE<br />
@ReturnCode INT<br />
,@StringToExecute NVARCHAR(1500)<br />
,@DBToProcess INT<br />
,@ServerName VARCHAR(200)<br />
,@TableWithServer VARCHAR(200)<br />
,@NameOfDatabase VARCHAR(50)<br />
--<br />
DECLARE @ServerDatabaseTables TABLE<br />
(TempTblID INT NOT NULL IDENTITY(1,1)<br />
,DBName VARCHAR(100) NOT NULL DEFAULT ''<br />
,Processed BIT NOT NULL DEFAULT 0)<br />
--<br />
CREATE TABLE #ServerDatabaseObjectUsage <br />
(UsageID INT NOT NULL IDENTITY(1,1)<br />
,DBName VARCHAR(100) NOT NULL DEFAULT ''<br />
,ObjectUsedIn VARCHAR(200) NOT NULL DEFAULT ''<br />
,TypeOfObject VARCHAR(50) NOT NULL DEFAULT ''<br />
,IsColumnOfTable BIT NOT NULL DEFAULT 0)<br />
--<br />
-- first get all the databases on the current server<br />
--<br />
INSERT INTO @ServerDatabaseTables<br />
(DBName)<br />
SELECT<br />
name<br />
FROM master.dbo.sysdatabases<br />
--<br />
SET @TableWithServer = ''<br />
SET @NameOfDatabase = ''<br />
--<br />
SET NOCOUNT ON<br />
-- each database has it's own listing of System Objects so inorder to get<br />
-- a correct listing we will need to go through every database.<br />
-- the only way I know to do this is using sqlexec.<br />
-- I know it is not the best way but we will need the ability to dynamically<br />
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect<br />
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN<br />
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)<br />
--<br />
SELECT @ServerName = DBName + '.dbo.'<br />
,@NameOfDatabase = DBName<br />
FROM @ServerDatabaseTables<br />
WHERE TempTblID = @DBToProcess<br />
--<br />
SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +<br />
'(DBName' +<br />
',ObjectUsedIn' +<br />
',TypeOfObject' + <br />
',IsColumnOfTable) ' +<br />
'SELECT DISTINCT ' +<br />
char(39)+@NameOfDatabase+char(39)+ <br />
',obj.NAME' + <br />
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) + <br />
' THEN ' + char(39) + 'PROCEDURE' + char(39) +<br />
' WHEN ' + char(39) + 'V' + char(39) + <br />
' THEN ' + char(39) + 'VIEW' + char(39) +<br />
' WHEN ' + char(39) + 'U' + char(39) + <br />
' THEN ' + char(39) + 'USER TABLE' + char(39) +<br />
' WHEN ' + char(39) + 'D' + char(39) + <br />
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +<br />
' WHEN ' + char(39) + 'F' + char(39) + <br />
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +<br />
' WHEN ' + char(39) + 'IF' + char(39) + <br />
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +<br />
' WHEN ' + char(39) + 'FN' + char(39) + <br />
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +<br />
' WHEN ' + char(39) + 'TF' + char(39) + <br />
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +<br />
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +<br />
',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +<br />
'THEN 1 ELSE 0 END)' + <br />
'FROM ' + @ServerName + 'sysobjects as obj ' + <br />
'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' + <br />
'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' + <br />
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + <br />
' OR syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + <br />
' ORDER BY obj.name '<br />
SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))<br />
PRINT LEN(@StringToExecute)<br />
--<br />
exec sp_executesql @StringToExecute<br />
--<br />
IF (@@ERROR != 0) BEGIN<br />
SET @ReturnCode = 1<br />
GOTO END_PROCEDURE<br />
END<br />
--<br />
UPDATE @ServerDatabaseTables<br />
SET Processed = 1<br />
WHERE TempTblID = @DBToProcess<br />
END<br />
--<br />
SELECT * FROM #ServerDatabaseObjectUsage<br />
--<br />
DROP TABLE #ServerDatabaseObjectUsage<br />
--<br />
SET @ReturnCode = 0<br />
<br />
END_PROCEDURE:<br />
IF (@ReturnCode != 0) BEGIN<br />
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))<br />
END ELSE BEGIN<br />
SET @ResultMessage = 'OK'<br />
END<br />
RETURN @ReturnCode<br />
I am looking for feed back on the code:
If you like it?
Is there a better way to do this?
And any other comments or suggestions are welome.
Please let me know,
William O'Malley
I hate users. Not all of them, just the ones who talk.CP member: Al Einstien
|
|
|
|
|
Would you be so kind as to put your 2 cents in on this code?
Thanks,
Will
I hate users. Not all of them, just the ones who talk.CP member: Al Einstien
|
|
|
|
|
Hi,
As a relative newbie to SQL Server/ASP.NET I'm hoping someone here
can help with my problem. I'm developing a timesheet application in
ASP.NET C# using Visual Studio 2003 with a database built in MSDE.
One of my forms needs to return a simple list of resources from my
database. I have followed the guide on the MSDN libraries, but for
some reason I continuously get the same error message.
What I've done so far is create the database, tables, and populate
with some sample data using using Server Explorer in Visual Studio. I have
connected to the database (using integrated security) and I am
trying to get the contents of the Resource table to appear on my
form. I have then created a DataAdapter (tested the connection, set
the SQL as a simple SELECT * from Resource, etc), which also generates an sqlConnection for me. To test this I have previewed the generated data, and it returns what I want, so I have chosen to generate a DataSet of this. I am then trying to get this data into a simple DataGrid. On the properties of the DataGrid I have changed the DataSource to point at my Dataset. As I
understand it, I then have to add the following to my Page Load
section of my code.
sqlConnection1.Open();
this.sqlDataAdapter1.Fill(this.dsResource);
DataGrid1.DataBind();
sqlConnection1.Close();
The form builds fine, but when I browse to the particular form I get
the following error for the sqlConnection1.Open(); line. If I remove this line the error simply moves to the line below.
Exception Details: System.Data.SqlClient.SqlException: Cannot open
database requested in login 'SCMS'. Login fails. Login failed for
user 'AL-NOTEPAD\ASPNET'.
To me this is an error with my connection string. My database
instance is actually 'AL-NOTEPAD\VSDOTNET'. However the properties
for sqlConnection1 are pointing to the correct datasource. I do not
know why the application is looking for user 'AL-NOTEPAD\ASPNET'. It does not exist, to my knowledge.
Any help with this would be greatly appreciated, as I get the same
error with my code for forms-based login...
Thanks in advance..
|
|
|
|
|
Check the web.config file if you have added the connectionstring there, and the source for the SqlConnecton1 again to double where it is pointing to.
|
|
|
|
|
i've developed an application to manage my table of logins
from that i can insert,modify,delete and view the login details of a user the problem is when i choose to view the details of a user with administrator rights it shows an error, also if i create a login user with administrator rights in the table the user is added as user and not as administrator why?
|
|
|
|
|
There's no way to answer this question with anything useful without knowing anything about your "table of logins", where this table is stored and what security options your using on this table.
But, in most systems anyway  , normal users cannot create Administrator accounts, for obvious reasons, and cannot see any details about them.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I derived a Class "CProtocol" from CRecordset which is linked with but one database table.
Everything works fine until I call AddNew(), here I receive a assertion Faile Message, referring to dbcore.cpp
Code sniplet ( .cpp file):
CString csTimeNow;
// Hole Hostnamen
DWORD dwHostLength = MAX_COMPUTERNAME_LENGTH + 1;
GetComputerName( csComputerName.GetBuffer( 0), &dwHostLength);
// Hole UserNamen
DWORD dwUserLength = UNLEN + 1;
GetUserName( csUserName.GetBuffer( 0), &dwUserLength);
// Hole aktuelle Zeit
COleDateTime coTimeNow(COleDateTime::GetCurrentTime());
csTimeNow.Format( "%04d%02d%02d%02d%02d%02d00", coTimeNow.GetYear(),
coTimeNow.GetMonth(),
coTimeNow.GetDay(),
coTimeNow.GetHour(),
coTimeNow.GetMinute(),
coTimeNow.GetSecond());
// Instanz zur Datenbank aufbauen
CString csConnectionString;
csConnectionString = "DSN=ODR_CRM;UID=ODRGMBH;PWD=origin";
m_pDB = new CDatabase();
if( m_pDB->OpenEx(csConnectionString,CDatabase::noOdbcDialog))
{
// We´re connected to the database
}
else
{
// we have a problem connecting to the database
}
// Access to Class Protokoll Table
m_pProtocol = new CProtokoll( m_pDB);
m_pProtocol->Open(); // ready to use
m_pProtocol->m_Timestamp = csTimeNow;
m_pProtocol->m_Benutzer = csUserName;
m_pProtocol->m_Rechner = csComputerName;
m_pProtocol->m_Aktion = csMessage;
if( m_pProtocol->IsOpen() )
{
m_pProtocol->AddNew(); // Ready to add new records
}
m_pProtocol->Update(); // Update Table
m_pProtocol->Requery(); //
.h file
CDatabase* m_pDB; // Pointer to CDatabase Class
CProtokoll* m_pProtocol; // Pointer to CRecordset Class
|
|
|
|
|
Hi,
I want to query two tables:
Select * FROM table1, table2
but I only want one column from table2.
Can I query * from table1 and just Column1 from table2?
thanks,
Ron
|
|
|
|
|
Select A.*, B.Fld FROM A , B
where A.Fld = B.Fld
|
|
|
|
|
|
While M Javed Khan's answer is correct, you should be aware that it is generally bad practice to SELECT * in code. I only ever do that while trying things out in the query analyzer.
The reason for that is that SELECT * can break production code because someone changed the database schema and that change is being propogated to the application. The application isn't expecting the different set of columns it is now receiving so it will perform unexpectedly.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
|
Give the alias names to your tables in the following way
select t2.columnname ,t1.columnname from table1 t1,table2 t2 where condition follows
Here Column name indicates the name of the column which you want to display from the second table table 2 .This way you can diasplay the columns you want to display .
|
|
|
|
|
|
Hi,
We have a weird trouble with our SQL 2000 Server. When we turn the machines on (the server and our only work-station{client}, both in the same Domain) we can access SQL from the client using standard Windows authentication, and it works just fine, but after a while it does not work any more.
This is the Error Msg we get: Cannot generate SSPI context. Using the Command Prompt from the client machine, we issue a ping "Server" as well as a ping "IP address" and both work; This makes us think that the DNS is working properly. But then, why is it failing after a few minutes. If we try to connect using the SQL Server Authentication it works just fine.
This is the first time we encounter this problem. We Re-Installed SQL in the Server as well as the client tools on our other machine, but got the same behavior.
After searching Inet for some help we found an article by MS about something called Kerberos, that honestly did not understand at all.
Thanks for your time and help.
A very lost guy(who is a big time ignorant about all this)
-- modified at 18:27 Thursday 2nd February, 2006
|
|
|
|
|
Presumably that was this article[^]. In a Windows 2000 (or later) Active Directory domain, Kerberos is the protocol used to perform authentication. When you make a connection to SQL Server, the client library uses the Kerberos protocol to get a ticket from the Ticket-Granting-Service on a domain controller. It then presents that ticket to the SQL Server. If the ticket is valid, SQL Server allows the connection.
Kerberos tickets have timestamps on them so they're only valid for a short-ish period and cannot be replayed by an attacker. You should ensure that the clocks on the servers, domain controllers and clients are all synchronised. If you have multiple domain controllers, ensure that they're all in sync with each other - the default configuration for member servers and workstations is to synchronise with a DC. Ideally DCs should get their time from a true external time source, but synchronising with an online time server is fine. We use our ISP's NTP server; if you don't know yours or they don't have one, see the public NTP servers list[^]. On the DC, use NET TIME /SETSNTP to set the NTP servers to use.
Otherwise I suggest you follow the suggestions in that KB article and then call MS support.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I need to match entries of two tables Survey and Phones . Donno what am I missing on
Tables and example :
Phones
[CaseID], [SurveyID],[DateTime], [TelephoneNumber]
90 13875 2006-01-05 10:31:00.000 2814211345 NULL
90 13875 2006-01-05 10:33:00.000 2814211345 NULL
Survey has following columns
[CaseID], [SurveyID], [CaseStartTime],[Phone]
90 13875 2006-01-05 10:31:00.000 2814211345 NULL
90 13875 2006-01-05 10:33:00.000 2814211345 NULL
There are multiple entries of CaseID in both the tables. When a call is made, there is an entry in Phones table and after that an entry is made in Survey table. Timelag could vary from millisec to 2 or 3 mins.So the way to identify is through timestamp, factoring in mins difference.
I am using this query - does not match correctly though
SELECT t1.CaseID, t1.CaseStartTime,t1.ITS, t2.CaseID,t2.DateTime,
DateDiff("n", t2.DateTime,t1.CaseStartTime)as "Min_Diff"
FROM Survey AS t1,Phones AS t2
WHERE t1.CaseID = t2.CaseID and t1.CaseID=90
and DateDiff("n", t2.DateTime,t1.CaseStartTime) <= 2 and DateDiff("n", t2.DateTime,t1.CaseStartTime) >0
Order by t1.CaseID;
Result
90 2006-01-05 10:33:00.000 47 90 2006-01-05 10:31:00.000 2
Minoo S
|
|
|
|
|
Minoo S wrote: Donno what am I missing on
Proper relationships? Primary Keys?
I really don't know where to start on this. There is apparently no real relationship between these two tables. In which case, matching them up is going to be sporatic and error prone.
To get the situation right:
A phone call is made. At the start of the call a row is inserted in the Phones table.
At the end of the call a row is inserted in the Survey table.
Minoo S wrote: Timelag could vary from millisec to 2 or 3 mins.So the way to identify is through timestamp, factoring in mins difference.
No it isn't. Really! That isn't the way to design a database.
Why does the survey table not have a foreign key that matches the primary key of the Phones table? (Or if there is the possibility of a many-to-many join - why is there no intermediate table?)
I'm hoping that there is missing information here and you just need to post it. What is the structure of the two tables? (I want DDL or a description of column names, types and primary/foreign keys)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
