Database

Insert Query -----> for datetime data type on sql server 2005

4-Feb-06 9:06

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

intrigued4-Feb-06 8:29

intrigued

4-Feb-06 8:29

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 Confused | :confused:

Re: Insert Query -----> for datetime data type on sql server 2005

Colin Angus Mackay4-Feb-06 9:11

4-Feb-06 9:11

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

Add two columen

webhay4-Feb-06 7:30

webhay

4-Feb-06 7:30

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

Re: Add two columen

Colin Angus Mackay4-Feb-06 12:39

List usage of any object in any DB on Server

4-Feb-06 12:39

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

restore a .dbf file

cristina_tudor4-Feb-06 7:07

cristina_tudor

4-Feb-06 7:07

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? Confused | :confused:

P.S. I don't even know the .dbf file's structure. Big Grin | :-D

Thanks a lot,

Cristina

OMalleyW4-Feb-06 6:25

OMalleyW

4-Feb-06 6:25

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

Hay Colin

OMalleyW6-Feb-06 10:32

OMalleyW

6-Feb-06 10:32

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

Help with simple DataGrid....

thebison4-Feb-06 4:43

thebison

4-Feb-06 4:43

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..

Re: Help with simple DataGrid....

intrigued4-Feb-06 8:34

intrigued

4-Feb-06 8:34

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.

Administrator

D.N.3-Feb-06 7:06

D.N.

3-Feb-06 7:06

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?

Re: Administrator

Dave Kreskowiak3-Feb-06 13:49

Dave Kreskowiak

3-Feb-06 13:49

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 Wink | ;)

, 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

Recordset AddNew fails

RadioOpa3-Feb-06 2:13

RadioOpa

3-Feb-06 2:13

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

Multi table queries

myNameIsRon2-Feb-06 21:11

2-Feb-06 21:11

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

Rana Muhammad Javed Khan2-Feb-06 21:15

Rana Muhammad Javed Khan

2-Feb-06 21:15

Select A.*, B.Fld FROM A , B
where A.Fld = B.Fld

myNameIsRon3-Feb-06 5:15

3-Feb-06 5:15

thanks M

Colin Angus Mackay2-Feb-06 22:48

2-Feb-06 22:48

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

myNameIsRon3-Feb-06 5:16

3-Feb-06 5:16

thanks Colin

Sri Vidhya3-Feb-06 21:58

Sri Vidhya

3-Feb-06 21:58

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 .

myNameIsRon4-Feb-06 5:20