Insert Relative Columns

Jul 26, 2006

So I want to insert 40 values into a table, starting at a particular column. Like this:

INSERT INTO MyTable (1) VALUES (...my forty values...)

Pretending that 0 (zero) indexes the first column, and 1 indexes the second column, the purpose is to skip the (first) column that contained an identity value (since normally you can't insert into an identity column anyway).

The only way I currently know how to solve this problem, is to use highly verbose syntax, like this:

INSERT INTO MyTable (...my forty column names...) VALUES (...my forty values...)

But yuck, who wants to explicitly mention all forty column names, ONLY BECAUSE I'm trying to avoid inserting a value into the first column which contains the identity?

View 1 Replies


ADVERTISEMENT

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

Oct 12, 2007



Hi,
i have a file which consists data as below,

3
123||
456||
789||

Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.


BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this?

Thanks,
-Badri

View 5 Replies View Related

Openquery Insert With Additional Columns For Manual Insert.

Apr 24, 2008



I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.

The query below works well because the table's columns are even in both databases:

Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')


I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.

My insert statement above no longer works because the column numbers don't match.

In a nutshell I would like a way to combine the following queries:

Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')

Insert into Webcases (insurancetype) Values ('SF')


--insurancetype is the new column.

View 9 Replies View Related

Relative Path

Apr 16, 2008

I have my SqlDataSource with my connection string:<add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersLaurensDesktopstockmanASP****.accdb" providerName="System.Data.OleDb"/> but I wanted to make a relative path from it because when I put it online i get an error. Thanks in advance  

View 6 Replies View Related

Relative Paths

Mar 30, 2006

We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig  to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?

For example: Our absolute config path is "D:Program FilesMicrosoft SQL Server90DTSPackagesETLProjectETLBaseconfigDefault.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:

'D:Program FilesMicrosoft SQL Server90DTSPackagesDEVDataExchangeETLBaseconfigconfigDefault.dtsconfig'.

Ideas?

View 6 Replies View Related

AttachDbName Relative Directory

Jan 23, 2007

I have host my ASP.NET application on gate.com, and there I have access to a SQL Server 2005 instance, and I am trying to make relative paths in my web.config work, (|Data Directory|), but I don't know how to change the default value of |Data Directory|, and it isn't the root of my application, I'm pretty sure. My setup is like so, and the file was created using SQL express 2005, I am not sure if that is my problem, because gate uses the full version.
/<root>/App_Data/Leads.mdf
here is the stack trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188   System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105   System.Data.SqlClient.SqlConnection.Open() +111   System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84   System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197   System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766   System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428   System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410   System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117   System.Configuration.SettingsBase.get_Item(String propertyName) +89   System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36   System.Web.Profile.ProfileBase.get_Item(String propertyName) +68   System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4   ProfileCommon.get_Cart() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot27a74ff4376615bdApp_Code.ceobor1b.4.cs:102   Default2.UpdateTotal() in \shared.hosting.localfscust98045754089webOrder.aspx.cs:45   Default2.Page_Load(Object sender, EventArgs e) in \shared.hosting.localfscust98045754089webOrder.aspx.cs:37   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34   System.Web.UI.Control.OnLoad(EventArgs e) +99   System.Web.UI.Control.LoadRecursive() +47   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
 

View 2 Replies View Related

Relative Dates For The Next Month-help

Apr 18, 2008

need help
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month


"tb_all_holiday before"
date_start date_end
----------------------------------------------------------
15/03/2008 00:00:00 17/09/2008 00:00:00

20/04/2008 00:00:00 12/05/2008 00:00:00



i must to covert it like this

to this - see relative dates for the next month

"VIEW_all_holiday after -next month only "
date_start date_end
----------------------------------------------------------
01/05/2008 00:00:00 31/05/2008 00:00:00

01/05/2008 00:00:00 12/05/2008 00:00:00

TNX for help

View 2 Replies View Related

XML Source Xsd Relative Path

Apr 24, 2007

Hi all,

Is it possible in the XML Source to specify a relative path for the xsd file? If not, do you have any idea why?

Best regards,
pc

View 5 Replies View Related

Drill Through --relative Path

Sep 21, 2007

I'm trying to use the drill-through feature to link to a report in different solution, on the same server, but in a different project. Local help files say: "The list of report names includes all reports in the current report server project. If the drillthrough report is on the report server but is not in the project, type the name of the report. The report name can contain a relative or absolute path to the report."


The report will be passing a parameter to the target report through drill-through. The target report is located on different solution and project but on a same server (report manager). Any help will be be appreciated.


Can someone give an example of a relative path? Nothing I've tried has worked, and I've posted this on sqlteam to no avail.



Thanks,

Lalon

View 1 Replies View Related

Query Field Relative To Max Date

Nov 10, 2005

How do I query to ONLY pull the location for the most recent date?

Date Location
1/2/97 ABC
3/4/02 DEF
2/8/89 GHI
4/30/05 JKL
2/28/03 MNO

View 1 Replies View Related

Relative Time In A Data Warehouse

Feb 28, 2008

At my office, we've been slowly working on putting together a data warehouse.

We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations).

When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510

Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).

My question:

how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse.

We have the following:

1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below.

2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case

3.) DIM_Date: A date dimension table.

(For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.)

Any ideas, comments, experience with something like this?

Thank you.

View 7 Replies View Related

Relative Path For Child Packages

Apr 20, 2007

hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.

Is there any property in the SQL Server Agent or mayby a workaround to solve this?

View 7 Replies View Related

Relative Beginner With View Issues

Oct 30, 2007



I'm trying to create a view from a query using a temp table and I'm running into some strange issues. When I try to enter the query into the view it gives me the following error, but then the query runs and returns the expected data:


"Unable to parse query text."

In an attempt to get around this (I assumed that temp tables just weren't supported with in a view) I put my query into a Stored Procedure and attempted to call the SP from the view. This gave me the same results as before (an error and then a result set) but the error was the following:


"The EXECUTE SQL construct or statement is not supported."


The query seems to run properly in both forms, it just gives an error on the first run and won't allow me to save the view because of the syntax error. The stored procedure runs just fine and returns the right data, so I'm pretty sure that my query is good. I just don't know how to make it execute into a view without getting these errors.

Any help would be greatly appreciated.

Thanks!

Phil

View 13 Replies View Related

Create Assembly With Relative Path

Jan 11, 2008

I am new to the wonders of CLR and, as can be expected, have hit a snag. We have multiple environments to run this off of (dev, test, production, etc.) and need to be able to use the same scripts to install them. Here is our basic setup:



Batch script runs a master SQL file using sqlcmd utility
Master SQL file runs secondary SQL files, including the one with the CLR definitions
Secondary SQL file with CLR calls the CREATE ASSEMBSLYI want to be able to call

CREATE ASSEMBLY assemblyName FROM 'myAssembly.dll'

or

CREATE ASSEMBLY assemblyName FROM '..myAssembly.dll'

or something to the effect. However, when I try this, it says I cannot do it or it cannot find the file, even though the file is located in the same folder.

Any ideas?

View 3 Replies View Related

SQL Server 2008 :: CPU Usage Relative To Each Database

Feb 26, 2015

I am looking for the script to find the CPU usage for each database like this

Test1DB76.00%
Test2DB12.00%
Test3DB1.21%
Test4DB20.08%

View 4 Replies View Related

Calculate Relative Totals With Absolute Values

Feb 28, 2015

Have a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included.

---------------------------------------------------------------------------------------------------
-- Basic simplified structure of maintenance and usage tables
---------------------------------------------------------------------------------------------------
CREATE TABLE #Maintenance (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Maintenance PRIMARY KEY,
MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset
HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance
)

[Code] ....

---------------------------------------------------------------------------------------------------
-- Required output view so that at any given point in time I can determine hours remaining
---------------------------------------------------------------------------------------------------
-- AtDate HoursAvailable
-- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown
-- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset
-- '11-Jan-2015 07:30' 8.9 -- subtract 1.2
-- '11-Jan-2015 11:10' 2.9 -- subtract 6.0
-- '15-Jan-2015 00:00' -0.1 -- subtract 3.0
-- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset
-- '02-Feb-2015 13:00' 10.0 -- subtract 2.2

View 4 Replies View Related

Returning Up To Previous Sunday Relative To Today

Apr 18, 2008

I'm quite a newbie with MDX.

I have a report that uses MDX to return Figures by day. The user selects the Month/Year eg.[Nov F2007] that he wants to see the figures for.

Here is the catch:

The user wants the figures to cut off to the previous Sunday. So if its Thursday, the result set will still only return results to Sunday.

What I'm thinking is something like this:

STRTOMEMBER('[Nov F2007]').FirstChild : STRTOMEMBER('[Nov F2007]').[LastSunday].

I'm not sure if I'm on the right track, but its a mute point if I can't get the previous sunday as a normal day member (ie. 4 November 2007)



Thanks,
Jason

View 3 Replies View Related

Relative ConnectionString For SQL Server 2005 Express

Apr 20, 2006

I am creating a application in Visual C++ 2005 that has database
connections in it. I have gone through the process of setting up the
data source and it will work great on my computer. However, the second
I move either the project or the created executable to another computer
the application keeps crashing. I know that it is crashing due to the
database connection string. The string is setup to work only on my
computer, I am wondering how do I make, or edit, the string to work on
any computer? Thanks for the help.

View 1 Replies View Related

Memory Usage Relative To Size Of Database

Nov 29, 2007

Does anyone know if there is a rough guide in terms of how much memory SQL server will normally use relative to the size of the actual databases that it is hosting?



For example, I'm working on a server that hosts only about 250-300 MB worth of data (including all of the full text indexes, etc.) but the sqlservr.exe process is using 1.25 GB of ram. Also Page Reads/sec is around 1200 -- despite there being over 5 GB of RAM available and not being used (the PF is about 3.25 GB, total memory in the machine is 8GB). Below that w3wp.exe uses only 290 MB and lsass.exe uses only about 225 MB. The machine is a web/database server that is hosting ASP.NET 2, ASP.NET & Classic ASP pages (and the associated databases). For the amount of hardware, the server seems to be responding to requests on the slow side. While some of this is probably due to the legacy code-base, there seems to be a noticeable difference in speed after SQL Server is restarted.



Thanks,



Jeremy

View 1 Replies View Related

How To Use Relative Path For Links In Reports While Using URL Access?

Apr 23, 2007

Dear Sirs,



We use NLB + ISA , Win2003/SP1 + Reporting Services 2005



We use host name URL to access Reporting Serives reports, like http://www.hostname.com/Reportserver?rc%3aParameters=false&amp;%2MyReports%2f....



When reports have grouping , the drill down images(+/-) and links failed to be displayed. We checked the HTML source code and found, instead of the reative path, the image/link URLs are using FULL path and using IP address, like http://10.102.40.55/Reportserver/.....



Because the company policy do not allow use IP to access reports , those drill down images and links failed to be displayed.



How can I configure Reporting Service to use host name or relative path for the drill down images and links.



PS:

We have changed the <UrlRoot> in rsreportserver.config and <ReportServerUrl> in rsWebApplication.config to use host name, but no worked.





Best regards,



Roger (roger_lo@gss.com.tw)











View 1 Replies View Related

Parent? Child? Distant Relative? Casual Aquaintance?

May 20, 2004

Hi all, I have a question that relates to a relationship in my stock performance database. It is a conceptual one, but is the only aspect of my database design that I am "losing sleep over" *LOL* (as if that could happen with me... ;) )

Anyway, Here are my tables/Keys, as setup

1) CurrentList - Primary Key is PortfolioID, StockID, BuyDate, Selldate.
- essentially, this table defines stock portfolios. A PortfolioID has one-to-many StockIDs (stocks in the portfolio), and each PortfolioID/StockID pair can further be grouped by BuyDate and Selldate (because a single stock may come in and out of a single portfolio over time).

2) StockProperty - Primary Key is StockID, CreateDate. This table is built on a daily basis from rows in the Currentlist. It represents the affected stocks currently in ANY portfolio. For example, the IBM stock may exist in more than one PORTFOLIO (PortfolioID) but there will still only be ONE row for IBM on a particular date (CreateDate) in the StockProperty table (since the stock is the same stock, regardless of how many portfolios it is in).

I am trying to figure out which is the parent table, and which the child...or if there IS an "enforceable" relationship at all!?!? None of the non-key data is the same in either table, so the only columns with a relationship is StockID, and StockProperty.Createdate - which must be between CurrentList.BuyDate and CurrentList.SellDate.

I guess that's my quandry (or one of myriad quandries in my life ;) ) - I can't really think of how to enforce (via foreign keys, etc) the relationship in the date range...AND...how to explain the relationship between the two tables (I lean toward CurrentList being Parent to StockProperty, but can't come to grips with what the probably obvious "standard" relationship description would be.

Any (preintable) thoughts?
Thanks!

View 2 Replies View Related

Query Of Set Time Range Relative To Current Date

Dec 26, 2013

I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:

SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))

Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.

View 3 Replies View Related

[HELP!] How Do You Make The Database Path Relative In The Connection String?

Oct 10, 2006

Hi everyone,

I am working on a Hospital Information System project with a team of 6.Each one of us builds or modifies a part of the system and shares the project over a common repository. The problem here is that we use the absolute path of the database in our connection string. Although the directory structure of our project is the same for each member, since the main project folder itself is stored in different locations on each person's computer (for example, one may have it stored in c:My Documents and the other in d: My Documents), we are forced to modify the connection string manually each time someone else from the team updates the repository with the database. How do we make the path of the database relative so that we don't have to modify the connection string manually each time after receiving an update from the repository?

Your help is greatly appreciated.

View 1 Replies View Related

Import Column Transform And Relative/absolute Path

Apr 26, 2007

Hi,

I have a table with a BLOB column, and I need to populate this table including the BLOB column (image type in the database).



What I have done is:

1. use a flat file transform to read a .csv file which specifies the names of the files that store the binary contents for the BLOB column for each row.

2. use an Import Column Transform to read the binary files.

3. use an OLE DB Dest transform to dump the data into my destination table.



I got the error saying:

Error: 0xC02090BB at XXXX, Import Column [1]: Opening the file ".diagram1.bin" for reading failed. The file was not found.



I guess this is because my file "diagram1.bin" is not in the current path? (The current path can be found by "System.IO.Directory.GetCurrentDirectory() call, in my case it is "c:program filesmicrosoft visual studio 0common7IDE".)



My question is: how to determine the directory path information of the package I am running?



Thanks!

Wenbiao

View 3 Replies View Related

Insert Distinct Columns?

Apr 17, 2007

I have two rows of data:
Customer as string, Date as int
 These values are predefined so I cannot use autoindex.  I have to make sure that when I insert a row of data that it does not exist already int the database.  there can be duplicate customers and duplicate dates but not duplicate customer/date combinations.  What would the sql query be?
if    (select count(1) from customers where customer = @customer and date = @date) =< 0  
    insert into table1(customer, date) values (@customer, @date)
end if

View 2 Replies View Related

Insert First N Columns From Select

Mar 1, 2006

I have to insert data from about 30 tables into a single table (Users),to do so i used a cursor and a bit of dynamic sql, this should workfine if the tables have to do the select from had the same number ofcolumns, how ever they don't.I only need the first 5 columns from each the table, in the case wherethey have more than the 5 i need for my 'Users' table i get this error:'An explicit value for the identity column in table 'Users' can only bespecified when a column list is used and IDENTITY_INSERT is ON.'.Is there a way to select only the first five columns from a table, ifnot is there another solution for my problem?My Sql query is the following:DROP TABLE UsersCreate Table Users(ID INT identity PRIMARY KEY,TIPO VARCHAR(255),NOME VARCHAR(255),USERNAME VARCHAR(255),EMAIL VARCHAR(255),GROUPID VARCHAR(255))DECLARE @Table VARCHAR(255)DECLARE @Sql VARCHAR(8000)DECLARE sysCursor CURSORFORSELECT name FROM sysobjects where xtype='U'OPEN sysCursorFETCH NEXT FROM SysCursor into @Tablewhile @@FETCH_STATUS<>-1BEGIN/** INSERE VALORES NA TABELA DE TESTE*/SET @SQL = 'INSERT INTO UsersSELECT * FROM ['+ @Table +']'EXECUTE (@SQL)SET @SQL = 'UPDATE Users SET GROUPID=' + @Table +'WHERE GROUPID IS NULL'EXECUTE (@SQL)print @tableFETCH NEXT FROM SysCursor INTO @TableENDCLOSE sysCursor/** APAGA VALORES INVÁLIDOS DA TABELA DE TESTE*/DELETE FROM Users WHERE TIPO IS NULLDELETE FROM Users WHERE NOME='Nome'DEALLOCATE sysCursorI hope you can give me hand, thank you in advance.

View 8 Replies View Related

Deny Insert Of Columns

May 14, 2008

Hi all,

I'm trying to write a database table that is ONLY accessiable to ONE login / DB user and restrict access to what that user can do with the table.

At the moment I have granted SELECT & INSERT permissions but some of the fields in the table should also be restricted, but for some reason SQL doesn't allow a deny on INSERT for columns.

It seems strange that you can deny an UPDATE permission on a column but not INSERT, so users can create new lines of junk into fields you dont want them to, but cant change that junk afterward??

Anyone have any ideas how I can prevent this user from altering these fields at all?

Thanks in advance

View 1 Replies View Related

Package Configuration File Issue - Relative Path Not Recognized In VS2005

Feb 7, 2008

I have been reading through different posts about how to manage SSIS configuration files across multiple environments. I have seen a few that mention a solution that turns out not to really work (or maybe I€™m doing something wrong); which is what I hope to find out here.

Solutions I am working with:
1: Put the XML Configuration file in the same directory as the SSIS package and only specify the config file name when setting it up in the package. I do not specify a path to the XML Configuration file, just the config file name.

ISSUE: This works when I first create the package and test it without closing down visual studio. Even after deploying the SSIS package and the config file and moving it between environments it works as long as the working directory of the DTExec command is the same directory as the SSIS package and configuration files. The problem comes in after I have closed Visual Studio and opened it again and loaded up the SSIS package. It then gives a bunch of errors stating that it can€™t locate the configuration files€¦. If I could fix this issue, then this would be a solution that I could live with. (I would much rather use a relative path to a different directory but I€™ll live with what I can get). Can anyone solve the issue of Visual Studio 2005 not recognizing the location of the configuration files when only the configuration file name, and not the path is specified?

-- The other odd thing is that I have been able to get this to work consistently with some of my packages but not others. I'm not sure what I did to get those packages to recognize the location of the xml config file.

Thank you in advance for any input and help.

View 27 Replies View Related

BCP Bulk Insert Into Specific Columns

May 20, 2008

I am trying to do a BULK insert using BCP and a XML format file.

I have a file with 32 columns, and a table with 34 columns that i want to bulk insert

i have read here:

http://msdn.microsoft.com/en-us/library/ms179250.aspx

to try and do the bulk insert. but it doesn't seem to insert the data into the correct columns.

here is the SQL to create the table.


SQL Code:






Original
- SQL Code





CREATE TABLE [dbo].[prep_SomeTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Last_Update] [smalldatetime] null,
[ClientID] [varchar](10) NOT NULL DEFAULT (''),
[pollSystem] [varchar](10) NOT NULL DEFAULT(''),
[vin] [varchar](17) NOT NULL DEFAULT(''),
[year] [varchar](4) NOT NULL DEFAULT(''),
[make] [varchar](50) NOT NULL DEFAULT(''),
[model] [varchar](50) NOT NULL DEFAULT(''),
[trim] [varchar](50) NOT NULL DEFAULT(''),
[mileage] [varchar](10) NOT NULL DEFAULT(''),
[factColor] [varchar](100) NOT NULL DEFAULT(''),
[intFactColor] [varchar](100) NOT NULL DEFAULT(''),
[doors] [varchar](10) NOT NULL DEFAULT(''),
[newused] [varchar](10) NOT NULL DEFAULT(''),
[stockno] [varchar](10) NOT NULL DEFAULT(''),
[cpo] [varchar](10) NOT NULL DEFAULT(''),
[certType] [varchar](50) NOT NULL DEFAULT(''),
[certid] [varchar](10) NOT NULL DEFAULT(''),
[otherprice] [varchar](10) NOT NULL DEFAULT(''),
[webprice] [varchar](10) NOT NULL DEFAULT(''),
[invprice] [varchar](10) NOT NULL DEFAULT(''),
[msrp] [varchar](10) NOT NULL DEFAULT(''),
[mktClass] [varchar](50) NOT NULL DEFAULT(''),
[options1] [varchar](8000) NOT NULL DEFAULT(''),
[instock] [varchar](50) NOT NULL DEFAULT(''),
[manfModelNum] [varchar](50) NOT NULL DEFAULT(''),
[trans] [varchar](100) NOT NULL DEFAULT(''),
[enginedesc] [varchar](100) NOT NULL DEFAULT(''),
[drivetrain] [varchar](100) NOT NULL DEFAULT(''),
[overviewtext] [varchar](8000) NOT NULL DEFAULT(''),
[options2] [varchar](8000) NOT NULL DEFAULT(''),
[chromestyleid] [varchar](10) NOT NULL DEFAULT(''),
[photourls] [varchar](8000) NOT NULL DEFAULT(''),
[photosupdated] [varchar](10) NOT NULL DEFAULT(''),
CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]






 CREATE TABLE [dbo].[prep_SomeTable](    [ID] [int] IDENTITY(1,1) NOT NULL,    [Last_Update] [smalldatetime] NULL,    [ClientID] [varchar](10) NOT NULL DEFAULT (''),    [pollSystem] [varchar](10) NOT NULL DEFAULT(''),    [vin] [varchar](17) NOT NULL DEFAULT(''),    [year] [varchar](4) NOT NULL DEFAULT(''),    [make] [varchar](50) NOT NULL DEFAULT(''),    [model] [varchar](50) NOT NULL DEFAULT(''),    [TRIM] [varchar](50) NOT NULL DEFAULT(''),    [mileage] [varchar](10) NOT NULL DEFAULT(''),    [factColor] [varchar](100) NOT NULL DEFAULT(''),    [intFactColor] [varchar](100) NOT NULL DEFAULT(''),    [doors] [varchar](10) NOT NULL DEFAULT(''),    [newused] [varchar](10) NOT NULL DEFAULT(''),    [stockno] [varchar](10) NOT NULL DEFAULT(''),    [cpo] [varchar](10) NOT NULL DEFAULT(''),    [certType] [varchar](50) NOT NULL DEFAULT(''),    [certid] [varchar](10) NOT NULL DEFAULT(''),    [otherprice] [varchar](10) NOT NULL DEFAULT(''),    [webprice] [varchar](10) NOT NULL DEFAULT(''),    [invprice] [varchar](10) NOT NULL DEFAULT(''),    [msrp] [varchar](10) NOT NULL DEFAULT(''),    [mktClass] [varchar](50) NOT NULL DEFAULT(''),    [options1] [varchar](8000) NOT NULL DEFAULT(''),    [instock] [varchar](50) NOT NULL DEFAULT(''),    [manfModelNum] [varchar](50) NOT NULL DEFAULT(''),    [trans] [varchar](100) NOT NULL DEFAULT(''),    [enginedesc] [varchar](100) NOT NULL DEFAULT(''),    [drivetrain] [varchar](100) NOT NULL DEFAULT(''),    [overviewtext] [varchar](8000) NOT NULL DEFAULT(''),    [options2] [varchar](8000) NOT NULL DEFAULT(''),    [chromestyleid] [varchar](10) NOT NULL DEFAULT(''),    [photourls] [varchar](8000) NOT NULL DEFAULT(''),    [photosupdated] [varchar](10) NOT NULL DEFAULT(''),    CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED         (            [ID] ASC        )) ON [PRIMARY]



the format file:


Code:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="26" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="27" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="28" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="29" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="30" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="31" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="32" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ClientID" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="pollSystem" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="vin" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="year" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="make" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="model" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="trim" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="8" NAME="mileage" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="factColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="intFactColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="11" NAME="doors" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="12" NAME="newused" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="13" NAME="stockno" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="14" NAME="cpo" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="15" NAME="certType" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="16" NAME="certid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="17" NAME="otherprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="18" NAME="invprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="19" NAME="webprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="20" NAME="msrp" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="21" NAME="mktClass" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="22" NAME="options1" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="23" NAME="instock" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="24" NAME="manfModelNum" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="25" NAME="trans" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="26" NAME="enginedesc" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="27" NAME="drivetrain" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="28" NAME="overviewtext" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="29" NAME="options2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="30" NAME="chromestyleid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="31" NAME="photourls" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="32" NAME="photosupdated" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>



and the SQL:


SQL Code:






Original
- SQL Code





BULK INSERT prep_SomeTable
FROM '\devd_driveftproot7415_519.dat'
WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' )






 BULK INSERT prep_SomeTableFROM '\devd_driveftproot7415_519.dat' WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' ) 



error message:

Quote: Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.

I am not sure why it is trying to insert into the Last_Update Column, which is the only smalldatetime field in the table. but in the format file, i do not have any data going into that column.

it looks like everything is set correctly in the format file

in the SQL Server Library Link, section "Using an XML Format File" 2nd example, it looks to be the same thing as I am?

not sure what I am doing wrong?

View 1 Replies View Related

Converting Columns In An INSERT (was SQL Query)

Jan 17, 2005

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...

View 2 Replies View Related

Insert Country Code Into Columns

Nov 9, 2011

I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.

View 10 Replies View Related

Insert Data Into Two Columns In Table

Feb 26, 2013

I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it says

Msg 515, Level 16, State 2, Line 4

Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.

I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..

Below is the code

SET @dbLoop = CURSOR FOR
SELECT name
FROM sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'

[Code] ....

View 6 Replies View Related

What Is The Maximum Columns In INSERT Statement ??

Feb 24, 2007

Hello friends,

I am sorry to be posting this message in this forum. But thought if anyone does have any idea abt my problem, which i illustrate below.

I want to know what is the maximum number of columns that an INSERT statement can handle?

I have come across a situation where i need to insert 15 values in a table.

INSERT INTO myTable (col1, col2, col3,....., col15) VALUES (v1, v2, v3,...., v15).

Actual statement:

INSERT INTO BillSell (bnumber, stock, qty, price, tprice, seen, bill_id, brokerage, service_tax, brok_st, stt, brok_st_stt, total_cost, total_rate_pu, check)
VALUES ('ICICI 06-12-2004 reshma', 'RELIND', 10, 541.7375183271872, 5417.375183271873, 'no', 2402, 40.2375, 4.92507, 45.162569999999995, 6.771718979089841, 51.934288979089835, 5365.0, 536.5, 'check')


When I call this in Java program, it asks me to check the syntax of the statement as follows :

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check) VALUES ('ICICI 06-12-2004 reshma', 'RELIND', 10, 541.7375183271872, 5417.' at line 1

Please make notice that all the data types of the table columns and the values passed in the statement match.

Now the problem is that, when from the above INSERT statement, i remove the last column (i.e. the "check" column and its corresponding value passed i.e. 'check'), the code works fine. I observe here that now there are 14 columns in the statement.

So i need the solutions for this. I cannot move further in the program......

Please help.

Trust in Technology mate .....'Not in Human Beings'

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved